Creating tables on the fly with SSIS

Question

Post reply

Creating tables on the fly with SSIS

tripower

SSCrazy

Points: 2035
More actions
August 11, 2010 at 6:51 am

#142352

I wanted to create a set of tables on the fly using a SQL script can I do this via an Execute SQL task or is there a better way?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 1

Yes, you can use an Execute SQL task, and that is probably the best way to accomplish it.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

If you want to put data in them too, you might find this a challenge.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 3

Phil Parkin (8/11/2010)
If you want to put data in them too, you might find this a challenge.

Ummm... No.

Use an ExecuteSQL task to create the table, then a DataFlowTask with a Success Constraint that will transfer the data from the source into the table.

Rich

Phil Parkin SSC Guru Points: 246986 More actions · Answer 4

But if the table doesn't exist at design time, you can't create the dataflow.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Baskar B.V SSCrazy Points: 2823 More actions · Answer 5

tripower (8/11/2010)
I wanted to create a set of tables on the fly using a SQL script can I do this via an Execute SQL task or is there a better way?

To create the table itself dynamically, how you are going to pass the schema? If you have the schema in .sql file you can use execute sql task to run the .sql file...

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 6

Sure you can. Configure the DataSource for where the data is coming from, and then configure the destination, setting the DataAccessMode to be TableNameOrViewNameVariable. Create the variable at the package level, and put the name of the table that you want the data inserted into in that variable.

Baskar B.V SSCrazy Points: 2823 More actions · Answer 7

Phil Parkin (8/11/2010)
But if the table doesn't exist at design time, you can't create the dataflow.

have done data population dynamic using DTS... We pulled the column info using systables from source and target and mapped the columns using activex scripting... This was possible in DTS since no separate control & data flow...

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

Phil Parkin SSC Guru Points: 246986 More actions · Answer 8

Rich-403221 (8/11/2010)
Sure you can. Configure the DataSource for where the data is coming from, and then configure the destination, setting the DataAccessMode to be TableNameOrViewNameVariable. Create the variable at the package level, and put the name of the table that you want the data inserted into in that variable.

I'll play along. If you proceed in this way, how do you create the column mappings?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246986 More actions · Answer 9

Baskar B.V (8/11/2010)
Phil Parkin (8/11/2010)
But if the table doesn't exist at design time, you can't create the dataflow.
have done data population dynamic using DTS... We pulled the column info using systables from source and target and mapped the columns using activex scripting... This was possible in DTS since no separate control & data flow...

Things were possible in DTS which are now ... tricky ... in SSIS, I'll agree that.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 10

:ermm: Phil, my apologies.

Should have tried it a little more before I ASSUMED that SSIS would be logical. Guess it's not as straight forward as I thought.

Rich

Phil Parkin SSC Guru Points: 246986 More actions · Answer 11

Rich-403221 (8/11/2010)
:ermm: Phil, my apologies.
Should have tried it a little more before I ASSUMED that SSIS would be logical. Guess it's not as straight forward as I thought.
Rich

No problem - and you learned something along the way, which is good 🙂

SSIS is logical ... but perhaps it becomes so only after spending a lot of time with it. At some point, the philosophy 'clicks' and after that it becomes your friend.

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 12

Agreed. But, I have spent a fair amount of time with it, just sometimes forget that there are some "quirks" that can get in the way of doing something which should be relatively straight forward.

vinu512 SSCoach Points: 15729 More actions · Answer 13

Phil Parkin (8/11/2010)
Rich-403221 (8/11/2010)
:ermm: Phil, my apologies.
Should have tried it a little more before I ASSUMED that SSIS would be logical. Guess it's not as straight forward as I thought.
Rich
No problem - and you learned something along the way, which is good 🙂
SSIS is logical ... but perhaps it becomes so only after spending a lot of time with it. At some point, the philosophy 'clicks' and after that it becomes your friend.
Phil

Hi Phil,

Sorry for hijacking the thread. had this posted for almost a week without any luck so thought someone from this thread might be able to help.

I was working on an SSIS project in which I need to:

1. Loop through a folder containing multiple text files and get their name into a variable.

2. Append the current system date to that variable so that the variable now contains txtfilename + Date.

3. Create a table in the database with the name as value of the variable.

4. Transfer data of the txt file into the newly created table.

The first two steps I accomplished using a For Each Loop Container and i am able to get the file name into the variable.

The second step I accomplished by just appending GetDate() to the value of the variable.

The third step I accomplished using an Execute SQL task for creating table.

What i am not able to do is get the data into the table with a Data Flow Task.

The problem is that it requires me to give table name as input, which i cannot as the table is being created by the same package dynamically. I need to automate this task so I would always have to create the table dynamically.

Please be patient enough to go through my problem and provide a solution. Thanks a tonne in advance.

PS: I am not very good with C# or other scripting languages so I won't be able to work with a

Scripting task in SSIS. So, please give me a solution other than scripting.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Phil Parkin SSC Guru Points: 246986 More actions · Answer 14

Will the tables all have the same DDL?

If not, this is practically impossible to do without C#/VB.NET coding. Unless you decide not to use data flows and instead code the imports using dynamic SQL. But if you go down that path, why not just do everything in a stored proc?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel