Sunday, January 10, 2010

Create a file using SSIS file system task

I have seen a lot of queries on forums to create an excel file of a particular format and then use it inside a data flow task. File System Task does come to the mind but unfortunately there is no "Create" operation inside File System Task.
But there is a workaround with little bit of stage setting.
Suppose an excel file is to be created on a daily basis and the name of the file should be the current date (20100110, 20100111 etc.)
First it is required to create a template excel file (same as the file required to be created daily). Then a file system task to copy this template file to the destination folder (folder where the new file is to be created). Then use another file system task to rename the copied file with system date.
Step1: Create a template excel file (Sample.xls) in C:\ABC folder
Step2: Create 3 variables (string type and package scoped) named :
Dest with value as :C:\XYZ (folder where new file is to be created)
Final with "Evaluate as Expression" property set as TRUE. then set the
expression as: "C:\\XYZ\\" +REPLACE(SUBSTRING( (DT_WSTR,30)GETDATE(),1,10),"-","")+ ".xls"
Rename with value as : C:\XYZ\Sample.xls
Step3: Take a File System Task and open the File System Task editor.
Select Operation as "Copy File"
Set IsDestination path variable to TRUE and select Dest from drop down box as variable.
Keep IsSourcePath variable as FALSE and create a connecion manager for C:\ABC\Sample.xls
Step4: Take one more File System Task and open the File System Task editor.
Select Operation as "Rename File"
Set IsDestination path variable to TRUE and select Final from drop down box as variable.
Keep IsSourcePath variable as TRUE and select Rename from drop down box as variable.

Select the 2nd File System Task, hit F4 (to go to its properties) and set "Delay Validation" as TRUE. The new excel file with current date as its name can be verified in folder C:\XYZ

5 comments:

  1. I am having issues with the: Rename with value as : C:\XYZ\Sample.xls

    I am using UNC names is that an issue? I keep getting invalid character in path

    ReplyDelete
  2. I used the above and it really helped!!
    Thank You,

    Sungeeta

    ReplyDelete
  3. Get stuff... really works... very helpfull...

    ReplyDelete
  4. This was very helpful and straightforward. Thanks.

    ReplyDelete