locked
Dynamic SSIS Flat File Import RRS feed

  • Question

  • Hello,

    I'm experienced with .NET and T-SQL but I'm new to SSIS.

    I want to create a package that will read in a process list consisting of a database name and a full path to csv files.  I have multiple structurally identical databases that I want import CSV files in a dedicated folder.... for example DB1 uses c:\ssis\csv4db1\*.csv, DB2 uses c:\ssis\csv4db2\*.csv, etc.

    I was trying to open a flat file  csv containing this process list and in a foreach loop, assign variables to the DBNAME and CSVPATH and pass them to another foreach loop to do the import.

    I'm stuck...

    Would anyone suggest an approach to taking a process list (and maybe reading it in from a table works better) consisting of a database name and a follder name where CSV files are and import that data.

    Thanks,

    Tony Z.

    Thursday, June 6, 2013 1:20 AM

Answers

  • Since you said you are a .Net programmer you can use the power of script task in combination with SSIS data flow task. I would suggest the following steps- 1. In a script component read the CSV file and store the database and path as comma separated items in a array list variable (object type in SSIS variables). 2. After the script component is executed you will have the list of databases and files in an object array. 3. Follow up the script component with a for each container where enumerator is "Foreach from Variable" 4. Read the first item in array, split it by comma and get the database and file location. Assign these to two variables and construct your connection strings. 5. Create a data flow task in the for each container and use the above created variables as connections and finish off the load. A sample implementation of such type can be found at http://www.rad.pasfu.com/index.php?/archives/18-Foreach-Loop-based-on-Variable-SSIS.html Let me know if this helps and if it helps please mark this as answer. Regards, Sri Meda
    • Proposed as answer by Srivatsav Meda Thursday, June 6, 2013 2:39 AM
    • Unproposed as answer by Srivatsav Meda Thursday, June 6, 2013 2:39 AM
    • Proposed as answer by Mike Yin Friday, June 7, 2013 1:49 AM
    • Marked as answer by tonyzoc Tuesday, June 11, 2013 11:45 PM
    Thursday, June 6, 2013 2:33 AM

All replies

  • You need to bind the data source for the flatfile connection to a variable.

    Now set the variable in a loop.

    If you don't bind the data source connection string to a variable, the flat file data source will only pick the file which is hardcoded in connection string.

    http://www.mssqltips.com/sqlservertip/1084/dynamic-flat-file-connections-in-sql-server-integration-services/


    val it: unit=()

    Thursday, June 6, 2013 1:26 AM
  • Thanks,

    I understand most of that...but the example has a flat file with 4 rows and each one would define a different DataLoadDir.  How to you iterate through that list to import multiple files.

    I want to open a flat file csv and for each row...

    set my import file and database name to variables

    Run my dataflow to open the flatfile and copy it to an OLEDB destination.

    The flatfile connection and OLEDB connection would be dynamic based on the variables set in the loop.

    I think I know how to set the flatfile source dynamically (con str = @[user::CSVFILEPATH]).

    I'm not sure yet about the OLEDB destination database...

    but right now I'm stuck with the processfile csv as a datasource to a foreach loop.



    Thursday, June 6, 2013 2:08 AM
  • Since you said you are a .Net programmer you can use the power of script task in combination with SSIS data flow task. I would suggest the following steps- 1. In a script component read the CSV file and store the database and path as comma separated items in a array list variable (object type in SSIS variables). 2. After the script component is executed you will have the list of databases and files in an object array. 3. Follow up the script component with a for each container where enumerator is "Foreach from Variable" 4. Read the first item in array, split it by comma and get the database and file location. Assign these to two variables and construct your connection strings. 5. Create a data flow task in the for each container and use the above created variables as connections and finish off the load. A sample implementation of such type can be found at http://www.rad.pasfu.com/index.php?/archives/18-Foreach-Loop-based-on-Variable-SSIS.html Let me know if this helps and if it helps please mark this as answer. Regards, Sri Meda
    • Proposed as answer by Srivatsav Meda Thursday, June 6, 2013 2:39 AM
    • Unproposed as answer by Srivatsav Meda Thursday, June 6, 2013 2:39 AM
    • Proposed as answer by Mike Yin Friday, June 7, 2013 1:49 AM
    • Marked as answer by tonyzoc Tuesday, June 11, 2013 11:45 PM
    Thursday, June 6, 2013 2:33 AM
  • Since you said you are a .Net programmer you can use the power of script task in combination with SSIS data flow task. I would suggest the following steps- 1. In a script component read the CSV file and store the database and path as comma separated items in a array list variable (object type in SSIS variables). 2. After the script component is executed you will have the list of databases and files in an object array. 3. Follow up the script component with a for each container where enumerator is "Foreach from Variable" 4. Read the first item in array, split it by comma and get the database and file location. Assign these to two variables and construct your connection strings. 5. Create a data flow task in the for each container and use the above created variables as connections and finish off the load. A sample implementation of such type can be found at http://www.rad.pasfu.com/index.php?/archives/18-Foreach-Loop-based-on-Variable-SSIS.html Let me know if this helps and if it helps please mark this as answer. Regards, Sri Meda
    Thanks.  I had to jump onto another task but I'm back to this...and your suggestion helps.
    Tuesday, June 11, 2013 11:45 PM