Post reply

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

  • 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.

  • June 16, 2017 at 6:05 am

    #1947082

    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.

  • June 16, 2017 at 6:16 am

    #1947086

    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.

  • June 16, 2017 at 6:20 am

    #1947087

    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.

  • June 16, 2017 at 2:25 pm

    #1947158

    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).".

  • June 18, 2017 at 8:26 am

    #1947190

    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?

  • June 21, 2017 at 5:23 am

    #1947519

    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.).

  • June 22, 2017 at 1:53 am

    #1947685

    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.).

    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.

  • June 22, 2017 at 7:37 am

    #1947755

    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.

  • June 24, 2017 at 2:04 am

    #1948041

    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.".

  • June 24, 2017 at 2:39 am

    #1948042

    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.".

  • June 24, 2017 at 2:36 pm

    #1948055

    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.

  • June 28, 2017 at 3:41 am

    #1948515

    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.

  • June 28, 2017 at 6:40 am

    #1948538

    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.

  • June 28, 2017 at 10:09 am

    #1948583

    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:

  • 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.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy