/*******************************************************************
*
* 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
*
* Class 4- 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.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
/*
* Adding Sequence Container
* with SQLTask Work Flow Source and Destination
*/
namespace SQLSaturday130
{
public class Demo4 : IDisposable
{
private Package myPackage = new Package();
public void CreatePackage()
{
String SqlCmd = "Select count (*) from information_schema.tables";
String Description = "SQLSaturday Demo";
//Add Variable to package
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");
Sequence seqSequence = (Sequence)execSequence;
//add Properties
seqSequence.Name = "SEQUENCE Demo";
seqSequence.LoggingMode = DTSLoggingMode.UseParentSetting;
seqSequence.Description = Description;
//Set precedence Constraint
PrecedenceConstraint SqlTask2Sequece = myPackage.PrecedenceConstraints.Add(execSqlTask, execSequence);
SqlTask2Sequece.Value = DTSExecResult.Success;
//Add Work Flow
Executable execDataFlow = seqSequence.Executables.Add("STOCK:PipelineTask");
//Wrapper
TaskHost thDataFlow = (TaskHost)execDataFlow;
thDataFlow.Name = "DataFlow Demo";
//reference thHost pipe
MainPipe dfDataFlow = (MainPipe)thDataFlow.InnerObject;
String SaveLocation = @"c:\\temp";
SavePackage(SaveLocation);
}
public 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 void AddSQLTask(Executable exec, String TaskName,String Connection, String SqlCmd,String VariableName)
{
TaskHost sqlTaskHost = (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);
Application app = new Application();
app.SaveToXml(FullFilePath, myPackage, null);
}
else
{
Application app = new Application();
app.SaveToXml(FullFilePath, myPackage, null);
}
}
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