locked
SSIS- Dynamic table, CSV FIle RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    Can somebody please refer me to a few articles, below is my problem statement.

    -> Import file format CSV.

    -> Based on the number of columns in CSV, create a table dynamically with the columns.

    I'm Using SQL Server 2008 R2

    I'm not allowed to use dynamic queries :(

    Please advice.

    Thanks

    Swat

    Thursday, July 4, 2013 5:07 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    If your end intent is to load this CSV (so it is an all dynamic oepration) into that table then the SSIS package better be built dynamically in .net code.

    The steps roughly are:

    1. Create a Console C# or VB .net project, reference the DTS dll, and
    2. Have the code inspect the columns for quantity, size and content, then finally
    3. Generate and run the package built by the code

    An post with relevant links: http://consultingblogs.emc.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

    a more specialized example: http://blogs.msdn.com/b/christophputz/archive/2010/03/04/creating-ssis-packages-dynamically-from-template-packages.aspx

    PS: I proposed this solution because designing SSIS packages using SSDT/BIDS/VS requires static metadata. And you also need to inspect the content to determine the length of the columns, not all may fit into the target.

    Arthur My Blog

    • Proposed as answer by Mike Yin Monday, July 8, 2013 2:31 AM
    • Marked as answer by StSingh Monday, July 8, 2013 7:39 AM
    Thursday, July 4, 2013 6:19 PM