SSIS SQL Question

Posted on 2009-02-18
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

    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 (  to parse the delimited text and give you the desired output


    Author Closing Comment

    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now