SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Expression Help


SSIS Expression Help

Author
Message
rocky_498
rocky_498
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 1369
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27125 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14888 Visits: 18591
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-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 1369
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13950 Visits: 15927
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-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 1369
I am sorry, I am lost, would you mind if you help me with expression?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27125 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
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