Tuesday, May 10, 2011

Data-dump in Dynamic Flat file with todays date

This is with reference to a question asked my an online friend regarding how can we do a daily Datadump on a flat file with a todays date as a file name.

To show that I will use an example in which we will dump a table "Student" from SQL Server database into a file with name appended with todays date and time eg. DataDump_201105101108.txt. 20110510 being the date and 11:08 as time of load.

1. Lets have a look at our table.

2. Now create a simple package with a Data Flow task which dumps data from Student to a flat file let say with Dummy.txt. SSIS is metadata oriented so at the time of creation of package you would need to have a dummy txt file so that mappings can be created.

3. Not the actual work starts. Go the Flat file Connection Managers Properties and expand Expressions.
 Choose ConnectionString as Property.

4. In Expression Builder window create a expression as
"E:\\DataDump_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".txt"

5. Run the package it will create a flat file with required File name.