Archives de la catégorie Sql Server 2008 – Business Intelligence (BI) – Sql server Management ( SMO) – Ado.net
Run SSIS package programmatically from csharp c# code
Publié par hassanboutougha dans Sql Server 2008 - Business Intelligence (BI) - Sql server Management ( SMO) - Ado.net, SSIS - Sql Server Integration Service le octobre 13, 2012
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
Publié par hassanboutougha dans SMO - Sql Server Management allows to script Database objects le octobre 6, 2012
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);