Dynamic table name in destination

How to create a new table dynamically in OLE DB destination.

This is what i am doing

I am reading multiple flat files in loop and saving file name to a variable. Then i have a source script component which read and transforms data .Now how can I push the data to SQL table. I want to create a new table with name saved in a variable. I tried using OLE DB destination and assigning table name from variable. Does'nt work.

Thanks in advance for any insight on how to make this work.

-Amar

I think you'll have to break this up into multiple work flows. The first executes an Execute SQL task in the control flow to create the table. Then attached to that Execute SQL Task is the data flow that operates on the file and then inserts into the destination, which is derived from the incoming table name.

That may work for you. The problem is, the OLEDB Destination cannot create a table at run time.|||

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

|||

Amar Khaira wrote:

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

In the OLE DB Destination, set it to "Table name or view name variable - fast load". Then just pick the variable that you loaded in the foreach loop. The metadata must be the same, though, for each table.|||

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

|||

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

|||

Amar Khaira wrote:

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

You need to create one table first. Then populate the variable that you are using in the foreach loop with a default value of that table you just created. Then in the OLE DB destination, in selecting that variable, it will read it and find the table. You can then perform your mappings. When you execute the package, the default value of the variable will be over-written.|||

Amar Khaira wrote:

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

See my comment above.|||

Phil,

That what I did...can you please elaborate more...what i am missing

|||

Amar Khaira wrote:

Phil,

That what I did...can you please elaborate more...what i am missing

You need to create a table first. Then, in the variable you are using, TYPE in the name of that table in the DEFAULT VALUE parameter of that variable.

Then in the OLE DB Destination, do as I said above. Select that variable, and it should work for you.|||And this next comment is important, so I'm going to make it its own post:

The metadata (number of columns, data types, etc...) must be the same across all of your tables that you are dynamically feeding into the OLE DB Destination.|||

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

|||

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

|||

Amar Khaira wrote:

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

Try setting the "ValidateExternalMetaData" property on the OLE DB Destination to false. If that doesn't work, then set the "DelayValidation" property of the data flow to true.|||

Amar Khaira wrote:

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

Excellent. Please mark one of these posts as the answer to your question.

Thanks,
Phil