Export to .csv

Question

Export to .csv

SQLSlammer

Ten Centuries

Points: 1045
More actions
July 31, 2009 at 5:03 am

#156047

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

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

SQLSlammer (7/31/2009)
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

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/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 2

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?

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

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/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 4

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

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/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 6

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?

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

SQLSlammer (7/31/2009)
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?

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/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 8

With the data viewer added, the dates look the same as before (incorrect) in the .csv file,

but show properly within the data viewer.

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

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/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 10

SQLSlammer

Ten Centuries

Points: 1045

July 31, 2009 at 9:47 am

#1033035

How do I show that?

Which part do you mean?

COZYROC One Orange Chip Points: 28499 More actions · Answer 11

If you post the complete package you are using, that would be the easiest.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 12

SQLSlammer

Ten Centuries

Points: 1045

July 31, 2009 at 10:01 am

#1033047

Here you go

COZYROC One Orange Chip Points: 28499 More actions · Answer 13

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/

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 14

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.