SSIS Expression Help

  • Hi All,

    Here is my question.

    I have Source as an E.G

    ID,Fname,Qty

    1,Gim,2

    2,Kimmy,42

    I want to create a flat file Destination, where it should this logic

    ID = Its fine no changes, same as Source

    Fname = Should be 8 cHARACTER, E.G if my source has Gim, in destion I want Gim-------- (- mean Empty Space), Second E.G Souce has Fname = Kimmy, I want Kimmy---(- mean Empty Space)

    Qty = Should be 6 Character, E.G If my Source has 2, In Destination I want 000002, Second E.G source has Qty = 42, I want 000042.

    The End Result or Flat file should be

    ID, Fname, Qty

    1,Gim ,000002

    2,Kimmy ,000042

    Please advise me, Let me know, if my question is not clear. I want to done this through SSIS. I have little idea, I can use Derived Column, but I want to know what expression should i use.

    Thank You.

  • You need to use a simple trick:

    Fname:= LEFT(8,Fname + "--------")

    A similar expression can be build for Qty.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Quick thought, as this is straight forward in T-SQL, why not do this in the source query?

    😎

    USE tempdb;

    GO

    DECLARE @TESTDATA TABLE

    (

    ID INT NOT NULL

    ,FNAME VARCHAR(8) NOT NULL

    ,QTY INT NOT NULL

    );

    INSERT INTO @TESTDATA(ID,FNAME,QTY)

    VALUES

    ( 1,'John',2)

    ,( 2,'Paul',4)

    ,( 3,'Georg',8)

    ,( 4,'Ringo',16)

    ,( 5,'Peter',32)

    ,( 6,'Paul',64)

    ,( 7,'Mary',128)

    ,( 8,'Paul',256)

    ,( 9,'Art',512)

    ,(10,'Steve',1024);

    SELECT

    TD.ID

    ,STUFF(REPLICATE(CHAR(32),8),1,LEN(TD.FNAME),TD.FNAME) AS FName

    ,REPLICATE(CHAR(48),6 - LEN(CAST(TD.QTY AS VARCHAR(6)))) + CAST(TD.QTY AS VARCHAR(6)) AS Qty

    FROM @TESTDATA TD;

    Results

    ID FName Qty

    --- -------- ------

    1 John 000002

    2 Paul 000004

    3 Georg 000008

    4 Ringo 000016

    5 Peter 000032

    6 Paul 000064

    7 Mary 000128

    8 Paul 000256

    9 Art 000512

    10 Steve 001024

  • Thank guys for reply.

    Koen, I am using 2008 R2 VS, I am not seeing LEFT STRING FUNCTION in derived column. Please advise.

    Eirikur, This file, i am not inserting in sql server. My source is Flat and I want to produce a flat file with a lot of different logic and that

    logic are of them. If you have solution in SSIS please advise.

    Thank You.

  • rocky_498 (8/20/2014)


    I am not seeing LEFT STRING FUNCTION in derived column. Please advise.

    Try using SUBSTRING. There is a good resource here.

    John

  • I am sorry, I am lost, would you mind if you help me with expression?

  • rocky_498 (8/20/2014)


    I am sorry, I am lost, would you mind if you help me with expression?

    Come on, a little effort please. John has linked to the documentation of substring. Read it from top to bottom, especially the examples.

    Then search for the documentation on LEFT and try to find the connection between the two.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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