May 2, 2012 at 2:30 pm
What can I use within SSIS to accomplish the following
(A) I have a table with data listed below (Fix number of columns)
DTL|000099951|OK||AE|78011014301|20120413|1|HUN
DTL|000099951|OK||AE|78011014301|20120413|1|HUN
DTL|000099951|OK||AE|78077092901|20120413|1|HUN
(B) I would like to insert Header (HDR|630845288|278) for every DTL record to an output text file delimited by "|".
HDR|630845288|278
DTL|000099951|OK||AE|78011014301|20120413|1|HUN
HDR|630845288|278
DTL|000099951|OK||AE|78011014301|20120413|1|HUN
HDR|630845288|278
DTL|000099951|OK||AE|78077092901|20120413|1|HUN
May 2, 2012 at 2:41 pm
You're most likely going to want to try to use an asynchronous transformation script component in mid-stream. Why asynchronous? Because it's 2 rows for every 1, which isn't synchronous.
There's a number of good articles on the web for that but I know of *no* good way to do this otherwise. It's nasty, it's mainframe, it's streaming file type of work, which most ETL shuns and avoids these days. It's meant to be built, and read, from a straight scripting language and not a bulk data manipulator.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 31, 2012 at 5:13 am
Well , i can suggest a workaround for this.
U can try and see if you get the desired result.
Here's what you can do :
Inside the DFT you must be pulling the records using a OLE DB source & directly writing them to a flat file.
You just need to modify this a bit by placing a derived column task in between.
1) Firstly, add four colums (starting from the top) to column list of yor Flat File Connection Manager.
i.e. If your table has 10 columns , your Flat File Connection manager should have 14.
2) These 4 colums will make your header (repetitive) , here's how :
Use a derived column column between the two tasks , and add four columns to it.
1) Column 1 - HDR
2) Column 2 - 630845288
3) Column 3 - 278
4) Column 4 - backslash followed by n (within double quotes) - this means newline
3) Now in the Flat file destination mappings , map these 4 new columns from derived column to the 4 new colums available in the FFC (which we added in Step 1)
(Take care to place these colums at the top of your mapping followed by your actual table columns)
4) Use | as the column delimeter.
I believe this should work, and repeat the header for each record.
Hope this helps.
Revert back if anything is unclear.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply