Using dynamically named table in SSIS data flow task

Question

I'm new to SSIS and am writing a package that includes moving data to a table that is created in a previous Execute SQL Task object.

The issue that I'm encountering is that I am unable to create a data flow destination task that uses a dynamic destination table name.

The intended process is:

• Execute SQL Task object creates new table based on today's date (i.e. Table1_20111014)
• Data Flow task moves data from table "Table1" to "Table1_20111014".

The column metadata for Table1 and Table1_20111014 are the same, and does not change. However, the name of the table the data needs to be moved to will change depending on the date at time of execution.

Is it possible to dynamically specify the destination table in a destination data flow object?

If not, are there known workarounds or is using SSIS for this task a bad idea?

Solution

As long as the meta data remains the same, there is no drawback to using dynamic destination table name.

To accomplish this, on the ole db destination instead of using "table name" or "table name fast load" use the equivalent "from variable" table load option. This obviously assumes you have a variable defined that contains the name of the table created in the execute sql task

Comments

[WT_W]

Thanks, I had been using SQL Server destination instead of OLE DB destination so I didn't see that option. I eventually used the variable as an expression for the Bulk Insert table location value of the SQL Server destination.

[billinkc]

Glad to hear it's working. In general, while the SQL Server destination was touted as being this amazing, high performing option when it was released, the improvements to the OLE DB destination since then have offset the performance gains. Further, the SQL Server destination is just flaky, if you haven't seen a random error message of "unable to prepare the SSIS bulk insert...", you will. Even if the package is executing on the same box as the destination database, it can still randomly raise this error.