Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS Expression Help

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-19 : 22:16:40
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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 08:24:31
1. Set up the flat file destination with the desired column widths
2. For the Qty, use a Derived Column transform with an expression like this:

RIGHT("00000" + (DT_WSTR,6)Qty,6)
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-20 : 10:56:47
Thank for reply.

1. Set up the flat file destination with the desired column widths = I need your help, I couldn't find out how i can set up this one.
2. For the Qty, use a Derived Column transform with an expression like this: = Looks great. Thank You.

Thank You.

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 13:35:49
1. choose the Flat File destination transform from the dataflow tab toolbox. Choose Fixed width as the type and under Advanced, set the widths of the columns.
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-20 : 17:30:35
I think i got that part, but the problem is, how i can set new data in next line..

I am getting as an example

IDFnameQty
1Gim22Kimmy42

I want the data look like

IDFnameQty
1Gim2
2Kimmy42

Just to FYI my source is

ID,Fname,Qty
1,Gim,2
2,Kimmy,42
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-20 : 17:54:42
Sorry to say I got it,

I am not getting what I want, Here is my source sample data

Tele8005934191201408190343LEAD000001000000871055055080000004093038009999999
Tele8005934191201408190343SBQL000001000000871055055080000004093038009999999
Tele8009130035201408190344LEAD000001000000314019126040000004093068009999999
Tele8009130035201408190344SBQL000001000000314019126040000004093068009999999
Tele8008489429201408190345LEAD000001000000432056143720000004093088009999999
Tele8008489429201408190345SBQL000001000000432056143720000004093088009999999
Tele8008489429201408190346LEAD000001000000151024128350000004093108009999999
Tele8008489429201408190346SBQL000001000000151024128350000004093108009999999

what i want like this

Tele80059341 91201408190343 LEAD 00000100000087105505508000000409 3038009999999
Tele80059341 91201408190343 SBQL 00000100000087105505508000000409 3038009999999
Tele80091300 35201408190344 LEAD 00000100000031401912604000000409 3068009999999
Tele80091300 35201408190344 SBQL 00000100000031401912604000000409 3068009999999
Tele80084894 29201408190345 LEAD 00000100000043205614372000000409 3088009999999
Tele80084894 29201408190345 SBQL 00000100000043205614372000000409 3088009999999
Tele80084894 29201408190346 LEAD 00000100000015102412835000000409 3108009999999
Tele80084894 29201408190346 SBQL 00000100000015102412835000000409 3108009999999

If i am using Fixed Width from Flat File Format here is the data that I am getting
my out put is messed up.

if I use Fixed Width with row delimiters, I am getting above resut.

Please advise.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 18:46:43
Looks like you want to add space between the columns. Is that it?
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-20 : 20:39:18
Yes you are right, but that space depend on logic, For E.g in T-SQL
SPACE(10 - LEN(MYFIELD)), Here is the logic that I want to use. System can only accept FIXED WIDTH file .txt format.
As an example, if source is FNAME = Gim and in destination I gave FNAME 10 character, so in Destination FNAME = GIM------- (- IS SPACE)

Make sense?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 21:10:48
Makes sense but easy to achieve in ssis. Set the column widths you want. They are then fixed. Siss will do the rest. I cant see what the problem is.
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-20 : 23:19:50
I agree, Here is my sample source flat file data

Id,Fname,City
1,Smith,ZA
2,Anthony,MA

I select Flat File Destination ==>Flat File Format (Fixed Width) and IN Advance change Out Put Column Width. Here is result that I am getting.

1 Smith ZA 2 Anthony MA

But I want
1Smith ZA
2Anthony MA

Please let me know what I am doing wrong, Its urgent. Thank You.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 05:53:14
Are you saying you want multiple lines out for each row in?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 06:15:49
What is your line end character on you input file?
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-21 : 10:07:18
Are you saying you want multiple lines out for each row in? = Yes, depend on logic.

What is your line end character on you input file? = I am sorry I don't understand, Here is my sample file

Id,Fname,City
1,Smith,ZA
2,Anthony,MA

Please let me know, if you want info.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 10:47:29
You said, "Here is my sample source flat file data"

So, you need a Flat File Source source element in your data flow. In there you have a Flat File connector which specifies Header Row Delimeter. What do you have there?

Also you said, "multiple lines out for each row in? = Yes, depend on logic." So...what is the logic? Have you tried to implement that?
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-21 : 12:07:57

So, you need a Flat File Source element in your data flow. In there you have a Flat File connector which specifies Header Row Delimiter. What do you have there? = in sample data e.g, I am using
Comma {,} as a Row Header Row Delimiter. Above is my Source as an e.g

Also you said, "multiple lines out for each row in? = Yes, depend on logic." So...what is the logic? Have you tried to implement that? = the logic is,

This is T-SQL Logic "SPACE(10 - LEN(MYFIELD))", Here is the logic that I want to use. System can only accept FIXED WIDTH file .txt format.
As an example, if source is FNAME = Gim and in destination I gave FNAME 10 character, so in Destination file, FNAME should be = GIM------- (- IS SPACE)

Please let me know, if you need more explanation?

Thank You



Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 12:42:17
"Comma {,} as a Row Header Row Delimiter"

That's probably your problem. Isn't your header row on its own line? Then a comma is not the row delimiter. Try {CR}{LF} instead.

Also, you haven't shared your logic for creating multiple output lines for a single input line.

FWIW I just built a package using flat file inputs and outputs. The input is delimited text, the output is fixed. SSIS correctly builds the output file and pads columns with spaces when necessary.

Took about 2 minutes to put together.
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-08-25 : 12:21:21
Awesome... I got it. This question is Answered by gbritton.

Thank You.
Go to Top of Page
   

- Advertisement -