March 3, 2010 at 10:01 am
I have an SSIS package that's been work great until today.
Here are the basic steps:
1. The package loads a flatfile (that's full of evil embedded text qualifiers) into a SQL table that contains one (really huge) text field. This initial flatfile does have column headers in the first row, but in this first step, they're merely pulled in as a generic row.
2. A stored proc then runs a replace() function to scrub out the embedded text qualifiers.
3. After the data is scrubbed, the package then exports the data back to a flatfile so that it can be properly loaded into a SQL destination table.
4. The scrubbed flatfile is imported into a destination table with multiple columns -- making everybody happy.
Normally, when step 3 fires, the row with the column headers appears first in the scrubbed flatfile -- but not any more. Now the headers are ending up way deep in the data. Consequently, when step 4 fires, the package fails because it can't find the appropriate header rows and the actual header values end up being mixed in with a column of integers --> making nobody happy.
So, is there a way to ensure that the header row ends shows up first in the scrubbed flatfile?
--thanks,
Pete
March 3, 2010 at 10:24 am
My first thought is the table that the unscrubbed data is loaded into should have 2 fields, a Record Id which would be an identity and the huge text field you mentioned. The reason for this is that it preserves the order the records were inserted.
When you export the data from this table you be sure to specify ORDER BY RecordId. Also, if you are selecting the table off the list then don't. Build the query yourself..
SELECT BigTextField FROM dbo.YourWorkTable ORDER BY RecordId
CEWII
March 3, 2010 at 10:27 am
Great idea -- sounds like a plan.
Thanks,
Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply