Solved

SSIS Creating Destination Table Dynamically From Source Table

Posted on 2012-04-10
3
4,614 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
  • 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now