Someone please help - SSIS - Get info from SQL server table about which particular files to load in target SQL server table

Question

Someone please help - SSIS - Get info from SQL server table about which particular files to load in target SQL server table

ritesh.sinha 54676

SSC Veteran

Points: 227
More actions
June 16, 2017 at 3:56 am

#324101

Hi all,
I am struggling with the problem as below.
"A folder contains many source files (may be excel or text file). Need to create a solution which gets the information from SQL server table about
which particular file/files will get loaded in target SQL server table".
Not sure how to pick files whose names match with the names listed in an SQL server table. And then load each file into another SQL table.
I only know how to load multiple files from a folder to an destination SQL server table.
Error: The type of the value (DBNull) being assigned to variable"User::tablenamedest" differs from the current variable type(String). Variables may not change type during execution. Variable types arestrict, except for variables of type Object.
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but thenumber of errors raised (3) reached the maximum allowed (1); resulting infailure. This occurs when the number of errors reaches the number specified inMaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

ritesh.sinha 54676 - Friday, June 16, 2017 3:56 AM
Hi all,
I am struggling with the problem as below.
"A folder contains many source files (may be excel or text file). Need to create a solution which gets the information from SQL server table about
which particular file/files will get loaded in target SQL server table".
Not sure how to pick files whose names match with the names listed in an SQL server table. And then load each file into another SQL table.
I only know how to load multiple files from a folder to an destination SQL server table.

1) Load the file names from the SQL Server table into an SSIS object variable.
2) Use a FOREACH container to loop round the items in the object variable.
3) Do your processing within the FOREACH container.
Do all of the files have the same structure and are they all being imported into the same table? If not, you are going to find this task challenging.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 2

Phil Parkin - Friday, June 16, 2017 6:05 AM
ritesh.sinha 54676 - Friday, June 16, 2017 3:56 AM
Hi all,
I am struggling with the problem as below.
"A folder contains many source files (may be excel or text file). Need to create a solution which gets the information from SQL server table about
which particular file/files will get loaded in target SQL server table".
Not sure how to pick files whose names match with the names listed in an SQL server table. And then load each file into another SQL table.
I only know how to load multiple files from a folder to an destination SQL server table.
1) Load the file names from the SQL Server table into an SSIS object variable.
2) Use a FOREACH container to loop round the items in the object variable.
3) Do your processing within the FOREACH container.
Do all of the files have the same structure and are they all being imported into the same table? If not, you are going to find this task challenging.

Yes, all files have the same structure and the destination table is same.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

ritesh.sinha 54676 - Friday, June 16, 2017 6:16 AM
Phil Parkin - Friday, June 16, 2017 6:05 AM
ritesh.sinha 54676 - Friday, June 16, 2017 3:56 AM
Hi all,
I am struggling with the problem as below.
"A folder contains many source files (may be excel or text file). Need to create a solution which gets the information from SQL server table about
which particular file/files will get loaded in target SQL server table".
Not sure how to pick files whose names match with the names listed in an SQL server table. And then load each file into another SQL table.
I only know how to load multiple files from a folder to an destination SQL server table.
1) Load the file names from the SQL Server table into an SSIS object variable.
2) Use a FOREACH container to loop round the items in the object variable.
3) Do your processing within the FOREACH container.
Do all of the files have the same structure and are they all being imported into the same table? If not, you are going to find this task challenging.
Yes, all files have the same structure and the destination table is same.

That is very good news for you as a developer. So all you need to done is dynamically control the name of the source file in your data flow container, with each iteration of the FOREACH loop.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 4

I created the package as advised by you, but when executed the package it is throwing the below error:
None of the fields in my tables is identical, so the case of identical values is ruled out. Only the data of the first table gets loaded into the destination table and then this error. Looks like it is trying to load the same table again and again.

