Home Forums Data Warehousing Integration Services no. of columns in excel may vary RE: no. of columns in excel may vary

  • July 6, 2014 at 1:23 pm

    #1727246

    ykonline (7/5/2014)

    Please find the details below

    Date CAD CZK EUR

    1/1/20101.052018.34750.6977

    1/2/20101.053218.39850.6957

    1/3/20101.051718.33600.6982

    1/4/20101.037718.16240.6935

    1/5/20101.037118.25410.6943

    Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,

    Just to set some expectations, if knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mini-bike through a matchbox. Part of the reason for that is that I've not needed to use SSIS to do such things. Instead, I do it all in T-SQL.

    Doing the required dynamic unpivot is simple but we need to get the data into a table where we can actually work on it.

    That leads me to a couple of questions before I can help (others that know SSIS, please DO jump in)...

    1. Is there a way that SSIS can automatically create a table with the right number of columns (no matter how many) from a given spreadsheet? If so, this will be an absolute cake walk. If not, we'll have to bake the cake, first.

    2. If the answer to question #1 above is "No, SSIS can't automatically create a table with the right number of columns for a given spreadsheet", then are you allowed to use the ACE drivers and OPENROWSET?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)