Trouble with a Flat File Source...

[aultmike]

I have a flat file that is comma delimited and the text is qualified with a double quote ("), however each row in the flat file is prefixed with 3 spaces and throws off the flat file connection manager when I try to setup the columns. I'm not sure how to remedy this any advice?

Thanks

 

[ousoonerjoe]

Open the script that is generated. Edit the script so it ignores the first 3 chars of the first column:

[COL1] = RIGHT(column1, LEN(column1)-3)

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[aultmike]

Thanks for the suggestion ousoonerjoe, but what script? i dont see a script in the Flat File Connection Manager...

 

[ousoonerjoe]

When you do a manual import of a flat file, it generates a script. Let it create the script then take that and modify it to use in your job.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[aultmike]

I'm not doing a manual import... I'm creating an SSIS package and using a Flat File Connection Manager...unless i'm missing something you're telling me... *scratching head* If so I apologize that I'm not seeing it.

 

[ousoonerjoe]

not a problem...

There are times i use the manual process to generate the import script for me. I don't actually execute it. Just copy and paste the script out and use it as needed. However, after trying to create one for you, I have been reminded that was for 2000 DTS, not SIS.

Plan B: (Should always have a 'Plan B'). Import the file into a 'Temp' or 'Holding' table. Make the first column a VARCHAR(xx) and import the data into that table. Then execute an UPDATE on that column to strip out the extra chars. Perform any other data scrubbing tasks that may need to be done at this time as well. Once the data is cleaned up and good to go, you can import it directly into your tables as needed.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------