My name is Lesley Kitchens, and I’m a database administrator at FNC, primarily assigned to supporting the CollateralDNA™ (CDNA) portion of FNC’s business. I’ve been working in IT for more than 22 years now, with the majority of that time spent working as a database administrator and in systems management. Most recently before joining FNC, I worked as a service delivery manager for Oracle. Since joining FNC, I’ve made the transition from Oracle to the SQL Server tech stack. One of the areas I get to use extensively is SQL’s Integration Services technology (also known as SSIS).
Recently, I had an opportunity to build an SSIS package that needed to archive OS-level files (Excel files) on a regular basis. The process was that each month, we generated a certain number of Excel files, and then we would extract the data from those files, perform some manipulation on the data, and then load them into SQL Server. Finally, we wanted to move the Excel files to an archive directory, but not overwrite any previous Excel files that had already been archived. Most of that process was fairly straightforward, from an SSIS perspective. However, the archiving process was a little more complex. After talking through it, we decided to create a new directory with the current date as part of the directory name, and load the Excel files into that directory as the last step of the SSIS package. I had not tried this before in SSIS, but after a little digging, I found a pretty easy solution, which I’ll describe below.
- Declare a variable for the variable directory path. I called mine varDirPath, and set it initially to just d:\test
- I then added a script task to my workflow. I set the ReadWriteVariables property of the script task to be equal to User::varDirPath, which is the variable I created in step 1. I then clicked the Edit Script button inside the script task, and added this:
Dts.Variables("varDirPath").Value = "\\yourservergoeshere\somelocation " + Date.Today.Year.ToString _+ Date.Today.Month.ToString + Date.Today.Day.ToString ' Dts.TaskResult = ScriptResults.Success
Essentially, this code updates the varDirPath directory to be equal to \\yourservergoeshere\somelocation + a foldername with the current date. For example, our last run created a folder named 20141110, in the aforementioned path.
- Next, I created a File System Task, and set the Operation to Create Directory, and then set the SourceVariable property equal to User::varDirPath
That is all you need to do. I can then create another File System Task to move the files, after processing, to the newly created directory containing the datestamp in the directory name. Give it a try and see if it works for you!