format the output column in destination .csv

Question

format the output column in destination .csv

arkiboys

Hall of Fame

Points: 3958
More actions
July 21, 2014 at 7:30 am

#309266

Hello,
in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the result into .csv format file.
One of the result columns is Date.
The stored procedure returns dd/mm/yyyy but when I look at the .csv destination file, it shows the column as dd/mm/yyyy 00:00:00
Question:
How do I get rid of the 0's after the date in the destination .csv file please?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

arkiboys (7/21/2014)
Hello,
in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the result into .csv format file.
One of the result columns is Date.
The stored procedure returns dd/mm/yyyy but when I look at the .csv destination file, it shows the column as dd/mm/yyyy 00:00:00
Question:
How do I get rid of the 0's after the date in the destination .csv file please?

Lookup the CONVERT function in Books On Line. That'll do it for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arkiboys Hall of Fame Points: 3958 More actions · Answer 2

Jeff Moden (7/21/2014)
arkiboys (7/21/2014)
Hello,
in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the result into .csv format file.
One of the result columns is Date.
The stored procedure returns dd/mm/yyyy but when I look at the .csv destination file, it shows the column as dd/mm/yyyy 00:00:00
Question:
How do I get rid of the 0's after the date in the destination .csv file please?
Lookup the CONVERT function in Books On Line. That'll do it for you.

This is the sql I am using in stored proc:

AsOfDate = convert(varchar(11),AsOfDate,103)

It returns something like dd/mm/yyyy but in th edestination .csv I get dd/mm/yyyy 00:00:0000

I tried using derived column but that doe snot seem to do it.

Any suggestions please?

Thanks

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Ah, my apologies. You said that in your original post and I was in too much of a hurry. I'm not sure how to do this on the SSIS side of the export. There should be something in SSIS that says use the result set of your proc as all text data instead of converting the text (dates) back to a datetime.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lempster SSCoach Points: 15651 More actions · Answer 4

You shouldn't need a Derived Column transformation to do this. If you click on 'Advanced' in your Flat File Connection Manager Editor, what DataType is the receiving column set to? It should be DT_STR and the OutputColumnWidth property should be set to 11.

Regards

Lempster

arkiboys Hall of Fame Points: 3958 More actions · Answer 5

Lempster (7/22/2014)
You shouldn't need a Derived Column transformation to do this. If you click on 'Advanced' in your Flat File Connection Manager Editor, what DataType is the receiving column set to? It should be DT_STR and the OutputColumnWidth property should be set to 11.
Regards
Lempster

I did that but in preview I can still see the leading 00000

And also the 00000 appear in the .csv file in column AsOfDate.

Any suggestions?

Thanks

Lempster SSCoach Points: 15651 More actions · Answer 6

You didn't answer my question. Is the DataType property for that column set to DT_STR?

arkiboys Hall of Fame Points: 3958 More actions · Answer 7

Lempster (7/23/2014)
You didn't answer my question. Is the DataType property for that column set to DT_STR?

DT_STR

Lempster SSCoach Points: 15651 More actions · Answer 8

Hmm, in that case, I don't know why you are seeing that behavior. I put together a quick SSIS package to do the same as you are doing in your package and my dates in the CSV output file don't have any time portion. Could it be to do with the Regional Settings on your pc/server I wonder?

Sorry, I don't think I can help any further. :unsure:

arkiboys Hall of Fame Points: 3958 More actions · Answer 9

Lempster (7/24/2014)
Hmm, in that case, I don't know why you are seeing that behavior. I put together a quick SSIS package to do the same as you are doing in your package and my dates in the CSV output file don't have any time portion. Could it be to do with the Regional Settings on your pc/server I wonder?
Sorry, I don't think I can help any further. :unsure:

Thank you

Tim Mitchell SSCoach Points: 15681 More actions · Answer 10

You can get around this by creating a new column of type DT_DBDATE and using this new column to write to the CSV file.

First, add a new derived column transformation between your source and destination.

Next, add a new column to the derived column transformation. In the Expression field, use something similar to the following: (DT_DBDate)MyOriginalDateColumn. Replace MyOriginalDateColumn in this sample with the name of your date column from the source query. Use the <add as new column> selection to be sure this is a new column.

Finally, in your flat file destination component, use the new date field you just created in place of the original date field from the source.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices