Its been awhile since my last post and I have been very busy working on developing Android applications out of curiosity and I'm impress of what I have done in some little time when the last time I touch Java was during my college days,but at the end been a SQL Developer paid the bills and we have a problem where we have to deal with some very large files. That's when an idea came into place, why not used the data balance distributor component The idea is as follow you can create multiple partitions or you can send the data to separate tables and the then using union all you can retrieved the data, we used the partition option and everyone is very happy eventually we implemented the change to not just files but also transformations, So here is a variation of the code, happy days...
#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;
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;
#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();
public void Main()
{
try
{
Variables vars = null;
//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.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(@"\\10.48.72.139\MichaelHunterFiles\ssis", SsisName);
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();
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());
ssis.Name = "Error" + ssis.Name.ToString();
SaveSSIS(@"\\10.48.72.139\MichaelHunterFiles\ssis", ssis.Name.ToString());
ssis.Dispose();
throw e;
}
}
/*
* 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