SSIS Dataflow ODBC-source to Destination (OLEDB/any)

Question

I have to extract few (5) tables (from Ingres DB) using ODBC connection and load the same in OLE-DB (SQL Server).

1). OLE-DB based connection extract 5 tables name. 2). Using Conrol flow (For each loop container), loading variable (i.e. loop_var_tablename) for each table 3). For source connection:

• Using ODBC connection -> Expressions -> Sqlcommand="ingres."+@[User::loop_var_tablename]

4). For detination connectin:

• Using OLE-DB connection -> Data access mode: table name or veiew name variable @[User::loop_var_tablename]

Problem statement: Since the table name is being loaded Dynamically. Thus, i can not adjust 'Mapping' in Source and Destination. How to proceed with it?

I would really appreciate if you could guide me on this issue.

Thanks in advance!

Thank you KN

Expecation: Table should be loaded from Source (Ingres db) to Target (MS SQL Server).

failed attempt xx: In source connection/ODBC When -> Expressions -> Sqlcommand="ingres."+@[User::loop_var_tablename]

Errors:

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0x321 at Data Flow Task, ODBC_Extract_Rows [8]: Error getting schema information. Table has no columns
Error: 0xC004706B at Data Flow Task, SSIS.Pipeline: "ODBC_Extract_Rows" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation

failed attempt yy: In source connection/ODBC When -> Expressions -> Sqlcommand="select * from ingres."+@[User::loop_var_tablename]

Errors:

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC004706B at Data Flow Task, SSIS.Pipeline: "ODBC_Extract_Rows" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

I also changed OLE-db connection (Destinatino) 'Data access mode: Table name or view name variable -fast load'. The issue persists.

Answer 1

You can't. An SSIS data flow is not a generic "anything to anything" data moving tool. You can dynamically switch the destination from A to Z as long as the schema is the same for the columns that are being mapped (extra columns in table Z? No problem).

The metadata for an SSIS package is set at design time aka when you're in the Visual Studio editor. When it's running loop 1, your approach will work as the current table matches the expected metadata. Loop 2, everything is "different" and boom, VS_NEEDSNEWMETADATA.

For five tables, just add 5 data flows and be done with it. Dynamic is good, there's lots of things that can be done there but the amount of effort it's going to take versus just doing what's needed weighs heavily towards adding a data flow per table.

What can be dynamic is the table name. I often work in environments where the database/schema/tablename get environment specific values added "DevSalesDB.DevSchema.DevSalesPerson" because someone was super worried someone would confuse DevDatabaseServer with ProdDatabaseServer. In cases like that, your Expressions would be needed as the schema is the same for the sales person table but the actual name changes across environments as you deploy.