asked on 7/16/2008

Date format in SSIS using Derviced Columns

I'm trying to get my DTS packages converted over to SSIS. I have a package that has a datetime field that when converting to a text file needs to be in a format of MMDDYY. Simple in DTS, but change is good eh? I can run this query against my table in SQL 2005 and the output is exactly the way I want it;

REPLACE(CONVERT(CHAR(8),ADJ_DATE,10),'-','')

Output example. 043008 = April 30, 2008

When trying to add this statement in my expression as a derived column I am getting an error message. It's saying function CHAR not recognized. Any ideas on how to make this work in SSIS?
Thank you in advance.

Microsoft SQL Server

Last Comment

nmcdermaid

7/19/2008

ASKER CERTIFIED SOLUTION

nmcdermaid

7/16/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

nmcdermaid

7/16/2008

Actually here's a post which explains option 2 a bit further:

http://blogs.conchango.com/jamiethomson/archive/2007/02/07/SSIS-Nugget_3A00_-Construct-a-timestamp-value.aspx

nmcdermaid

7/16/2008

This one has a big mutha case statement to work out the month name... this implies that the first option above is best as there doesn't appear to be a nice way to format to a month name with a format string.... unfortunate.

http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html

OK I'm finished :)

barrontech

7/17/2008

ASKER

Thanks - I ended up using solution #1. Was very easy. Thanks for the links too. Good information for a beginning SSIS person.

barrontech

7/18/2008

ASKER

I kinda jumped the gun. Still having a minor issue. The conversion itself took place just fine. However, if you look at the datatype in the metadata tab of Data Flow Path Editor it has a data type of DT_STR and a length of 8000. I need the length to be 6. How do I change the length to 6 from 8000? I thought the CHAR(8) function would do that?

nmcdermaid

7/19/2008

Possibly the REPLACE is casting to an unknown (8000) length again.

You can try:

1. Another CHAR(8) around the outside: CONVERT(CHAR(8),(REPLACE(CONVERT(CHAR(8),ADJ_DATE,10),'-',''))
2. Using a data type conversion step to convert from DT_STR(8000) to DT_STR(8)

I'd recommend the first since you've already started down the T-SQL track.