Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Expression Help Expand / Collapse
Author
Message
Posted Tuesday, August 19, 2014 8:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 243, Visits: 992
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.
Post #1605263
Posted Wednesday, August 20, 2014 12:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1605297
Posted Wednesday, August 20, 2014 2:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:26 AM
Points: 2,532, Visits: 7,090
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
Post #1605315
Posted Wednesday, August 20, 2014 8:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 243, Visits: 992
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.
Post #1605479
Posted Wednesday, August 20, 2014 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 5,487, Visits: 10,335
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
Post #1605488
Posted Wednesday, August 20, 2014 9:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 243, Visits: 992
I am sorry, I am lost, would you mind if you help me with expression?
Post #1605647
Posted Thursday, August 21, 2014 12:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1605667
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse