SSIS Export to flat file data conversion

1
votes

I have an SSIS Package. Its pretty simple. It has a data source that has the following query and a flatfile connection manager. Its a fixed length file and mapping done correctly.

The data source queries a primary key column. The output format for the file for this column should be from e.g.: 000000156 if the source column has a value 156. So the output format for this column should be of length 9 with padded zeroes.

In order to achieve this I wrote my query as follows:

select left(('000000000' + case(clientid as varchar(9)), 9) as clientname from client

I tested the output of the datasource and it returns 000000125 for instance. I mapped the columns of the source to the flatfile output with correct length.

But when I tried to see either the output of the file or preview flatfile connection manager I only see a '125 ' in the first column, which means I dont see the padded zeroes and I have spaces. I am expecting the first column to be of the format '000000125'. Not sure What I am missing and I am new to SSIS.

1
ssisdata-conversion
In the output of your datasource, make sure you are using a string datatype for that column, and not a numeric. – Tab Alleman
@TabAlleman That worked Tab. Thanks a Ton. I dont know how to mark urs as an answer as you have commented in here. – SARAVAN
left(('000000000' + case(clientid as varchar(9)),9) Er, I'm assuming you mean RIGHT(), not LEFT(). As written, this will always return '000000000'. Assuming cast() instead of case. – Bacon Bits

1 Answers

2
votes

Check the output of your DataSource; it sounds like you are using a numeric datatype. Try changing it to a string.