From time to time you may come across a customer who needs to get data from a 3rd party or external application into their internal database. One of the easiest integration solutions is to FTP (File Transfer Protocol) the file to a folder that can be picked up for further processing or an ETL (Extract Transform Load) process.
I won’t show the code that picks up the file automatically, but I will show how you can use SSIS (SQL Server Integration Services) to get the flat file into the existing infrastructure.
One of my use cases was that the file was always going to have a different name each time it was uploaded. For that I created a variable in SSIS at the Package Scope level.
Next we have modify the File Connection in order to have it reference the variable. We want to set the ConnectionString property to the variable filename we created above. SSIS has a interesting syntax when referencing variables where they are prefixed with @ symbol.
To get to the screen below, select the object and hit F4 to bring up the properties pane. Expand the Expressions property and click on the button next to it to bring up the dialog below.
Once you have created your package in Business Intelligence Studio, copy that .dtsx file to your bin folder of your C# library that will execute the package programmatically. This will give us a common location to execute the package.
In your C# project, add a reference to Microsoft.SQLServer.ManagedDTS. In my case I have two different versions since I have both SQL Server 2008 and SQL Server 2010 installed on my box. Select the appropriate one for your environment.
In the code sample below, it shows how easy it is to execute the package from code.
1: public string ExecutePackage(string filePath, string packageName)
3: DTSExecResult results;
5: var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, packageName);
6: var app = new Application();
7: var pkg = app.LoadPackage(path,null);
9: Variables variables = pkg.Variables;
10: variables["filename"].Value = filePath;
12: results = pkg.Execute(null, variables, null, null, null);
14: string result = "";
15: if(results == DTSExecResult.Success)
17: // log
18: result = "Success";
22: // log
23: result = "Failure";
26: return result;