loading Multiple flat files with SSIS in a table in SSMS

Question

I tried to run the SSIS package to load multiple flat files from the NAS drive location. has created the variables, File_Path with data type String and the value as G:\CnS_Pro_Migration_to_QSI-XL\ETL_\Claims\QA\CA_HH_SMART_PRO_MEDCLM_74_CSPFacets_202203C1_202202261309_56662.txt File_Name with data type String and the value as CA_HH_SMART_PRO_MEDCLM_74_CSPFacets_202203C1_202202261309_56662.txt File_Folder with data type String and the value as G:\CnS_Pro_Migration_to_QSI-XL\ETL_\Claims\QA\

I created a for loop container and under Collection where the folder name given as G:\CnS_Pro_Migration_to_QSI-XL\ETL_\Claims\QA\ files: *.txt Under Variables mapping given the variable as User::File_Path.

Under the properties of connection manager for file load and in expressions given as Property as Connection String and Expression as @[User::File_Path]

But on executing the job I got the issue as

[Flat File Source [2]] Warning: The system cannot find the file specified. [Flat File Source [2]] Error: Cannot open the datafile "CA_SMART_PRO_MEDCLM_74_CSPFacets_202203C1_202202261233_56662.txt". [SSIS.Pipeline] Error: Flat File Source failed the pre-execute phase and returned error code 0xC020200E. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Could you please help me out?

Answer 1

For an SSIS solution, see the link below.

https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/

For a Bulk Insert option, see the link below.

https://www.mssqltips.com/sqlservertip/6458/sql-server-bulk-insert-for-multiple-csv-files-from-a-single-folder/

Here is an example of a looping Bulk Insert job, based on changing dates in the names of similar CSV files.

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=48)
BEGIN

PRINT @intFlag


declare @fullpath1 varchar(1000)
select @fullpath1 = '''\\source\FTP1\' + convert(varchar, getdate()- @intFlag , 112) + '_SPGT.SPL'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[table1] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 5, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)

-------------------------------------------

declare @fullpath2 varchar(1000)
select @fullpath2 = '''\\source\FTP2\' + convert(varchar, getdate()-@intFlag, 112) + '_SPBMI_GL_PROP_USD_C.SPL'''
declare @cmd2 nvarchar(1000)
select @cmd2 = 'bulk insert [dbo].[table2] from ' + @fullpath2 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 5, ROWTERMINATOR=''0x0a'')'
exec (@cmd2)

-------------------------------------------

declare @fullpath3 varchar(1000)
select @fullpath3 = '''\\source\FTP3\' + convert(varchar, getdate()-@intFlag, 112) + '_SPBMI_GL_PROP_USD_C_ADJ.SPC'''
declare @cmd3 nvarchar(1000)
select @cmd3 = 'bulk insert [dbo].[table3] from ' + @fullpath3 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 7, ROWTERMINATOR=''0x0a'')'
exec (@cmd3)

-------------------------------------------

declare @fullpath4 varchar(1000)
select @fullpath4 = '''\\source\FTP4\' + convert(varchar, getdate()-@intFlag, 112) + '_SPGTINFRA_ADJ.SPC'''
declare @cmd4 nvarchar(1000)
select @cmd4 = 'bulk insert [dbo].[table4] from ' + @fullpath4 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 7, ROWTERMINATOR=''0x0a'')'
exec (@cmd4)

SET @intFlag = @intFlag + 1
    
END
GO

Answer 2

Cannot open the datafile "CA_SMART_PRO_MEDCLM_74_CSPFacets_202203C1_202202261233_56662.txt"

This exception means that you are not reading the fully qualified file path in the ForEach loop container. Make sure that the "fully qualified" option is selected in the container's editor.