Sql server output to csv (pipe) file.
We are one sql 2005 and we need to output our tables to csv files. I have been able to output the files using SSIS, bcp etc.. but the format is not correct for the vendor. Example: We have a column - Facility_ID char(15). The facility id for many of the facilities is only 5 digits long. It looks like this in the pipe delimited output file. Just focus on the first field. it is 5 characters long, but is showing all the spaces of the char(15). |12345 |location|city|state|phone I need the output to look like this: |12345|location|city|state|phone - they cannot have any spaces between the last character and the pipe. I have 20+ files to output this way and need a good way to do this. I have tried bcp and dts, but the output always contains the spaces up to the length of the datatype. Please help.
February 26th, 2011 7:56am
Hello, You might have spaces with your ID in the table....Try this and see if you can achieve what you are trying to do 1--Get the data flow, then inside data flow , use OLE DB Source, use the query SELECT RTRIM(LTRIM(CAST(Facility_ID AS VARCHAR(15)))) AS Facility_ID , Col2,Col3 from dbo.MyTable. After that use the Flat File Destination and then map the columns.... if you have already connection to flat file destination, remove that and create new. Execute your package and let us know if it is working the way you want.... Edited: I just checked if you have CHAR , then it will take the space for number of characters you have defined , in your case CHAR(15) , as it Char data type takes all the memory even data is not present. The above query will solve your issue , convert to VARCHAR before you insert into destination. Thanks Aamir
February 26th, 2011 8:11am