/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using System.Windows.Forms;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Pipeline = Microsoft.SqlServer.Dts.Pipeline;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using PipelineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;
using System.IO;
using System.Xml;
namespace ST_0b887b30a0984bc088553f39a5f7d1c7.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
Runtime.Package myPackage = new Runtime.Package();
char SOH = (char)1; // SOH
char RS = (char)30;// RS
String Delimited = "";
public void Main()
{
Runtime.Variables vars = null;
Delimited = SOH.ToString();
//READ
Dts.VariableDispenser.LockForRead("User::strTableName");
Dts.VariableDispenser.LockForRead("User::strFileName");
Dts.VariableDispenser.LockForRead("User::strDirectoryPath");
Dts.VariableDispenser.LockForRead("User::bolOverrideFile");
Dts.VariableDispenser.LockForRead("User::strSqlCmd");
Dts.VariableDispenser.LockForRead("User::strSourceFileName");
Dts.VariableDispenser.LockForRead("User::strFullFilePath");
Dts.VariableDispenser.LockForRead("User::bolSavePackage");
Dts.VariableDispenser.LockForRead("User::strSavePackageLocation");
Dts.VariableDispenser.LockForRead("User::intColumnDelimitedAscii");
Dts.VariableDispenser.LockForRead("User::intRowDelimiterAscii");
Dts.VariableDispenser.LockForRead("User::bolIncludeColumnHeaders");
//WRITE
Dts.VariableDispenser.LockForWrite("User::intExtractCount");
// Reference Variables
Dts.VariableDispenser.GetVariables(ref vars);
String TableName = vars["User::strSourceFileName"].Value.ToString();
String FileName = vars["User::strFileName"].Value.ToString();
String DirectoryPath = vars["User::strDirectoryPath"].Value.ToString();
String FullFilePath = vars["User::strFullFilePath"].Value.ToString();
String SourceSqlCmd = vars["User::strSqlCmd"].Value.ToString();
String SavePackageDirectory = vars["User::strSavePackageLocation"].Value.ToString();
SOH = (char)(int)vars["User::intColumnDelimitedAscii"].Value;
RS = (char)(int)vars["User::intRowDelimiterAscii"].Value;
Boolean FileOverride = (Boolean)vars["User::bolOverrideFile"].Value;
Boolean SaveSsisPackage = (Boolean)vars["User::bolSavePackage"].Value;
Boolean IncludeHeaderFile = (Boolean)vars["User::bolIncludeColumnHeaders"].Value;
myPackage.Name = vars["User::strTableName"].Value.ToString() ;
myPackage.Description = "Table2FileTransfer";
try
{
// Add RowCount Variable
// Local variable for File Path
Runtime.Variable intRawDataCount = myPackage.Variables.Add("intRawCount", false, "User", 0);
//Create OLEDB Connection
Runtime.ConnectionManager HOST = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
Runtime.ConnectionManager MSDB = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
Runtime.ConnectionManager Source = AddOLEDBConnection(TableName, Dts.Connections["source"].ConnectionString);
//Add FlatFileDestination
Runtime.ConnectionManager FlatFile = AddFlatFileConnection(FileName, FullFilePath, Delimited,IncludeHeaderFile);
// Add Logging
AddLogging("msdb", true);
//Add Sequence Container
Runtime.Executable execSequence = AddSQLExecutable("STOCK:Sequence");
Runtime.Sequence rtSequence = (Runtime.Sequence)execSequence;
//Sequence Properties
rtSequence.Name = "DataHub_ODS_EXTRACT";
rtSequence.LoggingMode = Runtime.DTSLoggingMode.UseParentSetting;
rtSequence.Description = "DATAHUB_ODS Export Prototype";
// Add WorkFlow Component
Runtime.Executable execDataFlow = rtSequence.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thDataFlow = (Runtime.TaskHost)execDataFlow;
thDataFlow.Name = "Fill2RawLoading";
PipelineWrapper.MainPipe DataFlow = (PipelineWrapper.MainPipe)thDataFlow.InnerObject;
//Adding Source Component.
PipelineWrapper.IDTSComponentMetaData100 icmdSource = AddComponentMetadata(DataFlow, "SourceComponet");
icmdSource.ComponentClassID = "DTSAdapter.OleDbSource.2";//sql2012
icmdSource.Name = "SourceTbl";
PipelineWrapper.CManagedComponentWrapper mcwSource = icmdSource.Instantiate();
mcwSource.ProvideComponentProperties();
icmdSource.RuntimeConnectionCollection[0].ConnectionManagerID = Source.ID;
icmdSource.RuntimeConnectionCollection[0].ConnectionManager = Runtime.DtsConvert.GetExtendedInterface(Source);
mcwSource.SetComponentProperty("AccessMode", 2);//SQLCmd
mcwSource.SetComponentProperty("SqlCommand", SourceSqlCmd);
//mcwSource.SetComponentProperty("AccessMode", 0); //TableOrView
//mcwSource.SetComponentProperty("OpenRowset", RawTableName);
RefreshMetadata(mcwSource);
//Add RowCount Component
PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(DataFlow, "RowCountComponent");
icmdRowCount.ComponentClassID = "DTSTransform.RowCount.2";
icmdRowCount.Name = "RawFileRowCount";
PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
mcwRowCount.ProvideComponentProperties();
mcwRowCount.SetComponentProperty("VariableName", "User::intRawCount");
// Join Source and RowCount
PipelineWrapper.IDTSPath100 tblSource2Count;
tblSource2Count = DataFlow.PathCollection.New();
tblSource2Count.Description = "Fill Table 2 Raw Table";
tblSource2Count.AttachPathAndPropagateNotifications(icmdSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
RefreshMetadata(mcwRowCount);
// Add FlatFile Destination Component
PipelineWrapper.IDTSComponentMetaData100 icmdFileDestination = AddComponentMetadata(DataFlow, "FlatFileDestination");
icmdFileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination.2";
icmdFileDestination.Name = "FlatFileDestination";
//Get Instance
PipelineWrapper.CManagedComponentWrapper mcwFlatFileDestination = icmdFileDestination.Instantiate();
mcwFlatFileDestination.ProvideComponentProperties();
mcwFlatFileDestination.SetComponentProperty("Overwrite", FileOverride);
//Set Destination connection
icmdFileDestination.RuntimeConnectionCollection[0].ConnectionManagerID = FlatFile.ID;
icmdFileDestination.RuntimeConnectionCollection[0].ConnectionManager = Runtime.DtsConvert.GetExtendedInterface(FlatFile);
// Add Path between rowCount and Flatfile
IDTSPath100 path = DataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0],
icmdFileDestination.InputCollection[0]);
//Set Input and Virtuals for FlatFile Inputs
SetColumnsNamesForFlatFile(ref icmdFileDestination, ref FlatFile, Delimited);
RefreshMetadata(mcwFlatFileDestination);
MappColumns(icmdFileDestination, mcwFlatFileDestination);
// Exectue Package in Memory
myPackage.Execute();
if ((myPackage.ExecutionResult == Runtime.DTSExecResult.Failure) || (myPackage.ExecutionStatus == Runtime.DTSExecStatus.Abend))
{
if (SaveSsisPackage)
{
SavePackage(SavePackageDirectory);
}
throw new System.InvalidCastException("Generated Package Failure");
//Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
// Get internal Count
vars["User::intExtractCount"].Value = (int)intRawDataCount.Value;
// release variables
vars.Unlock();
vars = null;
if (SaveSsisPackage)
{
SavePackage(SavePackageDirectory);
}
myPackage.Dispose();
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch
{
myPackage.Dispose();
//SavePackage(vars["User::strDirectoryPath"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
/*************************Sub Programs*********************************************************/
public void SetColumnsNamesForFlatFile(ref PipelineWrapper.IDTSComponentMetaData100 FlatFile, ref Runtime.ConnectionManager ConnFlatFile, String Delimited)
{
IDTSInput100 FlatFileOutput = FlatFile.InputCollection[0];
IDTSVirtualInput100 vFlatFileInput = FlatFileOutput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 FlatFileVirtualInputColumns = vFlatFileInput.VirtualInputColumnCollection;
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = (RuntimeWrapper.IDTSConnectionManagerFlatFile100)ConnFlatFile.InnerObject;
// Create flat file connection columns to match pipeline
int indexMax = FlatFileVirtualInputColumns.Count - 1;
for (int index = 0; index <= indexMax; index++)
{
// Get input column to replicate in flat file
IDTSVirtualInputColumn100 virtualInputColumn = FlatFileVirtualInputColumns[index];
// Add column to Flat File connection manager
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileColumn =
connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
flatFileColumn.ColumnType = "Delimited";
flatFileColumn.ColumnWidth = virtualInputColumn.Length;
flatFileColumn.DataPrecision = virtualInputColumn.Precision;
flatFileColumn.DataScale = virtualInputColumn.Scale;
flatFileColumn.DataType = virtualInputColumn.DataType;
RuntimeWrapper.IDTSName100 columnName = flatFileColumn as RuntimeWrapper.IDTSName100;
columnName.Name = virtualInputColumn.Name;
if (index < indexMax)
flatFileColumn.ColumnDelimiter = Delimited;
else
flatFileColumn.ColumnDelimiter = RS.ToString();
}
}
public void AddLogging(String ConnectionName, Boolean Enable)
{
Runtime.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 Runtime.ConnectionManager AddFlatFileConnection(String ConnectionName, String FullFilePath, String Delimited,Boolean IncludeHeaders)
{
char RS = (char)30;
Runtime.ConnectionManager connectionManagerFlatFile = myPackage.Connections.Add("FLATFILE");
connectionManagerFlatFile.ConnectionString = FullFilePath;
connectionManagerFlatFile.Name = ConnectionName;
connectionManagerFlatFile.Properties["Format"].SetValue(connectionManagerFlatFile, "Delimited");
connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, IncludeHeaders);
// connectionManagerFlatFile.Properties["TextQualified"].SetValue(connectionManagerFlatFile, false);
connectionManagerFlatFile.Properties["RowDelimiter"].SetValue(connectionManagerFlatFile, RS.ToString());
return connectionManagerFlatFile;
}
/*
* Add OLEDB Connection
*/
public Runtime.ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
{
Runtime.ConnectionManager ConMgr = myPackage.Connections.Add("OLEDB");
ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";
ConMgr.Name = ConnectionName;
ConMgr.Description = "SQL OLEDB using " + ConnectionName;
return ConMgr;
}
/*
/*
* Add WorkFlow
*
*/
private PipelineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
{
myPackage.Executables.Add("STOCK:PipelineTask");
Runtime.TaskHost _TaskHost = (Runtime.TaskHost)myPackage.Executables[0];
PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
_TaskHost.Name = ComponentName;
_TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");
return dataFlowTask;
}
private IDTSComponentMetaData100 AddComponentMetadata(MainPipe wf, String ComponentName)
{
IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
return icmd;
}
// Create SSIS executable
private Runtime.Executable AddSQLExecutable(String MONIKER)
{
Runtime.Executable exec = myPackage.Executables.Add(MONIKER);
return exec;
}
private void SavePackage(String DirectoryPath)
{
String FullFilePath = DirectoryPath + "\\" + myPackage.Name.ToString() + ".dtsx";
if (File.Exists(FullFilePath))
{
File.Delete(FullFilePath);
Runtime.Application app = new Runtime.Application();
app.SaveToXml(FullFilePath, myPackage, null);
}
else
{
Runtime.Application app = new 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 void MappColumns(IDTSComponentMetaData100 icmDest, CManagedComponentWrapper mcwDest)
{
//mappings
IDTSInput100 inpDestination = icmDest.InputCollection[0];
IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vinpcDestination = (IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;
foreach (IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
{
try
{
IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_READONLY);
// map external column
IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];
mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
}
catch
{
IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_IGNORED);
// map external column
}
}
}
protected void Dispose(bool disposing)
{
if (disposing)
{
// dispose managed resources
myPackage.Dispose();
}
// free native resources
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
}
