Run SSIS package programmatically from csharp c# code

Introduction:

We will first create a Sql Server Integration Service that will establish a sql server connection against a table and dump it in a flat file (we will not use transformation for this sample) . After we will create a console application to run this SSIS package.

Create a SSIS Package in BIDS:

Open Business Intelligence Development Studio and creat a SSIS project:

Select the Data Flow panel or double click on Data Flow Task: it is in this panel where we will define the source, the transformation of data , and the destination. Drag and drop an OLE DB Source from Data Flow Sources panel as below:

Right click and select properties to configure OLE DB Source:

select a new  connection manager on click:

and select the table you want to dump:

Click OK.

Drag and drop  in Data Flow Destination a Flat File Destination:

Bind the OLE DB Source to Flat File Destination using the green arrow:

Right click on Flat File Destination to configure the path of the file:

Finally, click on Mappings to make your mapping:

Now, your SSIS package is ready so test it in running it:

all is fine, so we will now add to our solution a console application to run programmatically our package

in Solution Explorer pane, add a new project:

and choose an application console:

Reference Microsoft.SQLServer.DTSRuntimeWrap:

this reference is contained in this assembly:

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll

Below code snippet to run your ssis package

set the application console as startup project and run it and you will see that the SSIS package runs successfully:

latency using SMO to script Table – Sql Server Management Object

With SMO, it is easier to programmatically make same tasks than Sql Server 2008 Management Studio wizard which allows script all database objects (tables, storde procedure, index,data…):

msdn url below will explain you how SMO works

http://msdn.microsoft.com/en-us/library/ms162169(v=sql.100).aspx

One trooble I have encountered in scripting with smo many tables (many hundred)  was that scripting with smo have taken 2 hours against only few minutes with SSMS wizard. One workaround was to firstly use PrefectObject and secondly make   call script method with multithreading. It allows me to decrease time processing from 2 hours to 20 minutes.

use PrefetchObject to make one call to load table collection:

Database db = … // get your database root

ScriptingOptions so = new ScriptingOptions();

db.PrefetchObject(typeof(Table), so);