March 3, 2008 at 10:39 am
Hi,
I need to produce a fixed length text file for import into an ISAM environment. I have overcome the date format issue but need to produce the numeric fields in a right justified, left 0 padded format. Does anyone have any ideas?
Thanks.
March 3, 2008 at 11:17 am
Are you exporting via a view or just a table?
You will have to either pad the column in SQL or using a SSIS expression.
SELECT RIGHT('0000000000' + CONVERT(VARCHAR(10),1234567),10) AS [10 LENGTH]
March 3, 2008 at 11:18 am
The above is an example of how to do it in TSQL
March 3, 2008 at 11:26 am
Thanks for the reply. In my SSIS package, my source is a sql table. From there, I perform a couple of transformations and derived column tasks. My original plan was to then output to my flat, fixed file and be don with it. My only obstacle at this point is the format of the numeric cols. I am not a script writer but will learn if that is my only hope. I have experimented with different code pages with no luck.
March 3, 2008 at 11:55 am
Create a data conversion task. Change the numeric columns to string. Then use a derived column tasks and put this in. Note this is to make a 10 digit padded number. You should change the number of zeros and the number 10 to fit your need.
RIGHT("0000000000" + [MyColumn],10)
Now all you have to do is choose the modified column in your destination.
March 3, 2008 at 12:13 pm
Thank you Adam:) You are my new hero.
March 3, 2008 at 12:45 pm
NP, glad I could help. 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply