Moving Excel or other files using MS Integration Services (SSIS)

The Discerning Analyst always have, and will always need integration as part of his skill sets in his craft. To be able to move data automatically, by database trigger, scheduled task or scheduled SQL Jobs from whatever format excel, text file, xml etc etc to a destination database,shared folder or ftp location across the organization, is a must have skill set.

        On this guide you can learn some extra skills on about basic manipulation of an excel file using SSIS.

Requirements: Visual Studio 2010 or higher with SQL Data tools, Patience.

1. Open visual studio and create a new file ->> project

2. After naming the project and specifying the project location, go to business intelligence templates and choose integration services projects.

3. Control flow and other tabs will now open. Drag drop script task from the SSIS Toolbox to the control flow area.

4. Click the script task then click on the project variables encircled on the image below.

5. Add the variables same with the table below on each column on the variables pane.

Name Data Type Value Expression
VarDestinationFullPath String D:\09.Projects\07.Data-Jackal\Destination-Location\ @[User::VarDestinationPath]+@[User::VarFileName]
VarDestinationPath String D:\09.Projects\07.Data-Jackal\Destination-Location\
VarFileName String
VarFolderFullPath String D:\09.Projects\07.Data-Jackal\Original-Location\ @[User::VarFolderPath]+ @[User::VarFileName]
VarFolderPath String D:\09.Projects\07.Data-Jackal\Original-Location\

6. After declaring the variables right click the script task. Then click edit.

7. Assign the read only variable and read write variable. As shown on the image. After assigning the variables click on edit script.

User::VarFolderPath
User::VarFileName

8. The .cs file for the script will now open and make sure to declare this namespaces.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

9. After declaring the namespaces go to script main() and copy and paste the code below. On this guide we are going to move the excel file titled a koan to a new location. You can modify and experiment with the script to whatever file name and format you wanted to move.

       var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());
            FileInfo[] files = directory.GetFiles("A_KOAN*.xls");
            DateTime lastModified = DateTime.MinValue;
            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime > lastModified)
                {
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::VarFileName"].Value = file.ToString();
                }
            }
            String FullPath;

            FullPath = Dts.Variables["User::VarFolderPath"].Value.ToString() + Dts.Variables["User::VarFileName"].Value.ToString();

 

10. After doing the above step save and close the .cs file then drag and drop a File System Task on the control flow.

 

11.Connect the script task to the file system task by dragging the arrow from the script task to file system task.

 

12. Right click the file system task then assign all the required variables for the destination and the source variable. Then click Ok.

 

13. Build the solution to make sure there are no errors. Then hit start to run the SSIS package.

 

The package is now ready to be run by a database trigger a scheduled task or an SQL Server agent jobs.

 

Now we have demonstrated the basics of moving files using SSIS the project is available on this github link. To download “A_KOAN” excel file please go to this link http://datajackal.com/2018/05/03/useful-microsoft-formulas-for-the-discerning-analyst/

If you have any suggestions or comments please comment on the comment section below.

Thank you -Data Jackal