/*******************************************************************
*
* Jorge Novo
* SQLSaturday SSIS Demostration
* jorge.novo@gmail.com
* http://etldevelopernotes.blogspot.com/
* 4/27/2012
*
* Package to demostrate an empty ssis package
* Add Connection Strings and loggings
* Add SQL Task and Sequence Container
* Add Data Flow
* Add Source
* Add Destination
*
* Class 5- 7
*
*
*
* *****************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
/*
* Adding Sequence Container
* with SQLTask Work Flow Source and Destination
*/
namespace SQLSaturday130
{
public class Demo5 : IDisposable
{
private Microsoft.SqlServer.Dts.Runtime.Package myPackage = new Microsoft.SqlServer.Dts.Runtime.Package();
public void CreatePackage()
{
String SqlCmd = "Select count (*) from information_schema.tables";
String Description = "SQLSaturday Demo";
String SourceSchTable = "tblDemo1";
String DestinationSchTable = "tblDemo2";
//Add Variable to package
Microsoft.SqlServer.Dts.Runtime.Variable mvar = myPackage.Variables.Add("iDestCnt", false, "User", 0);
//Add and get executable reference to a SQLTAsk Component
Executable execSqlTask = AddSQLExecutable("STOCK:SQLTASk");
//Wrap the executable in a TaskHost
AddSQLTask(execSqlTask, "SQLTASK DEMO", "destination", SqlCmd, mvar.Name);
//Add a Sequence Container
Executable execSequence = AddSQLExecutable("STOCK:Sequence");
Microsoft.SqlServer.Dts.Runtime.Sequence seqSequence = (Microsoft.SqlServer.Dts.Runtime.Sequence)execSequence;
//add Properties
seqSequence.Name = "SEQUENCE Demo";
seqSequence.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.UseParentSetting;
seqSequence.Description = Description;
//Set precedence Constraint
Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint SqlTask2Sequece = myPackage.PrecedenceConstraints.Add(execSqlTask, execSequence);
SqlTask2Sequece.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
//Add Empty Work Flow
//Add Work Flow
Executable execDataFlow = seqSequence.Executables.Add("STOCK:PipelineTask");
//Wrapper
Microsoft.SqlServer.Dts.Runtime.TaskHost thDataFlow = (Microsoft.SqlServer.Dts.Runtime.TaskHost)execDataFlow;
thDataFlow.Name = "DataFlow Demo";
//reference thHost pipe
MainPipe dfDataFlow = (MainPipe)thDataFlow.InnerObject;
//Add Source Component to package
IDTSComponentMetaData100 icmdSource = AddComponentMetadata(dfDataFlow, "SourceComponet");
//Add Destination
IDTSComponentMetaData100 icmdDestination = AddComponentMetadata(dfDataFlow, "DestinationComponent");
icmdSource.ComponentClassID = "DTSAdapter.OleDbSource.2";
icmdDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
//Source Configuration
ConnectionManager source = myPackage.Connections["source"];
ConnectionManager destination = myPackage.Connections["destination"];
CManagedComponentWrapper mcwSource = icmdSource.Instantiate();
mcwSource.ProvideComponentProperties();
icmdSource.RuntimeConnectionCollection[0].ConnectionManagerID = source.ID;
icmdSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(source);
//mcwSource.SetComponentProperty("AccessMode",2)//SQLCmd
//mcwSource.SetComponentProperty("SqlCommand",SqlCmd);
mcwSource.SetComponentProperty("AccessMode", 0); //TableOrView
mcwSource.SetComponentProperty("OpenRowset", SourceSchTable);
RefreshMetadata(mcwSource);
//Destination Configuration
icmdDestination.ValidateExternalMetadata = true;
CManagedComponentWrapper mcwDestination = icmdDestination.Instantiate();
mcwDestination.ProvideComponentProperties();
mcwDestination.SetComponentProperty("FastLoadKeepIdentity", true);
icmdDestination.Name = "tblDemo2";
icmdDestination.Description = "tblDemo2";
icmdDestination.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
icmdDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
mcwDestination.SetComponentProperty("AccessMode", 0);//FastLoad
mcwDestination.SetComponentProperty("OpenRowset", DestinationSchTable);
mcwDestination.Validate();
RefreshMetadata(mcwDestination);
String SaveLocation = @"c:\\temp";
SavePackage(SaveLocation);
}
public Microsoft.SqlServer.Dts.Runtime.Package GetSetPackage
{
get { return myPackage; }
set { myPackage = value; }
}
public String PackageName
{
set { myPackage.Name = value; }
}
public void AddLogging(String ConnectionName, Boolean Enable)
{
LogProvider pkLogging;
pkLogging = myPackage.LogProviders.Add("DTS.LogProviderSQLServer.2");
pkLogging.ConfigString = ConnectionName;
pkLogging.Description = "SQL Server Logging ";
myPackage.LoggingOptions.SelectedLogProviders.Add(pkLogging);
myPackage.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
myPackage.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
switch (Enable)
{
case true:
myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
break;
case false:
myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
break;
default:
myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
break;
}
}
public void AddOLEDBConnection(String ConnectionName, String ConnectionStr)
{
ConnectionManager ConMgr = myPackage.Connections.Add("OLEDB");
ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";
ConMgr.Name = ConnectionName;
ConMgr.Description = "SQL OLEDB using " + ConnectionName;
}
private IDTSComponentMetaData100 AddComponentMetadata(MainPipe wf,String ComponentName)
{
IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
return icmd;
}
private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd, String VariableName)
{
Microsoft.SqlServer.Dts.Runtime.TaskHost sqlTaskHost = (Microsoft.SqlServer.Dts.Runtime.TaskHost)exec;
//Add Properties
sqlTaskHost.Properties["Name"].SetValue(sqlTaskHost, TaskName);
sqlTaskHost.Properties["Description"].SetValue(sqlTaskHost, TaskName);
sqlTaskHost.Properties["Connection"].SetValue(sqlTaskHost, Connection);
sqlTaskHost.Properties["SqlStatementSource"].SetValue(sqlTaskHost, SqlCmd);
IDTSExecuteSQL iexecSqlTask = (IDTSExecuteSQL)sqlTaskHost.InnerObject;
iexecSqlTask.ResultSetType = ResultSetType.ResultSetType_SingleRow;
iexecSqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
IDTSResultBinding isqlTaskResult = iexecSqlTask.ResultSetBindings.Add();
isqlTaskResult.DtsVariableName = VariableName;
}
private void SavePackage(String DirectoryPath)
{
String FullFilePath = DirectoryPath + "\\" + myPackage.Name.ToString() + ".dtsx";
if (File.Exists(FullFilePath))
{
File.Delete(FullFilePath);
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
app.SaveToXml(FullFilePath, myPackage, null);
}
else
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
app.SaveToXml(FullFilePath, myPackage, null);
}
}
private void RefreshMetadata(CManagedComponentWrapper mcw)
{
//Reinitialize the metadata, Refresh Columns
mcw.AcquireConnections(null);
mcw.ReinitializeMetaData();
mcw.ReleaseConnections();
}
private Executable AddSQLExecutable(String MONIKER)
{
Executable exec = myPackage.Executables.Add(MONIKER);
return exec;
}
protected void Dispose(bool disposing)
{
if (disposing)
{
// dispose managed resources
myPackage.Dispose();
}
// free native resources
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
}
No comments:
Post a Comment