Dynamic table loading in SSIS (Part 2)

I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).

SQL Server Developer Edition 2005 Win32/X64/IA64 English CD/DVD

However, the previous post is probably the most popular on this blog and I’ve had requests for more detail. So as part 2 of this post, I’ve created a demo solution for you to try out. Before implementing something like this though I’d think very carefully about what it is your trying to achieve. If you only have a small number of tables/records to load, with a well organised and controlled framework, this may work for you. Certainly, one package is easier to keep track of than 100! Having said this, one package to run all loads means that a failure is going to halt all table loads.

Here is the solution – Dynamic Table Loading Solution

  1. Run the “ExampleDynamicLoad.sql” in the miscellaneous folder to create an example “control” and “staging” database with necessary tables and metadata. This will also create the folder structures on your local drive to accomodate format and comma delimited load files. 
  2. Run the package “GenerateDynamicLoadFiles.dtsx”. This will create 3 text files in your newly created folder structures, each with a million rows.
  3. Finally, now that the environment has been set up, you can run the package.

Note that this demo has been created to run against a single development machine. Therefore localhost or “.” have been used in place of specifying servernames.

Cheers
Frank

5 thoughts on “Dynamic table loading in SSIS (Part 2)”

  1. Thanks Protean for the code! Your example was very helpful and helped to learn some new concepts in SSIS and bcp. I forgot we could bcp out a format file.

    I’m working on a large project to dynamically create format files as the table width of my imports varies, i.e. one day the file as 3 date columns (if it’s Jan 3), the next it has 4, the next it has 5, etc…

    I’m trying to reverse engineer what you have done and apply it to my situation.

    One issue I’m having is that my hosting company won’t allow bcp.exe or any xp_cmdshell commands.

    Do you have any examples you can show me on how to create the format file using the script task instead of bcp.exe format?

    Thanks!

    Jason
    lonestarfinancing[dot]com

  2. I’m afraid I don’t have any examples to hand but it should be possible if you interrogate the information schema catalogue views and write the file based on that.

    To see some examples of how to do this in script component see the post on writing sql results to SSIS variable. It would require some modification to make it fit your purposes, but hopefully you will see the idea.

    Finally if you want to write to a file (which you will have to do) lookup examples using streamwriter class.

    Hope this helps!

  3. Hello,
    Really nice article. I am newbie in this area.
    Can I implement the same in my environment? here is the scenario.

    1. I have lot of different tables data to be extracted to flat files like table1, table2, table3…..tableXXX.
    2. I need to run select * from each table and capturing the data in flat files by naming the file as table name.
    I am trying to use foreach loop in the case but getting error…I captured table names (table1, table2 etc.,) and Query (select * from table1, select * from table2 etc., ) to a temp table and then using the variables tablename and query as variables in the foreach loop container and ending with error.

    Error at Getting Data to Flatfiles [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80040E0C.
    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80040E0C Description: “Command text was not set for the command object.”.

    So my question is …can I use yours in my scenario? I heard some where that each table contains different columns and the each query leads to different columns and hence the columns are changing flatfile extraction is not possible. Is that correct?
    Can you kindly suggest some method to my schenario?
    Krishna.

    1. Hi Krishna,

      You heard correctly that you can’t dynamically change the metadata within a dataflow. So in your case each iteration of the loop will require different column names and as you have found out, this doesn’t work.

      You may be able to use a combination of bcp format command and bcp export command within the loop to do this.

      However, if you have the time I’d recommend that you build a dataflow for each table. Then you can take advantage of SSIS by exporting in parallel.

      Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.