August 11, 2010 at 6:51 am
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?
August 11, 2010 at 6:55 am
Yes, you can use an Execute SQL task, and that is probably the best way to accomplish it.
August 11, 2010 at 9:10 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2010 at 9:13 am
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
August 11, 2010 at 9:15 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2010 at 9:34 am
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]
August 11, 2010 at 9:35 am
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.
August 11, 2010 at 9:37 am
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]
August 11, 2010 at 9:38 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2010 at 9:40 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2010 at 10:23 am
: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
August 11, 2010 at 11:10 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2010 at 11:17 am
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.
September 25, 2013 at 11:54 am
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.
September 25, 2013 at 10:59 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply