Creating SSIS OLE DB Source with Parameters programmatically with .NET
-
Hello, I am trying to build a SSIS Package programmatically with .NET (C#). Now I have problems with the component "OLE DB Source" when trying to set the ParameterMapping in order to use variables in the data flow task.
...
string srcSql = "SELECT bla bla from ABC where YY = ? and BB = ? ";
Source.SetComponentProperty("AccessMode", 2);
Source.SetComponentProperty("SqlCommand", srcSql );Guid ParamGuid = new Guid(TrackingIDOld.ID);
Guid ParamGuid1 = new Guid(TrackingIDNew.ID);// Here I am trying to set the parameters. However, it does not work!
Source.SetComponentProperty("Parameter0 Name", ParamGuid.ToString());
Source.SetComponentProperty("Parameter1 Name", ParamGuid1.ToString());Source.AcquireConnections(null);
Source.ReinitializeMetaData();
...Alternatively, I also tried this, but it does not work, too:
Source.SetComponentProperty("ParameterMapping", "User::TrackingIDOLD"); // Parameter 0
Source.SetComponentProperty("ParameterMapping", "User::TrackingIDNew"); // Parameter 1Does somebody know the correct syntax how to set the parameters? Thanks for any advices! Best wishes, Miroslav Stimac
-
Hello, I am trying to build a SSIS Package programmatically with .NET (C#). Now I have problems with the component "OLE DB Source" when trying to set the ParameterMapping in order to use variables in the data flow task.
...
string srcSql = "SELECT bla bla from ABC where YY = ? and BB = ? ";
Source.SetComponentProperty("AccessMode", 2);
Source.SetComponentProperty("SqlCommand", srcSql );Guid ParamGuid = new Guid(TrackingIDOld.ID);
Guid ParamGuid1 = new Guid(TrackingIDNew.ID);// Here I am trying to set the parameters. However, it does not work!
Source.SetComponentProperty("Parameter0 Name", ParamGuid.ToString());
Source.SetComponentProperty("Parameter1 Name", ParamGuid1.ToString());Source.AcquireConnections(null);
Source.ReinitializeMetaData();
...Alternatively, I also tried this, but it does not work, too:
Source.SetComponentProperty("ParameterMapping", "User::TrackingIDOLD"); // Parameter 0
Source.SetComponentProperty("ParameterMapping", "User::TrackingIDNew"); // Parameter 1Does somebody know the correct syntax how to set the parameters? Thanks for any advices! Best wishes, Miroslav Stimac
I assume you're using some sort of API, but you don't say, so how are we to help you? Alternatively, I know people who use a product called Mist and its BIML language for generating SSIS packages. (I've never used it, I prefer to do it the hard way.)
-
I assume you're using some sort of API, but you don't say, so how are we to help you? Alternatively, I know people who use a product called Mist and its BIML language for generating SSIS packages. (I've never used it, I prefer to do it the hard way.)
Hello, Yes, you are right. I am using an API. It is the namespace Microsoft.SqlServer.Dts.Pipeline.Wrapper See here: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper(v=sql.110).aspx[^] Especially the method SetComponentProperty: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper.cmanagedcomponentwrapperclass.setcomponentproperty(v=sql.110).aspx[^]