Post reply

SSIS excel source case sensitive fields

  • September 23, 2011 at 2:24 pm

    #248364

    Hi,

    I have an excel file and there are some columns that are different for each file.

    For example Source : When I created the SSIS package , the file had Source but some other test files have source as the column name. So it fails.

    IS there any way I can make it case sensitive ?

    Thanks

  • September 26, 2011 at 12:58 am

    #1386295

    What you could do is not to use the header (so read it as actual data) and refer to the actual columns:

    the Excel source component will reference the columns with F1, F2, F3, ...

    Use a conditional split to get rid of the header line.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 28, 2015 at 8:05 am

    #1836056

    HI PSB,

    Make sure that column sequence should be same.

  • October 28, 2015 at 8:49 am

    #1836092

    revanappa.shivanagi (10/28/2015)

    HI PSB,

    Make sure that column sequence should be same.

    I sure hope the problem was solved 4 years ago 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 28, 2015 at 9:13 am

    #1836101

    Hi Koen,

    I have another Issue related to same.

    In my SSIS package , I have changed one of table column name from upper case to lower case For Ex."EMPLOYEEID" to "employeeid".

    After changing the case i am facing an error as "Error: "component "OLE DB Destination" (16)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"."

    I am aware that SSIS is case Sensetive.I am having near about 500+ dataflow task which i can't change manually as going into dataflow task and update the metadata mapping.

    I am looking for solution to resolve this problem or else any work around for to skip the case sensitivity in metadata.

    Thanks,

    Revan

  • October 29, 2015 at 1:41 am

    #1836257

    You have 500 packages writing to the same table?

    There's no easy solution for this.

    Maybe you can "search and replace" the old column name by the new column name in all of the XML files before you make the actual change in the table.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 29, 2015 at 4:11 am

    #1836288

    Thanks,

    Those 500+ DFT are divided into total 6 packages.

    I am looking for solution where we dont need to update the metadata in SSIS manually.

    Is there any way if i am changing the column case in that without fail SSIS package should run successfully.

    Or else any property in SSIS to avoid case sensitivity.

  • October 29, 2015 at 5:43 am

    #1836312

    Please don't cross post questions on multiple threads http://www.sqlservercentral.com/Forums/Topic1731492-364-1.aspx#bm1731775

    Koen and Phil have given you the solution.

    Make copies of your packages.

    Open them up in your favorite text editor

    Find and Replace the old string with the new string

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply