Export to .csv
-
Hi there
I am exporting some data to .csv and when I look at the file in notepad, it shows the dates with additional characters at the end, I dont want these characters because the file is to be used by a UNIX system.
The dates show like this:
2003-10-02 15:59:56.827000000
The data type is datetime and the tables / server are on the default collation
-
SQLSlammer (7/31/2009)
Hi thereI am exporting some data to .csv and when I look at the file in notepad, it shows the dates with additional characters at the end, I dont want these characters because the file is to be used by a UNIX system.
The dates show like this:
2003-10-02 15:59:56.827000000
The data type is datetime and the tables / server are on the default collation
You have to format your date according to your requirements. What you see is the complete date/time to string including tenths of a second. To format and build needed type of date, use the derived column transformation with the appropriate expression.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
The problem seems to be that it is combining to columns, the ID and the date are showing as one column,
such as
2008-11-29 01 01 40 890000000 322085
even in the column names, when I go to advanced on flat file config manager it shows the column name like:
LastUpdatedDate 322088
322088 is the ID for the first row of data.
This is pipe delimited, why does it think the first row of data is part of the last columns field name?
-
But this issue is different than your original question. Why not create another question in the forum and stay on topic?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
I just noticed it and its been happening all the time.. so in my mind it is still on topic. Personally I think its better to keep it in one topic as its not really a competely separate issue, it is related to the first issue in the respect that I am trying to create a .csv and it's not doing what I want.
I didn't understand your first answer, can you tell me where to change that?
I really dont understand why SSIS doesn't default to the right thing, the data type is date/time, in the query I have even used CONVERT(DATETIME, FIELD1, 103) where is all this extra zeros and 89 coming from? its not in the sql server table.
Why is it not picking up the first column is the data row, it seems to think it is part of the last column from the previous row, it is an integer so there isn't any obscure pipe characters confusing it
-
I'm getting confused a bit. Are you talking about generation of CSV or reading from CSV? Can you start from the beginning by including more details step-by-step what you are trying to accomplish? Thanks!
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Ok, lets deal with date problem first
I am trying to export data to .csv from a sql server 2005 sp3 table using SSIS. This export normally occurs using a BCP command and the dates look as expected. like so:
2008-11-29 01:01:40.890
The BCP command is:
bcp "select * FROM table" queryout G:\table.csv -c -k –t “|” -S server -U user -P password
But when we use SSIS the dates are showing like:
2008-11-29 01 01 40 890000000
Why is it adding these zeros? and removing the colons from the time?
If I need to change the date output format? can you talk me through where exactly I need to change it?
-
SQLSlammer (7/31/2009)
Ok, lets deal with date problem firstI am trying to export data to .csv from a sql server 2005 sp3 table using SSIS. This export normally occurs using a BCP command and the dates look as expected. like so:
2008-11-29 01:01:40.890
The BCP command is:
bcp "select * FROM table" queryout G:\table.csv -c -k –t “|” -S server -U user -P password
But when we use SSIS the dates are showing like:
2008-11-29 01 01 40 890000000
Why is it adding these zeros? and removing the colons from the time?
If I need to change the date output format? can you talk me through where exactly I need to change it?
Can you do a small test for me? Double-click on the line before the flat file destination. Go to "Data Viewers" and click "Add". Just click OK button in the dialog. Now run your data flow and tell me in what format do you see your date/time displayed ?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
With the data viewer added, the dates look the same as before (incorrect) in the .csv file,
but show properly within the data viewer.
-
Can you show what is the flat file configuration you have used for your flat file destination?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
How do I show that?
Which part do you mean?
-
If you post the complete package you are using, that would be the easiest.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Here you go
-
Hmm.. It looks fine to me. I see you have tried to use CONVERT function in your select statement? This should work. Did you reset the flat file connection manager after redefining to use CONVERT? The type of the column should be STRING instead of DB_TIMESTAMP.
Can you give it a try ?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
The convert code is more me trying to force it to work properly, its not actually doing anything because the column data type is datetime anyway.
I changed the output columns to string, made no difference.
I am starting to lose faith in SSIS to be honest, tempted to go back to DTS. This is a really really simple operation and it's proving to be a headache... I have programmed DTS packages a thousand times more complex than this is half the time i have already taken on this.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply