none
SSIS: Dynamic destination table creation with dynamic input columns RRS feed

  • Question

  • Hi,

    I have several CSV files that need to be added to an SQL database, each having its own separate table with the same name as the CSV file. The files do not all have the same columns.

    To summarize, what I need to do is:

    1. Get the file name (This gives the table name for my DDL script)
    2. Get column information from file (The Flat File Connection Manager gives both i.e. the column names as well as suggested types for each of the columns. I want to use these in my dynamically generated DDL script)
    3. Create the table using the generated script
    4. Load all data from file to destination table

    My main concern is STEP 2. How do I get the column names and their types (as suggested by SSIS) to generate a dynamic DDL for each file that comes in? A script task? If yes, then how exactly?

    There is something similar to my concern mentioned here but I'm unable to drive my way towards a solution with what has been suggested there.

    TIA!




    Wednesday, March 11, 2015 4:56 PM

Answers

  • For just several files it is too much work to go with a dynamic approach.

    If you want to then you need to generate the package programmatically in code, or given the complexity or rather the ease of the task at hand you could already do that in code without the package

    SSIS itself relies to static metadata and will not pick anything on the fly


    Arthur

    MyBlog


    Twitter

    Wednesday, March 11, 2015 5:30 PM
    Moderator

All replies

  • For just several files it is too much work to go with a dynamic approach.

    If you want to then you need to generate the package programmatically in code, or given the complexity or rather the ease of the task at hand you could already do that in code without the package

    SSIS itself relies to static metadata and will not pick anything on the fly


    Arthur

    MyBlog


    Twitter

    Wednesday, March 11, 2015 5:30 PM
    Moderator
  • The Flat File connection manager gives only column names. It doesn't suggest a type. All columns type are initially set to DT_STR , Length : 50. 

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Wednesday, March 11, 2015 7:08 PM
  • Thanks @ArthurZ

    I'd try doing this in code (C#) :)

    Thursday, March 12, 2015 12:45 PM
  • Hi @ArthurZ,

    Just a quick question then. Since it is more like a Workflow and I am attempting to achieve this through code, I need your suggestion on my project type. Here's the steps again:

    1. Read each CSV
    2. Create DDL Script for it
    3. Run that script
    4. Load data into DB

    Should I attempt to do this using the Windows Workflow foundation? (With Code Activities etc.) Would this be the best way?

    Tuesday, March 17, 2015 10:32 AM