locked
Changing column order in flat files RRS feed

  • Question

  • I have to import data from multiple flat files..so I am using a foreach loop container to loop through the files..the problem is that..the order of columns is not guaranteed..they may or may not change..in this case SSIS package loads the data incorrectly in the destination..Is there any way to take care of this problem??

    e.g. using script task or something else..

    Tuesday, July 10, 2012 5:50 PM

Answers

All replies

  • Using a script source in the dataflow is about the only way it'll work.

    But you should talk to who ever is creating these files and have them fix their crappy process.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, July 10, 2012 5:51 PM
  • Using a script source in the dataflow is about the only way it'll work.

    But you should talk to who ever is creating these files and have them fix their crappy process.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Thanks for replying mate..I have already escalated the issue..but the problem is that the project is still in the initial stages..we are still analyzing the data..so I dont think this will be taken care of very soon..

    Do you know how to handle a flat file inside a script source?

    Tuesday, July 10, 2012 5:53 PM
  • Its not too tough - google around a bit and you'll find lots of examples

    Here is one example http://take5systems.com/MadHat/?p=279


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Proposed as answer by SSISJoostMVP Tuesday, July 10, 2012 6:21 PM
    Tuesday, July 10, 2012 5:58 PM
  • Thank you very much..will try to do my best.. :)

    Will share here if I can get around the problem of columns..

    Tuesday, July 10, 2012 6:03 PM
  • Creating a package programmatically is another option here. E.g. using EzAPI or just pure .Net code.

    I think if the metadata is not static which required by SSIS then you are likely to have all sort of issues doing that the other ways.


    Arthur My Blog

    Tuesday, July 10, 2012 6:11 PM
  • Creating a package programmatically is another option here. E.g. using EzAPI or just pure .Net code.

    I think if the metadata is not static which required by SSIS then you are likely to have all sort of issues doing that the other ways.


    Arthur My Blog


    The meta data would be static coming out of the script source since you would only define the fixed set of output columns you care about.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, July 10, 2012 6:13 PM
  • There are mainly three issues I am facing..as follows:

    1) Changing column order

    2) File format is not confirmed. Sometime txt..sometimes csv..but this can be taken care by using different foreach loop for different file types. But the real problem is..the encoding of the file..sometimes it is UTF..somtimes it is ANSI

    Tuesday, July 10, 2012 6:23 PM
  • All these deviations/anomalies whatever you call these you can detect using .Net code when building your package programmatically.

    PS: I oftentimes say no to whoever wants me to process data like that because such "fluid" specs are ill suited for ETL, just too risky.


    Arthur My Blog

    Tuesday, July 10, 2012 6:27 PM
  • There are mainly three issues I am facing..as follows:

    1) Changing column order

    2) File format is not confirmed. Sometime txt..sometimes csv..but this can be taken care by using different foreach loop for different file types. But the real problem is..the encoding of the file..sometimes it is UTF..somtimes it is ANSI


    If you use Streamreader then it shouldn't matter, just use the flag that detects the file type from the byte order marks

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Proposed as answer by Eileen Zhao Sunday, July 15, 2012 1:43 AM
    Tuesday, July 10, 2012 6:27 PM
  • I am using a streamreader..and using

    Output0Buffer.MarketPrice = columnArray(1)

    to assign the values to the output rows..but does that make the encoding consistent no matter what type of encoding the source might have?

    Suppose my source encoding is 65001

    so,

    What will be the encoding of the output row if I use 

    Output0Buffer.MarketPrice = columnArray(1)

    and what will the encoding of the output row if I use

    Output0Buffer.MarketPrice = columnArray(1).ToString()

    Also how to check the encoding type of the file?
    Monday, July 16, 2012 6:37 PM
  • Hi rockstar283,

    Please refer to the code in the following thread:
    http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/7a9decde-25c1-439e-8110-212eed64f371/ 

    Thanks,
    Eillen
    • Proposed as answer by Desi_Boy Wednesday, July 18, 2012 5:16 PM
    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 7:48 AM
    Wednesday, July 18, 2012 8:55 AM
  • Thank you Eileen..That was  a big help.. :)
    Wednesday, July 18, 2012 5:16 PM