October 5, 2015 at 10:42 am
Hi everyone,
From SSIS I need to export data to a CSV with spaces padding the end of each field before the delimited value. For example if I have three fields that are Nvarchar(10) I need it to be this:
Testing ,Test123 ,Again {end of line}
instead of this:
Testing,Test123,Again{end of line}
It's like it can do fixed width or delimited but not both. Is this possible without having to force the spaces into the data coming back from SQL? I already have the SSIS package written to export the data to CSV which works great, just need to find some way to add the spaces to the end of each column to satisfy requirements on the system being exported to. Also the commas need to be there too.
Thanks --
October 5, 2015 at 4:16 pm
So, just for giggles, what is going to consume this file? It seems odd that you have to delimit the file AND make the columns fixed length.
So to answer the question, I'm not 100% how SSIS will treat padded spaces. I would recommend trying to pad the spaces in your source component query (using REPLICATE) and also use CAST to change the data types from varchar to char before it enters the data flow's pipeline. Another option may be to use a Derived Column transformation in your data flow to use an expression to pad the spaces. I'm just not sure how SSIS will react when it writes the file.
I'll test a little and reply, but I thought that I'd give you a head start on where to look also.
October 5, 2015 at 4:26 pm
I tested this and it works just fine. If you pad the varchar values in the source component, they will retain their trailing spaces all of the way into the destination. Here's an example of a table that contains just firstname and lastname and how to use REPLICATE to pad the values. Keep in mind that this means that you'll have to know the desired length ahead of time and build it into the query.
DECLARE @myTable TABLE(FirstName varchar(20), LastName varchar(20))
INSERT INTO @myTable
SELECT'John', 'Rowan' UNION ALL
SELECT 'John', 'Doe'
--==== Use this type of statement to pad the values in the Source Component
SELECTLEFT(FirstName + REPLICATE(' ', 20), 20) as FirstName
, LEFT(LastName + REPLICATE(' ', 20), 20) as LastName
FROM@myTable
October 6, 2015 at 10:52 am
You can try setting the columns up as CHAR/NCHAR(x) instead of varchar or nvarchar... that should fix your width...
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply