Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Expression Help


SSIS Expression Help

Author
Message
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16455 Visits: 13207
You need to use a simple trick:

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

A similar expression can be build for Qty.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6743 Visits: 17722
Quick thought, as this is straight forward in T-SQL, why not do this in the source query?
Cool

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

rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
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.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7466 Visits: 15142
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
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
I am sorry, I am lost, would you mind if you help me with expression?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16455 Visits: 13207
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search