Monday, January 05, 2009

Creating directory using SSIS

Creating a directory/folder at a dynamic path using SSIS sometimes becomes complex job as I feel and can understand by reading questions posted on various SSIS forums, File System task is not good in handling dynamic path

Here as an example and answer to a question posted on MSDN forum I would demonstrate how we can create a directory of today's date.

1) Create a variable varDirPath of string datatype


2.a) Create a Script task and give varDirPath as ReadWriteVariable


2.b) Use below code to set varDirPath value as the path of directory to be created
Public Sub Main()
'set dir path
Dts.Variables("varDirPath").Value = "D:\" + Date.Today.Year.ToString _
+ Date.Today.Month.ToString + Date.Today.Day.ToString

Dts.TaskResult = Dts.Results.Success
End Sub

this will create path like D:\20090115

3) Use File System Task and configure like below pic:


4) Execute the package

6 comments:

Anonymous said...

Check how you can connect to multiple severs and get the information

http://www.abhyast.com/abhyastcom/post/How-to-connect-to-multiple-servers-dynamically-and-collect-information-using-SSIS.aspx

Anonymous said...

This was exactly what I was looking for. Thanks

Anonymous said...

thank you for this guide! I've followed this guide and was able to create a folder titled with the date.

however, i'm trying to move files into this newly created folder, but im running into issues. can you assist?

Anonymous said...

in regards to the post above, what happens is, is that i end up moving the file into the same directory as the folder, but not INSIDE the folder. any suggestions?

Rahul Kumar said...

@Last Comment: Above post is on creating directory with current date, it doesnt move any file.
I guess you are looking to move a file to a new location(may be path created at runtime). The post for you is
http://sqlserversolutions.blogspot.sg/2009/04/copyrename-file-using-file-system-task.html

Hope it helps

Anonymous said...

@Last Comment: thanks, i've been looking at that guide as well. i was able to create a new directory with current date, and in another package, i was able to rename/move files,

but im having trouble piecing the two together. I'd like to create a package where when i execute,

1. create main backup folder
2. create directory with current date
3. take files from source, and move to directory with current date

if i run the package again on another date, it will create a new directory with date as name, and move files from source to that newly created directory. right now, my package looks like this:

1. File System Task - Create Directory named backup
2. Script Task - vb script to set date name
3. File System Task - Create Directory using step two
4. Foreach Loop Container that contains File System Task to rename

but i feel like im missing a step or variable. any suggestions? when i execute, the files get moved to the main backup folder, but not inside the date folder (thats inside the backup folder)

Post a Comment