Executing A SSIS Package From C#
This is something interesting that i had to do recently and i though i should share it.
The idea behind this is that SSIS is a freaking powerful tool and since its so powerful why go write another app to do exactly what it can already do.BTW if you have not used it (which is kinda hard if you are a windows developer) then i suggest you go look it up and get a taste of what you have been missing.
So the main reason i use it and i am sure the same reason a lot of other people use it is to run data imports into a sql server. Now a common problem that you will run into is if you have to import data from a Excel Worksheet. Since the location of the work sheet can change (especially if some one else needs to run the import) you need to either change the location manually , force the user to only use one location or add some VBScripting to prompt for a path.
The problem is that VB scripting is one of the most annoying languages i have ever tried to write a program in and i figured that you must be able to do this in C# somehow.
So first things first , you need to add a reference to Microsoft.SQLServer.ManagedDTS . Note that you will most likeley not find this in the default .Net Import list and you will have to manually find the dll so that it can be imported.
Once the reference has been added the code below will execute the package of your choice.
Application pacApp = new Application();
Package packLoader = pacApp.LoadPackage(System.Windows.Forms.Application.StartupPath+@”\WorkflowImport.dtsx”,
null);
packLoader.Connections["SourceConnectionExcel"].ConnectionString
= @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”+txtFileName.Text+”;Extended Properties=EXCEL 8.0;HDR=YES;”;
DTSExecResult pacResult = packLoader.Execute();
MessageBox.Show(pacResult.ToString());
What the code above does is pretty simple but it makes all the difference to the user.
- First it creates a instance of the SSIS application.
- It then creates a new package and initializes it using the app instance created in the previous step.It should be noted that because the package is deployed with the app it resides in the exe’s directory and you have to manually find the directory and load it from there.
- Once the package is loaded we change the path of the Excel Connection to a new one that has been entered in a text box on the form.
- Finally the package is executed and the result stored and then displayed using a message box.
And thats all there is to it , this allows you to make a pakage a lot simpler to use and provides you with a host of other options not discussed here.
As always let me know what you think
~stalkerh