[OLE DB Destination [27]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been
terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of
PRIMARY KEY constraint 'PK__Employee__E3E85D3CD712503C'. Cannot insert duplicate key in object 'dbo.EmployeeDest2'. The duplicate key
value is (109).".

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

ritesh.sinha 54676 - Friday, June 16, 2017 2:25 PM
I created the package as advised by you, but when executed the package it is throwing the below error:
None of the fields in my tables is identical, so the case of identical values is ruled out. Only the data of the first table gets loaded into the destination table and then this error. Looks like it is trying to load the same table again and again.

[OLE DB Destination [27]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been
terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of
PRIMARY KEY constraint 'PK__Employee__E3E85D3CD712503C'. Cannot insert duplicate key in object 'dbo.EmployeeDest2'. The duplicate key
value is (109).".

Did you manage to set the data source connection string using the filepath from the Foreach container?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 6

Phil Parkin - Sunday, June 18, 2017 8:26 AM
ritesh.sinha 54676 - Friday, June 16, 2017 2:25 PM
I created the package as advised by you, but when executed the package it is throwing the below error:
None of the fields in my tables is identical, so the case of identical values is ruled out. Only the data of the first table gets loaded into the destination table and then this error. Looks like it is trying to load the same table again and again.

[OLE DB Destination [27]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been
terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of
PRIMARY KEY constraint 'PK__Employee__E3E85D3CD712503C'. Cannot insert duplicate key in object 'dbo.EmployeeDest2'. The duplicate key
value is (109).".
Did you manage to set the data source connection string using the filepath from the Foreach container?

I am unable to have an 'Object' type variable in 'Connection String' for Data Connection manager for the Data Flow task (the job of which is to read from ADO object and load the data in an SQL table.).

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 7

ritesh.sinha 54676 - Wednesday, June 21, 2017 5:23 AM
Phil Parkin - Sunday, June 18, 2017 8:26 AM
ritesh.sinha 54676 - Friday, June 16, 2017 2:25 PM
I created the package as advised by you, but when executed the package it is throwing the below error:
None of the fields in my tables is identical, so the case of identical values is ruled out. Only the data of the first table gets loaded into the destination table and then this error. Looks like it is trying to load the same table again and again.

[OLE DB Destination [27]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been
terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of
PRIMARY KEY constraint 'PK__Employee__E3E85D3CD712503C'. Cannot insert duplicate key in object 'dbo.EmployeeDest2'. The duplicate key
value is (109).".
Did you manage to set the data source connection string using the filepath from the Foreach container?
Phil Parkin - Sunday, June 18, 2017 8:26 AM
ritesh.sinha 54676 - Friday, June 16, 2017 2:25 PM
I created the package as advised by you, but when executed the package it is throwing the below error:
None of the fields in my tables is identical, so the case of identical values is ruled out. Only the data of the first table gets loaded into the destination table and then this error. Looks like it is trying to load the same table again and again.

[OLE DB Destination [27]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been
terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of
PRIMARY KEY constraint 'PK__Employee__E3E85D3CD712503C'. Cannot insert duplicate key in object 'dbo.EmployeeDest2'. The duplicate key
value is (109).".
Did you manage to set the data source connection string using the filepath from the Foreach container?
I am unable to have an 'Object' type variable in 'Connection String' for Data Connection manager for the Data Flow task (the job of which is to read from ADO object and load the data in an SQL table.).

Sorry, I should have made this clearer:
I have created an 'ExecuteSQL Taskâ€™ to have an ADO object 'vFileName' to load the 'table names' from an SQL table that has the list of tables to be loaded into another SQL table.
Connected this Execute SQL task to a 'Foreach Loop Container'. This container is a 'Foreach ADO Enumerator' and have added the ADO object 'vFileName' in the ADO Object source variable. Correct this much?
I added a 'Data Flow Task' to load the tables the names of which are now in the ADO object 'vFileName'. Added a â€˜Flat File Source Taskâ€™ on Data Flow tab. Created a new â€˜Flat File Connection Managerâ€™. But when I am running this package, it is giving me the errors as described above.
I am unable to have an 'Object' type variable in 'Connection String' for Data Connection manager for this â€˜Flat File Connection Managerâ€™. It says cannot add ADO type object in 'Connection String'.
Please advise what am I missing here.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 8

You have missed a step, I think.
Create a new string variable, to hold the file name.
Configure the FEL to map the 'current' file name to the variable you have created.
Use the new variable to define your data source.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 9

Phil Parkin - Thursday, June 22, 2017 7:37 AM
You have missed a step, I think.
Create a new string variable, to hold the file name.
Configure the FEL to map the 'current' file name to the variable you have created.
Use the new variable to define your data source.

Yes, I have a String type variable 'tablenamedest' created and have added this on 'Variable Mappings' screen. But what's happening is that it loads the data in the first table correctly. But after that looks like it keeps on trying to load the same table data again so it fails. Error message:
"Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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.".

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 10

ritesh.sinha 54676 - Saturday, June 24, 2017 2:04 AM
Phil Parkin - Thursday, June 22, 2017 7:37 AM
You have missed a step, I think.
Create a new string variable, to hold the file name.
Configure the FEL to map the 'current' file name to the variable you have created.
Use the new variable to define your data source.
Yes, I have a String type variable 'tablenamedest' created and have added this on 'Variable Mappings' screen. And what's happening is that it loads the data in the first table correctly. But after that looks like it keeps on trying to load the same table data again so it fails. The destination SQL table gets the rows that are in one of the files only and the package fails after that. The only file which gets copied is the one which I had given in 'Flat File Connection Manager'-> 'General' -> 'File Name' field. Not sure why is the loop picking this very file repeatedly:

Actually there is a table in SQL Server that has a list of flat files' names. I need to load the data of these flat file into another SQL table. Suppose the flat files' names in the first SQL table are table1, table2 and table3. So the data in the flat files with names table1, table2, table3 should get loaded into the destination table say table 'Dest'.
Error message:
"Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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.".

Phil Parkin SSC Guru Points: 246695 More actions · Answer 11

Please read this:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9f9812c3-56cf-4a76-ad48-fad7b03ad9ac/how-to-pass-filename-as-a-dynamic-variable-to-flat-file-source-in-ssis-?forum=sqlintegrationservices
and confirm that you have set the flat file source dynamically.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 12

Phil Parkin - Saturday, June 24, 2017 2:36 PM
Please read this:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9f9812c3-56cf-4a76-ad48-fad7b03ad9ac/how-to-pass-filename-as-a-dynamic-variable-to-flat-file-source-in-ssis-?forum=sqlintegrationservices
and confirm that you have set the flat file source dynamically.

Hi Phil,
I am still unable to get through this. Please find the doc attached with each of the steps that I have followed and please confirm that I am not seeing something which is so clearly visible. Thanks a million in advance.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 13

ritesh.sinha 54676 - Wednesday, June 28, 2017 3:41 AM
Phil Parkin - Saturday, June 24, 2017 2:36 PM
Please read this:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9f9812c3-56cf-4a76-ad48-fad7b03ad9ac/how-to-pass-filename-as-a-dynamic-variable-to-flat-file-source-in-ssis-?forum=sqlintegrationservices
and confirm that you have set the flat file source dynamically.
Hi Phil,
I am still unable to get through this. Please find the doc attached with each of the steps that I have followed and please confirm that I am not seeing something which is so clearly visible. Thanks a million in advance.

In your document, I cannot find the part where you have used an Expression to set the value of the flat file connection string. You need to do this so that the connection string changes every time the FOREACH loop iterates.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ritesh.sinha 54676 SSC Veteran Points: 227 More actions · Answer 14

Phil Parkin - Wednesday, June 28, 2017 6:40 AM
ritesh.sinha 54676 - Wednesday, June 28, 2017 3:41 AM
Phil Parkin - Saturday, June 24, 2017 2:36 PM
Please read this:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9f9812c3-56cf-4a76-ad48-fad7b03ad9ac/how-to-pass-filename-as-a-dynamic-variable-to-flat-file-source-in-ssis-?forum=sqlintegrationservices
and confirm that you have set the flat file source dynamically.
Hi Phil,
I am still unable to get through this. Please find the doc attached with each of the steps that I have followed and please confirm that I am not seeing something which is so clearly visible. Thanks a million in advance.
In your document, I cannot find the part where you have used an Expression to set the value of the flat file connection string. You need to do this so that the connection string changes every time the FOREACH loop iterates.

I have added the below pieces in the doc and attached again.

When I execute this package it is giving the error as below: