I have an table called customer where i am selecting few columns and making its fixed length,where i need to send the values to SSIS packages for an fixed length output and is written in a text file
customerID:10
Mobilenumber:11
Emailaddress:256
select customerID,mobilenumber,Emailaddress from customer
I want to make sure my customerID is always length of 10, mobile number 11, emailaddress 256.
Well, you can use the LEN() function to find the length of a String value in SQL Server, for example, LEN(emp_name) will give you the length of values stored in the column emp_name.
A fixed-length column requires the defined number of bytes regardless of the actual size of the data. The CHAR data type is of fixed-length. For example, a CHAR(25) column requires 25 bytes of storage for all values, so the string “This is a text string” uses 25 bytes of storage.
Go to the Connection Manager you defined for your flat file. In the left box, select General and set the Format to "Fixed Width". Then in the left box, select Advanced and here you can set the OutputColumnWidth for each field.
You can use SQL length functions in the SELECT statement and other data manipulation statements. Length functions return the length of a column, string, or variable in bytes or characters. For the syntax of these functions, see the Expression segment in the IBM® Informix® Guide to SQL: Syntax .
You can cast them as fixed-length string datatypes, as in:
select cast(customerID as char(10)) as customerID,
cast(mobilenumber as char(11)) as mobilenumber,
cast(Emailaddress as char(256)) as Emailaddress
from customer
If you come across any oddity while working like this, it may be helpful to keep your ANSI_PADDING settings in mind.
Another way to do this might be:
select left(Emailaddress + replicate(' ', 256), 256)
Though this method is often just to apply similar logic from other languages to T-SQL.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With