Programmatically set SQL Destination Table

Question

Hi

I have a SSIS 2005 package that needs to read from an XML file, parse into columns and load into a SQL table. I have the parsing working ok. My problem is that I won't know the table name until runtime. The tables are named transaction_x where x is held in a package level variable. Is there any way I can set the destination table name programmatically?

Thanks

Rob.

Answer 1

Have you tried using a SQL Command component? I've never actually had to do this but I think (in theory) you can store this name in a variable and then create a table using the variable in the free T-SQL component. It's worth a try I'd say!

Answer 2

As AjAdams mentioned, you would want to store the table name in a variable. When you open up the gui of the ole db destination, you can then click on the data access mode drop down and select "Table name or view Name variable". You would then select your variable name that contains the table name from the variable name drop down. This should solve your problem.

Answer 3

In addition to rfonn's answer, you could iterate through your table names by dropping on a Foreach Container, and setting it to a Foreach NodeList Enumerator.

This is, of course, if your names are stored in the XML in manner that allows you to iterate this way.

Although not useful in this context, property expressions are also a good means by which values can be set dynamically.