If BULK INSERT fails because file does not exist then BULK INSERT Next file

I’m having an issue with BULK INSERT , the situation is the following : I’ve a folder path with probably 200 files in it but I’ve a table with 400 filenames that I must review if they exist in the same folder path. I need the BULK INSERT to search for this 400 filenames and if It does not find one filename because it does not exist , then move on with the next file but my code is actually stopping when the filename does not exists in the folder and it does not keep trying to load next filename.

Right now, is finding the 1st file so the while loop works till the end and then it deletes that filename from the table to continue with the second file but when it does not find the next file name , it goes out of the while loop, not deleting the filename from the table and just inserting the 1st file found when we are still missing 398 more files to search and load if found

                    BEGIN TRY
            ;WHILE (SELECT COUNT(*)
                     FROM dbo.IBOFileNames_V1) > 0
            BEGIN
                    SET @FileName = ''
                    SET @FileName = (SELECT     TOP 1 FileName
                                       FROM     dbo.IBOFileNames_V1
                                       ORDER BY [Date] ASC)
                    SET @SQLScript = N' 
        BULK INSERT [dbo].[RawEventBCPIBO_V1]
        FROM ''' + @MainPath + @FolderPath + N'' + @FileName + N''''

                    IF @FileType = 'TXT'
                    BEGIN
                            SET @SQLScript = @SQLScript + N' 
                WITH
                (
                    FIRSTROW=1,
                    FIELDTERMINATOR=''t'',
                    ROWTERMINATOR=''n''
                ) '
                    END
                    ELSE
                    BEGIN
                            SET @SQLScript = @SQLScript + N' 
                    WITH
                    (
                        FORMAT=''CSV'',
                        FIRSTROW=1,
                        KEEPNULLS,
                        FIELDTERMINATOR='','',
                        ROWTERMINATOR=''n''
                    ) '
                    END

                    EXEC sp_executesql @SQLScript

                    DELETE FROM dbo.IBOFileNames_V1
                      WHERE FileName = @FileName
            END

            END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0
            BEGIN
                    ROLLBACK TRANSACTION;
            END;

            DECLARE @errorSeverity INT = ERROR_SEVERITY();
            DECLARE @errorState INT = ERROR_STATE();
            DECLARE @logMessage VARCHAR(MAX);

            SET @logMessage = 'Line ' + CONVERT(NVARCHAR, ERROR_LINE()) + ': ' + ERROR_MESSAGE();

            RAISERROR(@logMessage, @errorSeverity, @errorState);
    END CATCH

Answer

I’ve applied the BEGIN TRY just to the EXEC variable , now it goes trough each of the existing 400 filenames, if it does not find 1 then it goes to the next file . If at the end only found 100 of the 400, the data of those 100 will be loaded into the table.

Once the script gets executed, it will show the error for all the files that were not found but it will load the ones found 🙂

                ;WHILE (SELECT COUNT(*)
                     FROM dbo.IBOFileNames_V1) > 0
            BEGIN
                    SET @FileName = ''
                    SET @FileName = (SELECT     TOP 1 FileName
                                       FROM     dbo.IBOFileNames_V1
                                       ORDER BY [Date] ASC)
                    SET @SQLScript = N' 
        BULK INSERT [dbo].[RawEventBCPIBO_V1]
        FROM ''' + @MainPath + @FolderPath + N'' + @FileName + N''''

                    IF @FileType = 'TXT'
                    BEGIN
                            SET @SQLScript = @SQLScript + N' 
                WITH
                (
                    FIRSTROW=1,
                    FIELDTERMINATOR=''t'',
                    ROWTERMINATOR=''n''
                ) '
                    END
                    ELSE
                    BEGIN
                            SET @SQLScript = @SQLScript + N' 
                    WITH
                    (
                        FORMAT=''CSV'',
                        FIRSTROW=1,
                        KEEPNULLS,
                        FIELDTERMINATOR='','',
                        ROWTERMINATOR=''n''
                    ) '
                    END

                    BEGIN TRY
                    EXEC sp_executesql @SQLScript
                    END TRY

                    BEGIN CATCH
                        PRINT 'Error File - ' + @SQLScript;
                    END CATCH

                    
                    DELETE FROM dbo.IBOFileNames_V1
                    WHERE FileName = @FileName
            END