Can we create folders and files dynamically using ssis ?

Question

Can we create folders and files dynamically using ssis ?

bhavika.chauhan

Mr or Mrs. 500

Points: 523
More actions
April 18, 2011 at 12:12 am

#236879

Hi,
While exporting the data , i need to send it to Excel file. While doing so i want to Create a specific folder and then save the excel file in that particular folder with the date.
Eg: C:\
I want to Create a Folder in C named (Inbox) and in that store my excel file ie : Excel_TodaysDate. Can i do all this things when the package executes.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 1

bhavika.chauhan (4/18/2011)
Hi,
While exporting the data , i need to send it to Excel file. While doing so i want to Create a specific folder and then save the excel file in that particular folder with the date.
Eg: C:\
I want to Create a Folder in C named (Inbox) and in that store my excel file ie : Excel_TodaysDate. Can i do all this things when the package executes.

Hey Bhavika,

Yes you can. There are 2 ways to perform the same. First is using FileSystem Task and second using Script Task (By creating object of System.IO.File). Though they have their own advantage but FileSystem Task is much faster and efficient when it come to perform operation on files. ( e.g. Create/delete Directory, Create/delete/Copy/Move File)

As you are generating destination file name at run time so you need to provide your full file path as connection string expression in your destination adapter.

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

bhavika.chauhan Mr or Mrs. 500 Points: 523 More actions · Answer 2

Hey can any one plz give me a expression to Replace / Rename file name.

I want my file name to be eg: Material_TodaysDate ie Materials_20110418.XLS

please help me with this expresion. I am using File system task to Replace/Rename the file .

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 3

Hi,

One way to do is like this way-

1) Drag n Drop a FileSystem task to the SSIS workflow.

2) Edit it. Specify source and destination connection (Any hard coded excel file destination, which we will rename later dynamically).

3) Give operation name as 'Rename'

4) Create a variable named FilePath(Full path of source variable, i.e C:\Inbox\NewFile.xls that need to be renamed)

5) Select Destination file connection in the Connection Manager's pane. Go to property pane. click on the eclipsis tab in the "Expression" property.

6) Select "Connection String " from the property column and write the followingn in expression :

SUBSTRING( @[User::FilePath] , 1, LEN( @[User::FilePath]) - LEN( RIGHT(@[User::FilePath] , 4))) + (DT_WSTR, 1252)YEAR( GETDATE()) + (DT_WSTR, 1252)MONTH( GETDATE()) + (DT_WSTR, 1252)DAY( GETDATE()) + ".xls"

This will work fine and allow you to rename the file using File System task. You can also rename the file using script task, but this is the best way that I can think of.

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!