Sunday, February 19, 2012

Daily Import of file with date

Hello,

I have an SSIS package that imports daily files that have the date in the file name. I'd like my SSIS package to pickup today's file and run the package.

I've been using expression builder to create the file name but it drops the leading zeros in the month and the day.

My Import file looks like this: SRMSNotes_DATA_20080118.txt

Using this code: "D:\\importdata\\srmsdata\\SRMSNotes_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

SSIS looks for this file: SRMSNotes_DATA_2008118.txt

How do I make SSIS add the leading zeros?

Thanks for you help"D:\\importdata\\srmsdata\\SRMSNotes_DATA_" +
(DT_WSTR, 4) YEAR( GETDATE())

+
((DATEPART("mm",GETDATE()) ) < 10 ? ("0" + (DT_WSTR, 2)MONTH( GETDATE())) : (DT_WSTR, 2)MONTH( GETDATE()))

+
(DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

You'd need to apply something similair for the DAY part|||To Get this:
20080213004235
YYYYMMDDHHMMSS

I use:
(DT_WSTR, 4) YEAR(GETDATE())
+
RIGHT(("0" + (DT_WSTR, 2) MONTH( GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DAY( GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("hh", GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("n", GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("s", GETDATE())),2)

To Get this:
20080213004235
YYYYMMDDHHMMSS

No comments:

Post a Comment