August 19, 2014 at 8:18 pm
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.
August 20, 2014 at 12:36 am
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
August 20, 2014 at 2:02 am
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
August 20, 2014 at 8:54 am
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.
August 20, 2014 at 9:08 pm
I am sorry, I am lost, would you mind if you help me with expression?
August 21, 2014 at 12:18 am
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