public SSISExcelSource(ExcelSource dbsrc, MainPipe pipe, ConnectionManager cm)
: base(pipe, "Excel Source")
{
// create the oledb source
//set connection properies
cm.Name = "Excel Source Connection Manager";
cm.ConnectionString = dbsrc.ConnectionString;
cm.Description = dbsrc.Description;
//set connection properties
//mwrt.IDTSConnectionManagerExcel100 ecm = cm.InnerObject as mwrt.IDTSConnectionManagerExcel100;
//ecm.ExcelFilePath = dbsrc.FilePath;
//ecm.FirstRowHasColumnName = dbsrc.Header;
//ecm.ExcelVersionNumber = mwrt.DTSExcelVersion.DTSExcelVer_2007;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
// Finalize
this.Reinitialize(dcomp);
}
public void TestOutputColumn_Timecode_Missing()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
// Before this is default setup for a clean component
speechToText.OutputCollection[0].OutputColumnCollection.RemoveObjectByID(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].ID);
IDTSOutputColumn100 tempCol = speechToText.OutputCollection[0].OutputColumnCollection.New();
tempCol.Name = "TempCol";
tempCol.SetDataTypeProperties(DataType.DT_STR, 10, 0, 0, 1252);
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: Required Output Column Timecode is missing.", events.errorMessages[0]);
}
public void TestAddNumberOfRowsOutputColumns()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 actual = textFileSplitter.OutputCollection.New();
ManageColumns.AddNumberOfRowsOutputColumns(actual);
Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Number of Columns is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnName, actual.OutputColumnCollection[0].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[0].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[0].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnDescription, actual.OutputColumnCollection[0].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnName, actual.OutputColumnCollection[1].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_I8, actual.OutputColumnCollection[1].DataType, "DataType is wrong");
Assert.AreEqual(0, actual.OutputColumnCollection[1].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnDescription, actual.OutputColumnCollection[1].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnName, actual.OutputColumnCollection[2].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[2].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[2].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnDescription, actual.OutputColumnCollection[2].Description, "Description is wrong");
}
public void TestValidateOK()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISVALID;
Assert.AreEqual(expected, actual);
Assert.AreEqual(0, events.errorMessages.Count, "There are error messages");
}
public override void AcquireConnections(object transaction)
{
if (ComponentMetaData.RuntimeConnectionCollection.Count <= 0)
{
return;
}
if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager == null)
{
return;
}
var cm = DtsConvert.GetWrapper(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
if (cm.InnerObject != null)
{
var bimlCatalog = cm.InnerObject as ConnectionManagerAdoNet;
if (bimlCatalog == null)
{
throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");
}
_connection = bimlCatalog.AcquireConnection(transaction) as SqlConnection;
}
if (_connection == null || _connection.State == ConnectionState.Open)
{
return;
}
_connection.Open();
}
public void TestSetOutputColumnDefaults()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 output = textFileSplitter.OutputCollection.New();
IDTSOutputColumn100 actual = output.OutputColumnCollection.New();
ManageColumns.SetOutputColumnDefaults(actual, 1252);
Assert.AreEqual(DataType.DT_STR, actual.DataType, "DataType is wrong");
Assert.AreEqual(255, actual.Length, "Length is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Row Disposition is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.TruncationRowDisposition, "Truncate Disposition is wrong");
}
void page_CreateNewConnection(object sender, EventArgs args)
{
IDtsConnectionService ConnectionService =
(IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));
if (ConnectionService == null)
{
Exception ex = new InvalidOperationException("Connection Service Unavailable");
this.ReportErrors(ex);
return;
}
ArrayList connections = ConnectionService.CreateConnection("SSDS");
if (connections != null && connections.Count > 0)
{
ConnectionManager cm = connections[connections.Count - 1] as ConnectionManager;
this.ComponentMetadata.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
this.ComponentMetadata.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(cm);
}
return;
}
public override IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp)
{
if (String.IsNullOrEmpty(quotedTableName))
{
throw new ArgumentException("Destination table name is empty");
}
IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
destComp.ComponentClassID = OLEDB_DEST_GUID;
destComp.ValidateExternalMetadata = true;
destDesignTimeComp = destComp.Instantiate();
destDesignTimeComp.ProvideComponentProperties();
destComp.Name = "OleDB Destination - Sql Server";
destDesignTimeComp.SetComponentProperty("AccessMode", 0);
destDesignTimeComp.SetComponentProperty("OpenRowset", quotedTableName);
// set connection
destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr);
destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID;
// get metadata
destDesignTimeComp.AcquireConnections(null);
destDesignTimeComp.ReinitializeMetaData();
destDesignTimeComp.ReleaseConnections();
extCols = destComp.InputCollection[0].ExternalMetadataColumnCollection;
return(destComp);
}
public override IDTSComponentMetaData100 Initialize()
{
//create flat file destination component
IDTSComponentMetaData100 comp = base.Initialize();
_cm.Name = $"FlatFile Destination Connection Manager {comp.ID}";
//Create a new FlatFileDestination component
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in _dst.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm);
}
this.Reinitialize(dcomp);
return(comp);
}
private void cmbSqlConnections_SelectedValueChanged(object sender, EventArgs e)
{
ComboBox comboxBox = (ComboBox)sender;
// Check for index 0 and <New Item...>
if (comboxBox.SelectedIndex == 0)
{
// Use connection service to create a new connection.
ArrayList newConns = _dtsConnectionService.CreateConnection(Connection_Type);
if (newConns.Count > 0)
{
// A new connection has been created, so populate and select
ConnectionManager newConn = (ConnectionManager)newConns[0];
PopulateConnectionsCombo(comboxBox, Connection_Type, newConn.Name);
}
else
{
// Create connection has been cancelled
comboxBox.SelectedIndex = -1;
}
}
// An connection has been selected. Verify it exists and update component.
if (_connections.Contains(comboxBox.Text))
{
// Get the selected connection
ConnectionManager connectionManager = _connections[comboxBox.Text];
// Save selected connection
_dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID =
_connections[comboxBox.Text].ID;
_dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.GetExtendedInterface(_connections[comboxBox.Text]);
}
}
public override IDTSComponentMetaData100 Initialize()
{
IDTSComponentMetaData100 comp = base.Initialize();
// create the adonet source
//set connection properies
_cm.Name = "AdoNet Source Connection Manager";
_cm.ConnectionString = _src.ConnectionString;
_cm.Description = _src.Description;
//cm.Qualifier = "System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
if (!String.IsNullOrEmpty(_src.DBConnection.Qualifier))
{
_cm.Qualifier = _src.DBConnection.Qualifier;
}
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in _src.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm);
}
// Finalize
Reinitialize(dcomp);
return(comp);
}
public override IDTSComponentMetaData100 Initialize()
{
// create the odbc source
IDTSComponentMetaData100 comp = base.Initialize();
//set connection properies
_cm.Name = "ODBC Source Connection Manager";
_cm.ConnectionString = _src.ConnectionString;
_cm.Description = _src.Description;
//do not require Qualifier
//cm.Qualifier = dbsrc.DBConnection.Qualifier;
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in _src.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm);
}
// Finalize
Reinitialize(dcomp);
return(comp);
}
/// <summary>
/// This method evaluate expressions like @([System::TaskName] + [System::TaskID]) or any other operation created using
/// ExpressionBuilder
/// </summary>
/// <param name="mappedParam">The mapped param.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns></returns>
public static object EvaluateExpression(string mappedParam, VariableDispenser variableDispenser)
{
Regex regex = new Regex(Keys.REGEX_EMAIL, RegexOptions.IgnoreCase);
if (regex.IsMatch(mappedParam))
{
return(mappedParam);
}
object variableObject;
try
{
var expressionEvaluatorClass = new ExpressionEvaluator
{
Expression = mappedParam
};
expressionEvaluatorClass.Evaluate(DtsConvert.GetExtendedInterface(variableDispenser), out variableObject, false);
}
catch (Exception) // for already initialized values
{
variableObject = mappedParam;
}
return(variableObject);
}
public void TestChangeRowsProcessedOutputTypeToData()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
Boolean exceptionThrown = false;
try
{
instance.SetOutputProperty(textFileSplitter.OutputCollection[3].ID, ManageProperties.typeOfOutput, Utilities.typeOfOutputEnum.DataRecords);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
public void TestValidateExtraCustomProperty()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection.New();
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: There is either to many or not enough custom properties.", events.errorMessages[0]);
}
public void TestOutputColumn_InvalidCustomProperty()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
// Before this is default setup for a clean component
IDTSCustomProperty100 cp = speechToText.OutputCollection[0].OutputColumnCollection[0].CustomPropertyCollection.New();
cp.Name = "IAmInvalid";
cp.Value = "IAmInvalid";
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: Output Column InputChannel has invalid property IAmInvalid.", events.errorMessages[0]);
}
/// <summary>
/// (OK)
/// </summary>
/// <returns> </returns>
private bool TryGetDictionaryDbConnection(bool open, out IDbConnection dbConnection)
{
IDTSRuntimeConnection100 dtsRuntimeConnection100;
IDTSConnectionManagerDatabaseParameters100 dtsConnectionManagerDatabaseParameters100;
ConnectionManager connectionManager;
dbConnection = null;
dtsRuntimeConnection100 = this.ComponentMetaData.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_NAME_DICTIONARY];
if ((object)dtsRuntimeConnection100.ConnectionManager != null)
{
connectionManager = DtsConvert.GetWrapper(dtsRuntimeConnection100.ConnectionManager);
if ((object)(dtsConnectionManagerDatabaseParameters100 = connectionManager.InnerObject as IDTSConnectionManagerDatabaseParameters100) == null)
{
return(false);
}
if (open)
{
dbConnection = (IDbConnection)dtsConnectionManagerDatabaseParameters100.GetConnectionForSchema();
}
}
return(true);
}
public SSISOdbcSource(OdbcSource dbsrc, MainPipe pipe, ConnectionManager cm)
: base(pipe, "ODBC Source")
{
// create the odbc source
//set connection properies
cm.Name = "ODBC Source Connection Manager";
cm.ConnectionString = dbsrc.ConnectionString;
cm.Description = dbsrc.Description;
//do not require Qualifier
//cm.Qualifier = dbsrc.DBConnection.Qualifier;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
// Finalize
this.Reinitialize(dcomp);
}
/// <summary>
/// Add a connection to the script component.
/// </summary>
/// <param name="referenceConnectionName"></param>
/// <param name="connectionNameInComponent"></param>
/// <returns></returns>
internal IDTSRuntimeConnection100 AddConnection_m(string referenceConnectionName, string connectionNameInComponent)
{
bool connectionExists = false;
IDTSRuntimeConnection100 conn = (IDTSRuntimeConnection100)null;
for (int r = 0; r < ComponentMetaData.RuntimeConnectionCollection.Count; r++)
{
if (ComponentMetaData.RuntimeConnectionCollection[r].Name == connectionNameInComponent)
{
connectionExists = true;
conn = ComponentMetaData.RuntimeConnectionCollection[r];
conn.Name = connectionNameInComponent;
}
}
if (connectionExists == false)
{
conn = ComponentMetaData.RuntimeConnectionCollection.New();
ConnectionManager cmg = GetConnectionManager(referenceConnectionName);
conn.ConnectionManager = DtsConvert.GetExtendedInterface(cmg);
conn.ConnectionManagerID = cmg.ID;
conn.Name = connectionNameInComponent;
}
return(conn);
}
public void TestExecuteRecogniseAsyncBasic()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = config.AppSettings.Settings["subscriptionKey"].Value;
Uri targetURI = new Uri(@"wss://speech.platform.bing.com/api/service/recognition/continuous");
SpeechToTextImplement testMe = new SpeechToTextImplement(speechToText, "en-us", targetURI, config.AppSettings.Settings["subscriptionKey"].Value);
string filename = "Ex_Pro_1.mp3";
testMe.ExecuteRecogniseAsync(filename).Wait();
Assert.IsTrue(testMe.Results.Count > 0);
}
public SSISAdoNetSource(AdoNetSource dbsrc, MainPipe pipe, ConnectionManager cm)
: base(pipe, "ADO NET Source")
{
// create the adonet source
//set connection properies
cm.Name = "AdoNet Source Connection Manager";
cm.ConnectionString = dbsrc.ConnectionString;
cm.Description = dbsrc.Description;
//cm.Qualifier = "System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
cm.Qualifier = dbsrc.DBConnection.Qualifier;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
// Finalize
this.Reinitialize(dcomp);
}
/// <summary>
/// This method evaluate expressions like @([System::TaskName] + [System::TaskID]) or any other operation created using
/// ExpressionBuilder
/// </summary>
/// <param name="mappedParam">The mapped param.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns></returns>
public static object EvaluateExpression(string mappedParam, VariableDispenser variableDispenser)
{
object variableObject;
var regex = new Regex(Keys.REGEX_EMAIL, RegexOptions.IgnoreCase);
if (regex.IsMatch(mappedParam))
{
return(mappedParam);
}
if (mappedParam.Contains("@"))
{
var expressionEvaluatorClass = new ExpressionEvaluatorClass
{
Expression = mappedParam
};
expressionEvaluatorClass.Evaluate(DtsConvert.GetExtendedInterface(variableDispenser),
out variableObject,
false);
}
else
{
variableObject = mappedParam;
}
return(variableObject);
}
private void AssignConnectionManager(ConnectionManager cmg)
{
ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cmg);
ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID = cmg.ID;
//cmg.Properties["Password"].SetValue(cmg, ConnectionPassword);
//_connectionAssgined = true;
//PopulateMetadata();
}
public static void SetConnection(this IDTSComponentMetaData100 obj, ConnectionManager con)
{
if (obj.RuntimeConnectionCollection.Count > 0)
{
obj.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(con);
obj.RuntimeConnectionCollection[0].ConnectionManagerID = con.ID;
}
}
/// <summary>
/// Gets the variable dispenser wrapper. This method itself is just an abstraction for the condition compilation code.
/// </summary>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns>A version typed variable dispamser wrapper.</returns>
private static IDTSVariableDispenserXX GetVariableDispenserWrapper(VariableDispenser variableDispenser)
{
#if YUKON
IDTSVariableDispenserXX variableDispenserWrapper = DtsConvert.ToVariableDispenser90(variableDispenser);
#else // KATMAI, DENALI, SQL2014, 2016
IDTSVariableDispenserXX variableDispenserWrapper = DtsConvert.GetExtendedInterface(variableDispenser);
#endif
return(variableDispenserWrapper);
}
private void AssignConnectionManager(ConnectionManager cmg)
{
ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cmg);
ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID = cmg.ID;
if (string.IsNullOrEmpty(OpenRowset) && string.IsNullOrEmpty(OpenRowsetVariable) && string.IsNullOrEmpty(SqlCommand) && string.IsNullOrEmpty(SqlCommandVariable))
{
}
else
{
RetrieveMetaData();
}
}
public SSISFlatFileDestination(FlatFileDestination fdst, MainPipe pipe, IDTSComponentMetaData100 src, int outputID, ConnectionManager cm)
: base(pipe, "Flat File Destination", outputID)
{
cm.Name = String.Format(CultureInfo.InvariantCulture, "FlatFile Destination Connection Manager {0}", outputID);
//Create a new FlatFileDestination component
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in fdst.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
this.Reinitialize(dcomp);
//Create datatype converter if needed
Dictionary <string, int> converted = new Dictionary <string, int>();
IDTSVirtualInput100 vInput = src.InputCollection[0].GetVirtualInput();
if (this.needDataTypeChange(vInput, comp.InputCollection[0]))
{
//create the destination column collection
Dictionary <string, MyColumn> exColumns = new Dictionary <string, MyColumn>();
foreach (IDTSExternalMetadataColumn100 exColumn in comp.InputCollection[0].ExternalMetadataColumnCollection)
{
MyColumn col = new MyColumn();
col.Name = exColumn.Name;
col.DataType = exColumn.DataType;
col.Length = exColumn.Length;
col.Precision = exColumn.Precision;
col.Scale = exColumn.Scale;
col.CodePage = exColumn.CodePage;
exColumns.Add(exColumn.Name, col);
}
SSISDataConverter ssisdc = new SSISDataConverter(pipe, src, outputID, exColumns);
src = ssisdc.MetadataCollection;
converted = ssisdc.ConvertedColumns;
outputID = 0;
}
this.ConnectComponents(src, outputID);
this.MatchInputColumns(converted, false);
}
private string[] ScanDtsObjectVariablesForErrors(DtsContainer o)
{
List <string> listOtherErrors = new List <string>();
foreach (Variable v in o.Variables)
{
if (!v.SystemVariable && v.GetPackagePath().StartsWith(((IDTSPackagePath)o).GetPackagePath() + ".Variables[")) //only variables in this scope
{
object val;
try
{
val = v.Value; //look at each value to see if each variable expression works
}
catch (Exception ex)
{
listOtherErrors.Add(ex.Message);
continue;
}
//if we haven't already gotten an error, try to validate the expression
IDTSInfoEventsWrapper events = new IDTSInfoEventsWrapper();
try
{
if (!string.IsNullOrEmpty(v.Expression))
{
Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass eval = new Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass();
eval.Expression = v.Expression;
eval.Events = events;
#if KATMAI || DENALI || SQL2014
eval.Evaluate(DtsConvert.GetExtendedInterface(o.VariableDispenser), out val, false);
#else
eval.Evaluate(DtsConvert.ToVariableDispenser90(o.VariableDispenser), out val, false);
#endif
}
}
catch
{
if (events.Errors.Length > 0)
{
listOtherErrors.Add("Error in expression for variable " + v.QualifiedName + ": " + events.Errors[0]);
}
continue;
}
}
}
return(listOtherErrors.ToArray());
}
public override IDTSComponentMetaData100 Initialize()
{
// create the odata source
IDTSComponentMetaData100 comp = base.Initialize();
//set connection properies
_cm.Name = "OData Source Connection Manager";
_cm.ConnectionString = _src.ConnectionString;
_cm.Description = _src.Description;
//ODataConnectionManager ocm = (ODataConnectionManager)cm.InnerObject;
//mwrt.IDTSConnectionManager100 dcm = DtsConvert.GetExtendedInterface(cm);
//dcm.AcquireConnection(null);
//set connection properties
//mwrt.IDTSConnectionManagerHttp100 hcm = cm.InnerObject as mwrt.IDTSConnectionManagerHttp100;
//cm.Properties["UserName"].SetValue(cm, "");
//string val = cm.Properties["Url"].GetValue(cm).ToString();
//foreach( var prop in cm.Properties)
//{
// string val = prop.GetValue(cm).ToString();
//}
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in _src.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm);
}
// Finalize
this.Reinitialize(dcomp);
return(comp);
}
public void GenerateColumnForVirtualInput(ConnectionManager conMgrDestination,
IDTSComponentMetaData100 flatFileDestination, string ColumnDelimeter, bool UniCode)
{
// Add columns to the FlatFileConnectionManager
wrap.IDTSConnectionManagerFlatFile100 fileConnection = null;
fileConnection = conMgrDestination.InnerObject as wrap.IDTSConnectionManagerFlatFile100;
fileConnection.Unicode = UniCode;
DtsConvert.GetExtendedInterface(conMgrDestination);
if (fileConnection == null)
{
throw new Exception("Invalid file connection");
}
// Get the upstream columns
IDTSVirtualInputColumnCollection100 vColumns = flatFileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;
for (int cols = 0; cols < vColumns.Count; cols++)
{
wrap.IDTSConnectionManagerFlatFileColumn100 col = fileConnection.Columns.Add();
// If this is the last column, set the delimiter to CRLF.
// Otherwise keep the delimiter as ",".
if (cols == vColumns.Count - 1)
{
col.ColumnDelimiter = "\r\n";
}
else
{
if (ColumnDelimeter == "\\t" || ColumnDelimeter == "Tab {t}")
{
col.ColumnDelimiter = "\t";
}
else
{
col.ColumnDelimiter = ColumnDelimeter;
}
col.ColumnDelimiter = "\t";
}
col.ColumnType = "Delimited";
col.DataType = vColumns[cols].DataType;
col.DataPrecision = vColumns[cols].Precision;
col.DataScale = vColumns[cols].Scale;
wrap.IDTSName100 name = col as wrap.IDTSName100;
name.Name = vColumns[cols].Name;
}
}
