Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS Creating Destination Table Dynamically From Source Table

Posted on 2012-04-10
3
Medium Priority
?
6,302 Views
Last Modified: 2012-04-18
Hello Experts,
When creating a Dataflow task within SSIS, the source is created and then a OLE DB Destination is added, there's an option to Create a table dynamically when you press the NEW button inside the OLE DB Destination Editor.

I'd like to build an SSIS package Programatically and I would like to create the destination table dynamically, just as if I press the New button within the code.  Would anyone know how to do this?

Your insight is appreciated.

Thank you.
0
Comment
Question by:DBL9SSG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 37832028
you need to generate the CREATE TABLE command dynamically with an expression and run it in an Execute SQL Task right before the data flow task.
or you can use SELECT ... INTO ... command in an execute sql task instead of data flow task to create the destination table structure based on selected structure.
0
 

Author Comment

by:DBL9SSG
ID: 37832505
Reza_Rad,
Thank you for the comment, however, want I'm looking for is the code behind the "New" button.  Since the source is already created, how do I go about generating the CREATE TABLE command with the associated columns and column_types from the source?
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 1500 total points
ID: 37835455
the NEW button will fetch metadata information from INPUT COLUMNS and generate the Create table command based on it.
So if you want to do that you should write a script component to loops through input columns and generated sql script for create table and run it. and finally map whole columns together.
But note that you need to do all of those in SCRIPT and this will be hard.
your other ways are what I mentioned earlier, which are much easier and better because SSIS data flow is not designed for dynamic metadata.
If you need more help let me know where exactly in detail you need more information.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question