Post reply

LOADING A PARTICULAR COLUMN FROM FLATFILE

  • November 5, 2016 at 1:13 pm

    #331777

    Dear Folks,

    i have flatfile like below

    col1 col2 col3

    aa $100 vv

    $200 bb dd

    dd nn $300

    my result will be (want to load those data only in destination table)

    $100

    $200

    $300

    How can we achive this USING SSIS and what is the logic..?

    Thanks in advance.

    Regards,

    Kannan

  • November 5, 2016 at 1:53 pm

    #1910771

    This worked...

    USE Tempdb;

    GO

    DROP TABLE #tempTable;

    GO

    CREATE TABLE #tempTable(

    col1 varchar(5),

    col2 varchar(5),

    col3 varchar(5)

    );

    GO

    INSERT INTO #tempTable VALUES ('aa', '100', 'vv'),

    ('200', 'bb', 'dd'),

    ('dd', 'nn', '300');

    SELECT COALESCE(TRY_CONVERT(INT,col1),TRY_CONVERT(INT,col2),TRY_CONVERT(INT,col3)) AS Result

    FROM #tempTable;

    If the TRY_CONVERT fails, it returns NULL, so any non-numeric values in the 3 columns will be converted to NULLs, and then you can use COALESCE to return the first non-null value. If you can have more than one non-null value in each record, you may need further branching.

    You could do the whole thing by just running a simple T-SQL statement in your SSIS package.

  • November 5, 2016 at 2:01 pm

    #1910772

    Thank You..

    That's fine.

    in case needed column is not having numeric means what will be the logic..?

  • November 5, 2016 at 2:36 pm

    #1910775

    What would you return if the value were NULL? Kind of depends on what you're trying to do. NULL is no the same as zero. NULL means "unknown".

    If you're sure you can convert the null values to zero, then you could use ISNULL()

    Here's an example (well, with another INSERT statement to cover the case where all 3 values are NULL).

    INSERT INTO #tempTable VALUES('aa', 'bb', 'cc');

    SELECT ISNULL(COALESCE(TRY_CONVERT(INT,col1),TRY_CONVERT(INT,col2),TRY_CONVERT(INT,col3)),0) AS Result

    FROM #tempTable;

    Wait a minute... could you post the structure of the table this stuff is going into? Looks like there could be a lot of easier ways to do this, depending on what your final table looks like.

  • November 7, 2016 at 12:01 am

    #1910834

    For fun, here is an alternative method

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    ;WITH SAMPLE_DATA(CID,C1,C2,C3) AS

    ( SELECT CID,C1,C2,C3

    FROM (VALUES

    (1,'aa', '100', 'vv')

    ,(2,'200', 'bb', 'dd')

    ,(3,'dd', 'nn', '300')

    ,(4,'NOT', 'A', 'NUMBER')

    )X(CID,C1,C2,C3)

    )

    SELECT

    SD.CID

    ,MAX(ISNULL(TRY_CONVERT(INT,X.CC),0)) AS CVAL

    FROM SAMPLE_DATA SD

    OUTER APPLY

    (

    SELECT C1 UNION ALL

    SELECT C2 UNION ALL

    SELECT C3

    ) X(CC)

    GROUP BY SD.CID;

    Output

    CID CVAL

    ----- -----

    1 100

    2 200

    3 300

    4 0

  • November 7, 2016 at 7:48 am

    #1910923

    What are the actual column names involved and what does a sample of the actual data look like? The reason I ask is that such a data configuration is typical of a "multi-line record" and the use of a proper BCP format file would make all of this much easier and MUCH safer if there were a regular pattern of "multi-line records" in the data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply