Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating tables on the fly with SSIS Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 6:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 17, 2012 5:39 AM
Points: 61, Visits: 123
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?
Post #967362
Posted Wednesday, August 11, 2010 6:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
Yes, you can use an Execute SQL task, and that is probably the best way to accomplish it.
Post #967367
Posted Wednesday, August 11, 2010 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
If you want to put data in them too, you might find this a challenge.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #967501
Posted Wednesday, August 11, 2010 9:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
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
Post #967504
Posted Wednesday, August 11, 2010 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
But if the table doesn't exist at design time, you can't create the dataflow.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #967508
Posted Wednesday, August 11, 2010 9:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 7:44 AM
Points: 151, Visits: 341
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...


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.
Post #967532
Posted Wednesday, August 11, 2010 9:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
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.
Post #967535
Posted Wednesday, August 11, 2010 9:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 7:44 AM
Points: 151, Visits: 341
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...


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.
Post #967537
Posted Wednesday, August 11, 2010 9:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #967539
Posted Wednesday, August 11, 2010 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #967541
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse