Solved

SSIS Creating Destination Table Dynamically From Source Table

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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