Being a newbie to SSIS I'm not sure of the most efficient method of converting a DateTime object to a String.

I'm from a C# background where this would be easy using DateTime.ToString("YYYYMMdd"). I want to use the date in a file name so don't require most of the parts.

I'm sure I could do this using a script task to produce a file name for each row of data in my table and add that filename to the dataset but it seem like overkill to do something that should be simple. Alsoas I'm supposed to be getting to grips with SSIS I shouldn't keep running back to what I know.

My current approach is to derive a column and build up an expression to convert the date into a string. The only problem being that it doesn't work.

The expression I'm working with is:

(DT_WSTR, 50)([OrgName] ) + "_" + (DT_WSTR, 50)( [PayrollName] ) + (DT_WSTR, 4)(YEAR( [ProcessedDate] )) + (DT_WSTR, 2)(MONTH( [ProcessedDate] )) + (DT_WSTR, 2)(DAY( [ProcessedDate] )) ".txt"

Can anyone see where I'm going wrong?

All comments greatly received.

Cheers

Ben

Doesn't work ? What is the error you are getting ?

Use dt_str instead. Here is an example I am using successfully...

Code Snippet

"FileName_" + (DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) +Right("0" + (DT_STR,4,1252) DatePart("n",getdate()),2) +".txt"

• Proposed as answer by Walter Habegger Friday, January 30, 2015 8:02 AM

Stupidly, I discovered that I'd missed the last + before appending the ".txt" to the file name.

• Marked as answer by Rafael SalasMVP, Moderator Wednesday, May 13, 2009 9:42 PM

Maybe this answer can help someone in this post.

(DT_WSTR, 50) (DT_DBTIMESTAMP) @[System::StartTime] => 2009-05-13 16:24:54

Good luck!

• Proposed as answer by Siggy01 Thursday, February 21, 2013 5:36 PM

wow - that answer seemed weird to a newbie but it works!  Thanks!!!

Hello,

I and, it seems, and the rest of the world are having problems converting between string and datetime fields in SSIS.  I have been programming  as my work for 40+ years now and  think that MS have gone back in time at least this period in the SSIS date handling.  Having to code at this level these days is absolute ____.  We should have default conversions with the option of personal control, when needed, requiring (maybe) code like your example.  Even T-SQL recognises date formats like '01 jan 2011' and just gets on with it.  I find this particular problem very irksome, annoying and totally unnecessary and it is costing my employer a lot of money.  Microsoft, wake up!!

Regards,

Jonno.

With this same code I was getting an error on datatype because when I created the variable I didn't specify STRING , be sure to specify string before create the Expression.

• Proposed as answer by RaibeartS 11 hours 14 minutes ago
• Unproposed as answer by RaibeartS 11 hours 14 minutes ago

Try the following. It will work fine fore the date portion:

(DT_WSTR, 4) Year(dateadd("day",-1,getdate())) + ( month(dateadd("day",-1,getdate())) < 10 ? "0" + (DT_WSTR, 4) month(dateadd("day",-1,getdate())):(DT_WSTR, 4) month(dateadd("day",-1,getdate()))) + ( day(dateadd("day",-1,getdate())) <10 ? "0" + (DT_WSTR, 4) day(dateadd("day",-1,getdate())):(DT_WSTR, 4) day(dateadd("day",-1,getdate())))