Solved

SSIS Creating Destination Table Dynamically From Source Table

Posted on 2012-04-10
3
5,434 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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 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