#region Help: Introduction to the script task
/* Jorge Novo
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using TxBDD;
using SQLTask = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
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;
#endregion
namespace ST_a521b7186dae425b959c1d4e8c6625d2
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
///
// Global Variables
Package ssis = new Package();
Runtime.Application app = new Runtime.Application();
Variables vars = null;
public void Main()
{
try
{
//External Parent Variables
Dts.VariableDispenser.LockForRead("User::strArchiveDirectory");
Dts.VariableDispenser.LockForRead("User::strImportDirectory");
Dts.VariableDispenser.LockForRead("User::strLoadDirectory");
Dts.VariableDispenser.LockForRead("User::strSsisName");
Dts.VariableDispenser.LockForRead("User::strBatchId");
Dts.VariableDispenser.LockForRead("User::strDestConnStr");
Dts.VariableDispenser.LockForRead("User::strDestSchema");
Dts.VariableDispenser.LockForRead("User::strDestTableName");
Dts.VariableDispenser.LockForRead("User::strDestServerName");
Dts.VariableDispenser.LockForRead("User::strFileCodePage");
Dts.VariableDispenser.LockForRead("User::strFileName");
Dts.VariableDispenser.LockForRead("User::strFilePrefix");
Dts.VariableDispenser.LockForRead("User::strFileSubfix");
Dts.VariableDispenser.LockForRead("User::strSymbol");
Dts.VariableDispenser.LockForRead("User::strFileExtension");
Dts.VariableDispenser.LockForWrite("User::bolIsLoaded");
Dts.VariableDispenser.LockForRead("User::intLFNFileId");
Dts.VariableDispenser.LockForWrite("User::intRecordCount");
Dts.VariableDispenser.LockForWrite("User::strErrorMessage");
Dts.VariableDispenser.LockForRead("User::strStoreXmlCmd");
Dts.VariableDispenser.LockForRead("User::intETLLogId");
Dts.VariableDispenser.GetVariables(ref vars);
//Local Variables
String Archive = vars["User::strArchiveDirectory"].Value.ToString();
String Load = vars["User::strLoadDirectory"].Value.ToString();
String Import = vars["User::strImportDirectory"].Value.ToString();
String SsisName = vars["User::strSsisName"].Value.ToString();
String BatchId = vars["User::strBatchId"].Value.ToString();
String DestConnectionStr = vars["User::strDestConnStr"].Value.ToString();
String TableSchema = vars["User::strDestSchema"].Value.ToString();
String TableName = vars["User::strDestTableName"].Value.ToString();
String DestServer = vars["User::strDestServerName"].Value.ToString();
String CodePage = vars["User::strFileCodePage"].Value.ToString();
String FileName = vars["User::strFileName"].Value.ToString();
String Prefix = vars["User::strFilePrefix"].Value.ToString().Trim();
String SubFix = vars["User::strFileSubfix"].Value.ToString().Trim();
String FileDelimite = vars["User::strSymbol"].Value.ToString().Trim();
String FileExtension = "." + vars["User::strFileExtension"].Value.ToString().Trim();
String TruncateCmd = "Truncate Table " + TableSchema + "." + TableName;
String FillTableName = TableSchema + "." + TableName;
int LFNFileId =(int) vars["User::intLFNFileId"].Value;
//Compose Variables
StringBuilder FilePattern = new StringBuilder();
FilePattern.Append(Prefix);
FilePattern.Append(FileName);
FilePattern.Append(SubFix);
FilePattern.Append(FileExtension);
String xFullFilePath = GetFileName(Load, FilePattern.ToString());
//Assign relevant package name and description
ssis.Name = SsisName;
ssis.Description = "Load Process of initial File";
//Package Variables
Runtime.Variable intRowCount = ssis.Variables.Add("intRowCount", false, "User", 0);
Runtime.Variable strBatchId = ssis.Variables.Add("strBatchId", false, "User", BatchId);
Runtime.Variable intLFNFileId = ssis.Variables.Add("intLfnFileId", false, "User", LFNFileId);
//Add database Connections
ConnectionManager destination = AddOLEDBConnection("destination", DestConnectionStr);
ConnectionManager msdb = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
ConnectionManager host = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
AddLogging(msdb.Name, true);
//Add Sequence Container
Executable execSequence = AddSQLExecutable("STOCK:Sequence");
Runtime.Sequence rtSequence = (Runtime.Sequence)execSequence;
//Sequence Properties
rtSequence.Name = "File2FillTableImport";
rtSequence.LoggingMode = Runtime.DTSLoggingMode.UseParentSetting;
rtSequence.Description = "EIM Import Prototype";
// Add SQLTask Component
Executable execFillRawSqlTask = rtSequence.Executables.Add("STOCK:SQLTASk");
//Wrap the executable in a TaskHost
AddSQLTask(execFillRawSqlTask, "Truncate Fill Table", destination.Name.ToString(), TruncateCmd);
//Add DataFlow
Executable execRawDataFlow = rtSequence.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thRawDataFlow = (Microsoft.SqlServer.Dts.Runtime.TaskHost)execRawDataFlow;
thRawDataFlow.Name = "RawFile2FillTable";
PipelineWrapper.MainPipe mDataFlow = (PipelineWrapper.MainPipe)thRawDataFlow.InnerObject;
// Add Precedence Constraint between SQLTask and workflow
Runtime.PrecedenceConstraint SqlTask2WorkFlow = rtSequence.PrecedenceConstraints.Add(execFillRawSqlTask, execRawDataFlow);
SqlTask2WorkFlow.Value = Runtime.DTSExecResult.Success;
//Insert Flat File Connection
ConnectionManager ConnMgrFlatFile = ssis.Connections.Add("FLATFILE");
//You can change this path depending on where you have stored the flat file
ConnMgrFlatFile.ConnectionString = xFullFilePath;// Dts.Connections["FlatFile"].ConnectionString;
//Assign name to the flat file connection
ConnMgrFlatFile.Name = FileName;
//Indicate that the flat file is delimited
ConnMgrFlatFile.Properties["Format"].SetValue(ConnMgrFlatFile, "Delimited");
//Indicate whether the source file has column headings or not - in this case, our sample data has column headings.
ConnMgrFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(ConnMgrFlatFile, Convert.ToBoolean(true));
ConnMgrFlatFile.Properties["CodePage"].SetValue(ConnMgrFlatFile, Convert.ToInt32(CodePage));
//Get Native Flat File Connection
RuntimeWrapper.IDTSConnectionManagerFlatFile100 rtwConnFlatFile = ConnMgrFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
AddFlatFileColumns(ref rtwConnFlatFile, xFullFilePath, FileDelimite);
//Add FlatFile Source Component
//Insert Flat File source component
PipelineWrapper.IDTSComponentMetaData100 componentSource = mDataFlow.ComponentMetaDataCollection.New();
componentSource.Name = "FlatFileSource";
componentSource.ComponentClassID = "{D23FD76B-F51D-420F-BBCB-19CBF6AC1AB4}";
//Insert source design-time instance and initialise component
PipelineWrapper.CManagedComponentWrapper instanceSource = componentSource.Instantiate();
instanceSource.ProvideComponentProperties();
//Set source connection
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = ConnMgrFlatFile.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConnMgrFlatFile);
//Reinitialize Flat File source metadata,
Reinitiaze(instanceSource);
//Add RowCount Component
PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(mDataFlow, "RowCountComponent");
icmdRowCount.ComponentClassID = "DTSTransform.RowCount";
PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
mcwRowCount.ProvideComponentProperties();
icmdRowCount.Name = "TotalRecordCount";
mcwRowCount.SetComponentProperty("VariableName", "User::intRowCount");
//Join Path
PipelineWrapper.IDTSPath100 SourceToCount;
SourceToCount = mDataFlow.PathCollection.New();
SourceToCount.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
Reinitiaze(mcwRowCount);
//Add Paralle Distributor
PipelineWrapper.IDTSComponentMetaData100 icmdParallel = AddComponentMetadata(mDataFlow, "ParallelDistribution");
icmdParallel.ComponentClassID = "{F06C606C-5980-460C-B2AF-B0ECA031B7A4}";
PipelineWrapper.CManagedComponentWrapper mcwParallel = icmdParallel.Instantiate();
mcwParallel.ProvideComponentProperties();
PipelineWrapper.IDTSPath100 CountToDistributor;
CountToDistributor = mDataFlow.PathCollection.New();
CountToDistributor.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], icmdParallel.InputCollection[0]);
Reinitiaze(mcwParallel);
//Add OLEDB Destination Componnent
PipelineWrapper.IDTSComponentMetaData100 icmRawDestination = AddComponentMetadata(mDataFlow, "destination");
icmRawDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
icmRawDestination.ValidateExternalMetadata = true;
PipelineWrapper.CManagedComponentWrapper mcwRawDestination = icmRawDestination.Instantiate();
// tblDestination
mcwRawDestination.ProvideComponentProperties();
icmRawDestination.Name = "destination";
icmRawDestination.Description = "destination";
icmRawDestination.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
icmRawDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
mcwRawDestination.SetComponentProperty("AccessMode", 3);//FastLoad
mcwRawDestination.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
mcwRawDestination.Validate();
mcwRawDestination.SetComponentProperty("FastLoadKeepIdentity", true);
mcwRawDestination.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
mcwRawDestination.SetComponentProperty("FastLoadKeepNulls", false);
mcwRawDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
Reinitiaze(mcwRawDestination);
///////////////////////////////////////////////////////
//Add OLEDB Destination Componnent
PipelineWrapper.IDTSComponentMetaData100 icmRawDestination2 = AddComponentMetadata(mDataFlow, "destination");
icmRawDestination2.ComponentClassID = "DTSAdapter.OLEDBDestination";
icmRawDestination2.ValidateExternalMetadata = true;
PipelineWrapper.CManagedComponentWrapper mcwRawDestination2 = icmRawDestination2.Instantiate();
// tblDestination
mcwRawDestination2.ProvideComponentProperties();
icmRawDestination2.Name = "destination2";
icmRawDestination2.Description = "destination2";
icmRawDestination2.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
icmRawDestination2.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
mcwRawDestination2.SetComponentProperty("AccessMode", 3);//FastLoad
mcwRawDestination2.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
mcwRawDestination2.Validate();
mcwRawDestination2.SetComponentProperty("FastLoadKeepIdentity", true);
mcwRawDestination2.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
mcwRawDestination2.SetComponentProperty("FastLoadKeepNulls", false);
mcwRawDestination2.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
Reinitiaze(mcwRawDestination2);
//////////////////////////////////////////////////////
///////////////////////////////////////////////////////
//Add OLEDB Destination Componnent
PipelineWrapper.IDTSComponentMetaData100 icmRawDestination3 = AddComponentMetadata(mDataFlow, "destination");
icmRawDestination3.ComponentClassID = "DTSAdapter.OLEDBDestination";
icmRawDestination3.ValidateExternalMetadata = true;
PipelineWrapper.CManagedComponentWrapper mcwRawDestination3 = icmRawDestination3.Instantiate();
// tblDestination
mcwRawDestination3.ProvideComponentProperties();
icmRawDestination3.Name = "destination3";
icmRawDestination3.Description = "destination3";
icmRawDestination3.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
icmRawDestination3.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
mcwRawDestination3.SetComponentProperty("AccessMode", 3);//FastLoad
mcwRawDestination3.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
mcwRawDestination3.Validate();
mcwRawDestination3.SetComponentProperty("FastLoadKeepIdentity", true);
mcwRawDestination3.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
mcwRawDestination3.SetComponentProperty("FastLoadKeepNulls", false);
mcwRawDestination3.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
Reinitiaze(mcwRawDestination3);
//////////////////////////////////////////////////////
//join Source
//TableLoading joins
PipelineWrapper.IDTSPath100 CountToRawTable;
CountToRawTable = mDataFlow.PathCollection.New();
CountToRawTable.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[0], icmRawDestination.InputCollection[0]);
PipelineWrapper.IDTSPath100 Distributor2Table;
Distributor2Table = mDataFlow.PathCollection.New();
Distributor2Table.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[1], icmRawDestination2.InputCollection[0]);
PipelineWrapper.IDTSPath100 Distributor3Table;
Distributor3Table = mDataFlow.PathCollection.New();
Distributor3Table.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[2], icmRawDestination3.InputCollection[0]);
MappColumns(icmRawDestination, mcwRawDestination);
MappColumns(icmRawDestination2, mcwRawDestination2);
MappColumns(icmRawDestination3, mcwRawDestination3);
// Execute package
SaveSSIS(@"\\c:\temp", SsisName);
StoreSsisPackage(host);
ssis.Execute();
if ((ssis.ExecutionResult == DTSExecResult.Failure) || (ssis.ExecutionStatus == DTSExecStatus.Abend))
{
StringBuilder ErrorMessage = new StringBuilder();
// Package Failure on Error
vars["User::bolIsLoaded"].Value = false;
//Capture Error message
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError Error in ssis.Errors)
{
String ErrorDesc = Error.Source + ":" + Error.Description;
ErrorMessage.Append(ErrorDesc);
ErrorDesc = null;
}
String eMessage = ErrorMessage.ToString();
vars["User::ErrorMessage"].Value = eMessage;
ErrorMessage = null;
throw new System.ArgumentException("Loading RawData File Error ", eMessage);
}
else
{
vars["User::intRecordCount"].Value = intRowCount.Value;
//vars["User::bolIsLoaded"].Value = true;
// SaveSSIS(SaveDirectory, myPackage.Name.ToString());
vars.Unlock();
FilePattern = null;
ssis.Dispose();
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (ArgumentException e)
{
// MessageBox.Show(e.Message.ToString());
// StoreSsisPackage(host);
ssis.Name = "Error" + ssis.Name.ToString();
SaveSSIS(@"\\c\temp\ssis", ssis.Name.ToString());
ssis.Dispose();
throw e;
}
}
// save XML package into errorlog table
private void StoreSsisPackage(ConnectionManager host)
{
int LogId = (int)vars["User::intETLLogId"].Value;
StringBuilder strbSQLCmd = new StringBuilder();
XmlDocument xlmdoc = new XmlDocument();
String ServerName = (String)host.Properties["ServerName"].GetValue(host);
String DatabaseName = (string)host.Properties["InitialCatalog"].GetValue(host);
String StoreProc = "Log.SsisStorage_InsUpd";
ssis.SaveToXML(ref xlmdoc, null, null);
strbSQLCmd.Append("Server=");
strbSQLCmd.Append(ServerName);
strbSQLCmd.Append(";Database=");
strbSQLCmd.Append(DatabaseName);
strbSQLCmd.Append(";Trusted_Connection=TRUE");
SqlConnection sqlCon = new SqlConnection(strbSQLCmd.ToString());
using (DbCommand Cmd = sqlCon.CreateCommand())
{
Cmd.CommandType = System.Data.CommandType.StoredProcedure;
Cmd.CommandText = StoreProc;
Cmd.Parameters.Add(new SqlParameter("@ETLLogId", System.Data.SqlDbType.Int)
{
Value = LogId
});
Cmd.Parameters.Add(new SqlParameter("@PackageName", System.Data.SqlDbType.VarChar)
{
Value = ssis.Name.ToString()
});
Cmd.Parameters.Add(new SqlParameter("@pkg", System.Data.SqlDbType.Xml)
{
Value = new System.Data.SqlTypes.SqlXml(new XmlTextReader(xlmdoc.InnerXml,XmlNodeType.Document,null))
});
sqlCon.Open();
DbTransaction tran = sqlCon.BeginTransaction();
Cmd.Transaction = tran;
try
{
String strCmd = Cmd.CommandText.ToString();
Cmd.ExecuteNonQuery();
tran.Commit();
sqlCon.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
tran.Rollback();
sqlCon.Close();
throw;
}
}
}
/*
* Add Destinatin Componnet
*/
private PipelineWrapper.IDTSComponentMetaData100 AddComponentMetadata(PipelineWrapper.MainPipe wf, String ComponentName)
{
PipelineWrapper.IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
return icmd;
}
private void AddFlatFileColumns(ref RuntimeWrapper.IDTSConnectionManagerFlatFile100 ConnMgrFlatFile, String FullFilePath, String FileDelimited)
{
String line;
//Determine the number of columns by reading the sample Flat File - line by line.
using (StreamReader file = new StreamReader(FullFilePath))
{
try
{
while ((line = file.ReadLine()) != null)
{
char[] delimiters = FileDelimited.ToCharArray();
string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < parts.Length; i++)
{
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol = ConnMgrFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
sS_AssignColumnProperties(flatFileCol, parts[i], FileDelimited);
}
//Exit file after reading the first line
break;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
file.Close();
}
}
//Edit the last Flat File column delimiter into NewLine instead of a Comma
ConnMgrFlatFile.Columns[ConnMgrFlatFile.Columns.Count - 1].ColumnDelimiter = Environment.NewLine;
}
private static void sS_AssignColumnProperties(RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol, string getColName, string getDelim)
{
//Assign delimiter
flatFileCol.ColumnType = "Delimited";
flatFileCol.ColumnDelimiter = getDelim;
//Indicate column data type - in this case, all the source columns will be set to String Data Type
flatFileCol.DataType = RuntimeWrapper.DataType.DT_STR;
//Indicate column width - in this case, width of all source columns will be set to a length of 800
flatFileCol.ColumnWidth = 8000;
flatFileCol.MaximumWidth = 8000;
//Assign column name
RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;
columnName.Name = getColName.ToString();
}
//Save Ssis package to a location
private void SaveSSIS(String Directory, String PackageName)
{
String FullFilePath = Directory + "\\" + PackageName + ".dtsx";
app.SaveToXml(FullFilePath, ssis, null);
}
private void AddLogging(String ConnectionName, Boolean Enable)
{
LogProvider pkLogging;
pkLogging = ssis.LogProviders.Add("DTS.LogProviderSQLServer");
pkLogging.Name = "Log Provider For SQL SERVER";
pkLogging.Description = "Log Provider For SQL SERVER";
pkLogging.ConfigString = ConnectionName;
ssis.LoggingOptions.SelectedLogProviders.Add(pkLogging);
ssis.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
ssis.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
switch (Enable)
{
case true:
ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
break;
case false:
ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
break;
default:
ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
break;
}
}
/*
* Reinitiazied
*/
private void Reinitiaze(PipelineWrapper.CManagedComponentWrapper InstanceSource)
{
//Reinitialize Flat File source metadata,
InstanceSource.AcquireConnections(null);
InstanceSource.ReinitializeMetaData();
InstanceSource.ReleaseConnections();
}
/*
* Add OLEDB Connection
*/
public ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
{
ConnectionManager ConMgr = ssis.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)
{
ssis.Executables.Add("STOCK:PipelineTask");
TaskHost _TaskHost = (TaskHost)ssis.Executables[0];
PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
_TaskHost.Name = ComponentName;
_TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");
return dataFlowTask;
}
// Create SSIS executable
private Executable AddSQLExecutable(String MONIKER)
{
Executable exec = ssis.Executables.Add(MONIKER);
return exec;
}
/* need to work on this if more than one file then error or what to do*/
private String GetFileName(String DirectoryPath, String NamePattern)
{
String[] Files = System.IO.Directory.GetFiles(DirectoryPath, NamePattern);
String FullPath = "";
foreach (String file in Files)
{
FullPath = System.IO.Path.GetFullPath(file);
}
return FullPath;
}
private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd)
{
Runtime.TaskHost sqlTaskHost = (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);
SQLTask.IDTSExecuteSQL iexecFillRawSqlTask = (SQLTask.IDTSExecuteSQL)sqlTaskHost.InnerObject;
iexecFillRawSqlTask.ResultSetType = SQLTask.ResultSetType.ResultSetType_None;
iexecFillRawSqlTask.SqlStatementSourceType = SQLTask.SqlStatementSourceType.DirectInput;
}
private void MappColumns(PipelineWrapper.IDTSComponentMetaData100 icmDest, PipelineWrapper.CManagedComponentWrapper mcwDest)
{
//mappings
PipelineWrapper.IDTSInput100 inpDestination = icmDest.InputCollection[0];
PipelineWrapper.IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = (PipelineWrapper.IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;
foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
{
try
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
// map external column
// inputColumn.Name = inputColumn.Name.Replace("[", "").Replace("]", "");
PipelineWrapper.IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];
mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
}
catch
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
// map external column
}
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
No comments:
Post a Comment