July 21, 2022 at 3:41 pm
I have a ragged right flat files where each row can have a different set of pipe delimited columns. The very first column is consistent in all rows and this 2 character code in this column describes the type of data its row contains. Each two character code has a set of defined columns.
My question, is it possible to check the first 2 characters of each row and divert each row into a different flow for each code? I suspect that's possible with a Ragged Right Data Source and an conditional split. However, once split, at this point, I then want to define my columns on each split flow; almost like putting a data source in the middle of the flow instead of at the beginning if that makes sense. Is there a best practice way to do this in SSIS, or should I just load all rows into one table and break out each data set using TSQL in a SPROC?
July 21, 2022 at 4:01 pm
You can definitely set up a single column source and create a derived column based on the first two characters and then do the conditional split. I believe you can then use the TOKEN expression to parse the single columns into multiple derived columns, or you can use a script task. I think the script would be fairly simple and you could probably find an example online to modify.
I would try using TOKEN to see if you get the performance you need. SQL is always an option, but then you'd have to write the data twice.
July 21, 2022 at 6:34 pm
I'd probably use a Script Component (source) to do this. It will perform all of the parsing and output redirection for you.
The above advice would also work (except for the Script Task bit – I think Ed B meant a Script Component (transformation) there). Getting the TOKEN() method to work will get messy if there are a lot of columns.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply