SSIS SQL Question

Posted on 2009-02-18
Medium Priority
Last Modified: 2012-05-06
I am running a flatfile Data task into an OLEDB(SQL Sever) destination. The data comes in as such....
00000041517 56028;5801287;5818026;
00000041517 906.00;20.00;17.00;
00000041517 09/13/2007;09/13/2007;09/13/2007;
00000044339 56028;5807615;5818026;
00000044339 02/18/2008;02/19/2008;02/19/2008;
00000044339 906.00;19.00;17.00;

Youll see they come in 3's in no particular order...All I need is the ID and currency rows(amount). But since Im doing it in SSIS If I can load these into 3 seperate columns in some kind of order then I can identify the currency column and just select that column in my next task. But if I can create a table in sql that just includes the ID and amount(currency column) and have 1 row for each id and each amount that would be best. I hope this makes sense..The best outcome I could get is the following..

A table with this from the above data would be ideal
00000041517 906.00
00000041517 20.00
00000041517 17.00
00000044339 906.00
00000044339 19.00
00000044339 17.00

Any help would be greatly appreciated!!!
Question by:healthcheckinc
LVL 25

Accepted Solution

reb73 earned 2000 total points
ID: 23673426
Create a staging table with just two columns - one for Id and the second column called Detail which takes in  the rest of delimited text

Import the flat file to this staging table.

Then create an execute SQL task which removes all records from the intermediate table which doesn't contain a period (.), since the only values you need have embedded periods, something like -

DELETE <stagingtablename>
WHERE PATINDEX('%.%', Detail) = 0

Then use the CTE solution to one of your previous questions (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24113508.html#23553659)  to parse the delimited text and give you the desired output


Author Closing Comment

ID: 31548345
You rock Reb....I know I have been redundant on some of my questions but i have been doing so many diff. types of parsing methods, I forget what Ive already done...Im doing 81 different parses, some the same but for the most part unique. Thanks again. Youll be hearing from me soon ;)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question