June 18, 2008 at 1:26 pm
hello all
i am trying to do a package where i try to transform the table data to a text file.
the user wants the text file in non-delimited format and the field's in the text file exactly containing the field width
Say for example name field is 50 characters and the data in the table is only 20 characters they want 30 spaces after the name in the text file.
Can anybody help or suggest on this?
June 19, 2008 at 12:06 am
Hi, I have a similar thing going on and I have used the Space function to insert standard numbers of spaces between valid fields where I need filler in my fixed length file.
My code looks something like this:
SELECT field1 + space(10) + field2 + field3 + space(50)
FROM tablename
Etc. Etc. Etc.
In some cases, I also have fields where I have a number field or something that has to be standard length with leading zeros. So, if, say, the field needs to be 7 characters long, but in some cases the number is not as big as 7 digits, then I do the following, since this is all output as text anyway:
Case Len(field1)
WHEN 7 THEN cast(field1 as nvarchar(7))
WHEN 6 THEN '0' + cast(field1 as nvarchar(6))
WHEN 5 THEN '00' + cast(field1 as nvarchar(5))
WHEN 4 THEN '000' + cast(field1 as nvarchar(4))
WHEN 3 THEN '0000' + cast(field1 as nvarchar(3))
WHEN 2 THEN '00000' + cast (field1 as nvarchar(2))
WHEN 1 THEN '000000' + cast(field1 as nvarchar(1))
ELSE '0000000'
END
I hope some of that is of some use to you. Good luck!
June 19, 2008 at 12:36 am
If the table is not too big, I would create a table valued function where I specify everything as char(x), and then insert the data into here.
Wont this force the correct length when transferring?
Also, when you define your text file, define the fixed width, which will tell SSIS where to start and where to stop with a specific field.
~PD
June 19, 2008 at 12:39 am
Yeah, actually mine is a little more involved than what I noted there.
I, in fact, do insert such values into a table and then output them from there. Even the filler created with Space(10), etc., is inserted into fields and output straight from the table.
Makes life a bit simpler than having to run a query from within the SSIS to output it.
But basic idea is the same anyway.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply