Changing column order in flat files

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..

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

Thank you very much..will try to do my best.. :)

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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?

Answer 11

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

Answer 12

Thank you Eileen..That was  a big help.. :)