CODEWITHSUNDEEP
sql-serverssis
Scotch
Scotch

Reputation: 3226

SSIS Dynamic OLEdb Source and Destination from variable

I'm attempting to configure an SSIS package to refresh 'n' number staging of tables, with a single data flow task.

The Data Flow Task is inside a forEach loop. It contains an OleDB Source and OleDB destination. Each of these are set to "table name from variable", with the source and destination variables being populated in each loop (they are all existing tables).

Delay Validation is set to 'True' and 'ValidateExternalMetadata' is set to false.

The package is running successfully for the first table in the loop, but fails on the second table, with the messages

Column [firstTablecolumn] cannot be found at the datasource

and

cannot create an OleDB accessor. Verify that the column metadata is valid

So it appears it is still trying to use metadata of the first table, although the variables are properly being set in each iteration. Is this not the correct way to iteratively load n number of tables with a parameter?

Upvotes: 0

Views: 624

Answers (1)

billinkc
billinkc

Reputation: 61221

The power of SSIS data flows comes from the design time contract between a source and the destination.

When a package begins execution, the first step is to validate that the design time API is in line with the current state of things. Failure to meet that results in a VS_NEEDSNEWMETADATA error.

What you want, a generic Source to Destination thing is not an SSIS thing. That's an Azure ADF pipeline or a SQL Server 2000 DTS package.

The way to do what you're describing but in an SSIS way is to build an SSIS package per table. A tool like Biml makes this trivial activity. + https://billfellows.blogspot.com/2015/01/biml-replicate-o-matic.html

Upvotes: 1

Related Questions