Programming with c#.NET and variables

Hi all,

I know how to basically change SSIS variables from within a C#. NET winform application. But can anyone point me on how to change the contents of a connection manager SSIS connection from withing C#.net please. That is..how do i parameterize a connection manager connection to basically accept the connection information from within the winform app.

Thanks

bump|||

One way is to use an expression to set the ConnectionString property of the ConnectionManager. You can base the expression on a variable.

You can also set the connection manager properties directly through code, by getting the collection of connections from the package.

|||

the problem is that i already have the ssis package...im basically creating a win form front end to mask the ssis package. in my win form im capturing the sql server connection info, and im trying to figure out how to variabalize the sql connection in the package connection manager.

i was able to do something like this easily for a flat file connection but am have no clue on how to do it for the sql connection.

|||

This should not be at all harder then dealing with flat file connections. The only thing you need to do is compose your connection string and assign it to the connection manager. You might want to post your initial code here and somebody will help you.

Thanks.

|||

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

|||

YoungEngineer wrote:

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

You add the Password parameter to the ConnectionString. Typically I build a variable that is used to build a dynamic connection string, and then I just reference that variable in the connection manager's expression property for ConnectionString.|||

is the password field in the connectionstring just called "password"?

Data Source=localhost;User ID=myUser;Initial Catalog=test;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

|||Yes.

Programming with c#.NET and variables

Hi all,

I know how to basically change SSIS variables from within a C#. NET winform application. But can anyone point me on how to change the contents of a connection manager SSIS connection from withing C#.net please. That is..how do i parameterize a connection manager connection to basically accept the connection information from within the winform app.

Thanks

bump|||

One way is to use an expression to set the ConnectionString property of the ConnectionManager. You can base the expression on a variable.

You can also set the connection manager properties directly through code, by getting the collection of connections from the package.

|||

the problem is that i already have the ssis package...im basically creating a win form front end to mask the ssis package. in my win form im capturing the sql server connection info, and im trying to figure out how to variabalize the sql connection in the package connection manager.

i was able to do something like this easily for a flat file connection but am have no clue on how to do it for the sql connection.

|||

This should not be at all harder then dealing with flat file connections. The only thing you need to do is compose your connection string and assign it to the connection manager. You might want to post your initial code here and somebody will help you.

Thanks.

|||

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

|||

YoungEngineer wrote:

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

You add the Password parameter to the ConnectionString. Typically I build a variable that is used to build a dynamic connection string, and then I just reference that variable in the connection manager's expression property for ConnectionString.|||

is the password field in the connectionstring just called "password"?

Data Source=localhost;User ID=myUser;Initial Catalog=test;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

|||Yes.

programmatically setting package variables in job step command line

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \\Package.Variables[User::period].Value;\"" + periodEndDate + "\"";

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

So managing a job in this way seems a bit of a pain. Why not let the package go and get the values from an external location when it is required.

One example would be to use a package configuration, perhaps using a SQL Server configuration. You could update the table values, and then just design the package to use that configuration value, assigning the values to the variables as required. Read up on package configurations if you are not familiar with them.

A variation on the theme it to do the work yourself. You could use any table, not just a configuration format table. Use an Execuite SQL Task to query for the values and using the result set option, you can return values and on the results page of the task, set the output to variable values.

|||

Yes it's been a learning curve in how to do what we're trying to do. The application is driven by a web page where the user says 'run this job, use these parameters'. However, you can't have one predefined job with multiple instances on the server, each with their own set of parameters - which needs to be possible because of the application requirements.

What we are doing now that seems to work is creating a new job, setting the type to ssis, setting the command line to specify the package and parameters, and then starting the job. It is also set to auto delete upon success.

The other way we thought of but decided against was to have the job pick up its runtime parameters from a queue - but then we'd have to create and manage the queue.

The 'create new job' approach lets us run now or set a schedule to run later, all the instances are visible as jobs on the server (based on category to filter out for the UI), and they clean up themselves if they run successfully.

NB: if anyone is curious, changing the command line of an existing job requires the Alter() method to persist the change back to the server, otherwise it just runs with the original command. like this:

string cmdLine = job.JobSteps[0].Command;
cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
job.JobSteps[0].Command = cmdLine;
job.JobSteps[0].Alter();
job.Start();

However, this permanently changes the command line in the job of course and you have to deal with that.

The code that that we're using to dynamically create the job and supply the parameters is pretty close to this:

string jobName = "the name to give to the new job";
string cmdLine = "the command line to run the package and set parameters";
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server svr = new Server(svrConnection);
JobServer agent = svr.JobServer;
if (agent.Jobs.Contains(jobName))
{
agent.Jobs[jobName].Drop();
}

job = new Job(agent, jobName);
job.DeleteLevel = CompletionAction.OnSuccess;
job.Category = "Calculate";
JobStep js = new JobStep(job, "Step 1");
js.SubSystem = AgentSubSystem.Ssis;
js.Command = cmdLine;
job.Create();
js.Create();
job.ApplyToTargetServer("(local)");
job.Alter();
job.Start();

Programmatically loop over variables in the variable dispenser?

In my custom task, I would like to loop over the variables in the variable dispenser, and only modify those that are of a certain type. Is this possible?

Thanks!

I think you can use Me.variables(x).getType to get the type of each variable. If that is not correct, you can define variables name sXXX for string, iXXX for integer, etc. Then parse the variable name, get the first character and determine the variable type.|||

That would work, but I should clarify my question:

I have the VariableDispenser object passed into my Execute method. I do not know ahead of time what variables will be accessible via this instance of the VariableDispenser object. I would like to loop over each variable that is accessible (again, without knowing its name), then perform an operation on it conditionally based on its type (which I will get from the getType method).

Sorry for not being clearer in my original post!

|||I know that we can say variables(0).xxxx to read the first variable. But I am not sure whether we can get the count of variables. If you can't get the count, what you can do is read variables(0), variables(1), etc like that, and put that code in a try..catch. If you get an exception as 'index out of range', come out of the loop.|||This would be a great way to loop over all the variables assuming I could get access to the entire variables collection to begin with. The problem is that all I have is the VariableDispenser object. The VariableDispenser object methods that interact with the Variables collection (LockForRead, LockOneForRead, etc.) require that I know the name of the variables I would like to access. In my component, I do not know this upfront. Unless I am missing something (and I hope I am), I think I am out of luck?|||

Hi David,

I am not sure whether I understood your problem correctly or not. If I am confusing you, I am sorry. So, what you want is variable name and its type before calling variableDispenser object method. If that is true, it is pretty much simple.

DTS.Variables collection has 'count', 'Name' and 'DataType' properties. Loop thru this collection to get the name of a variable and its datatype. If the datatype is what you are looking for, call the VariableDispenser method using the name that you got.

Hope it will work.

|||

Thiru,

No worries. I am probably not being as clear as I could be - I always find it difficult to describe technical problems on the first 3 or 4 tries. Thanks for the help thus far.

I should have clarified from the beginning that I writing my custom task in C#. All of my code exists within the Execute method of my task. I do not appear to have the equivalent of the DTS.Variables collection available to me. Instead, all I have is the VariableDispenser object. Again, I hope I am missing something simple here.

Thanks again for your help,

David

|||

Can I ask why you want to this? How does the type identify the variables you want to modify? I ask in case there is some better way which we could suggest to acheive your business case.

Donald

|||

The answer is kind of convoluted, but here goes:

I have a package that manages the execution of child packages. Exactly what child packages are to be executed in a given run of the parent package are store in a database table. The child packages stored in the database table are looped over in a ForEach loop, and executed sequentially.

Each child package also requires a set of variables for successful execution. The list of variables required differs for each child package. The value for each of the required variables is not going to be known until run-time, immediately before the child package executes. The easiest way for me to manage this is to have a database table that lists what variables are necessary for each child package, and what expression / database query should be executed at run-time to evaluate each variable.

I have found a number of limitations (perceived or real) within the current SSIS achitecture that makes what I am trying to accomplish difficult. To get around these limitations, I have created my own set of objects to interact with the variables stored in the database, evaluate them at run-time, and put them into a single custom "variable list" object (to which an SSIS variable is assigned) that is available to the child packages.

The problem I have is that my parent package contains the variable(s) assigned to the list object(s). For maximum portability, I do not want my child packages to have to know what the names of those variables are. Instead, I would like to write a custom task that loops over all variables accessible via the variable dispenser, identifies those of my custom type, iterates through all the custom variables in the list, and assigns those values to corresponding ssis child variables (of the same name).

I know - that is quite a mouthful, and likely is not as clear as it could be. I may be going down a path that is unnecessary as I have only been playing around with SSIS for about 3 weeks now. Any help / advice is much appreciated.

Thanks,

David

Programmatically developing an entire package..

Hi all,

I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.

Thanks,

Prithvi.

This is the code for creating connections and adding dataflow task. I need to add a script component transformation, but initially I wanted to see if a one-to-one mapping works fine.

Public Sub CreateConnections()

'Add the OLE DB and Flat File Connection Managers

Console.WriteLine("Creating the MyOLEDBConnection")

Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")

cnOLEDB.Name = "MyOLEDBConnection"

cnOLEDB.ConnectionString = "<connection string>"

Console.WriteLine("Creating the MyFlatFileConnection")

Dim cnFile As ConnectionManager = myPackage.Connections.Add("FLATFILE")

cnFile.Name = "MyFlatFileConnection"

cnFile.Properties("ConnectionString").SetValue(cnFile, "C:\sample.csv")

cnFile.Properties("Format").SetValue(cnFile, "Delimited")

cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, False)

cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)

cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)

cnFile.Properties("TextQualifier").SetValue(cnFile, """")

End Sub

Public Sub AddDataFlowTask()

'Add a Data Flow Task

Console.WriteLine("Adding a Data Flow Task")

Dim e As Executable = myPackage.Executables.Add("DTS.Pipeline")

Dim taskDF As TaskHost = CType(e, TaskHost)

taskDF.Name = "DataFlow"

Dim DTP As MainPipe

DTP = CType(taskDF.InnerObject, MainPipe)

' Add the FLAT FILE Source

Console.WriteLine("Adding the File Source")

Dim DFSource As IDTSComponentMetaData90

DFSource = DTP.ComponentMetaDataCollection.New()

DFSource.ComponentClassID = "DTSAdapter.FlatFileSource.1"

DFSource.Name = "FlatFileSource"

' Connect, populate the Input collections and disconnect

Dim SourceInst As CManagedComponentWrapper = DFSource.Instantiate()

SourceInst.ProvideComponentProperties()

DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyFlatFileConnection").ID

DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyFlatFileConnection"))

SourceInst.AcquireConnections(vbNull)

SourceInst.ReinitializeMetaData()

SourceInst.ReleaseConnections()

'checking If the external metadata columns are available as source output columns and the output is 0 which means no columns are being passed as output from source

Dim column As IDTSOutputColumn90

Try

Console.WriteLine("connection name: " & DFSource.RuntimeConnectionCollection(0).Name.ToString)

Console.WriteLine("output collection name: " & DFSource.OutputCollection(0).Name)

Console.WriteLine("output collection description :" & DFSource.OutputCollection(0).Description)

Console.WriteLine("source output columns count :" & DFSource.OutputCollection(0).OutputColumnCollection.Count.ToString

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message.ToString)

End Try

'tried to print col names but it does not print any

For Each column In DFSource.OutputCollection(0).OutputColumnCollection

Console.WriteLine(column.Name.ToString)

Console.WriteLine(column.DataType.ToString)

Next

' Add the OLEDB Destination

Console.WriteLine("Adding OLEDB Destination")

Dim DFDestination As IDTSComponentMetaData90

DFDestination = DTP.ComponentMetaDataCollection.New()

DFDestination.ComponentClassID = "DTSAdapter.OLEDBDestination"

DFDestination.Name = "OLEDBDestination"

' Create an instance of the component

Dim DestInst As CManagedComponentWrapper = DFDestination.Instantiate()

DestInst.ProvideComponentProperties()

If DFDestination.RuntimeConnectionCollection.Count > 0 Then

DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyOLEDBConnection").ID

DFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyOLEDBConnection"))

End If

DestInst.SetComponentProperty("AccessMode", 0)

DestInst.SetComponentProperty("OpenRowset", "tempSSIS")

DestInst.SetComponentProperty("FastLoadKeepNulls", True)

' Map a connection between the source and destination

Dim path As IDTSPath90 = DTP.PathCollection.New()

path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFDestination.InputCollection(0))

Dim InColumns As IDTSVirtualInputColumnCollection90 = DFDestination.InputCollection(0).GetVirtualInput().VirtualInputColumnCollection()

' the number of input columns to destination is zero

Console.WriteLine("input columns : " & InColumns.Count.ToString)

Try

DestInst.AcquireConnections(vbNull)

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message)

End Try

DestInst.ReinitializeMetaData()

'Console.WriteLine("input columns : " & DFDestination.InputCollection(0).InputColumnCollection.Count.ToString)

For Each input As IDTSInput90 In DFDestination.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

Console.WriteLine(vColumn.Name.ToString)

Console.WriteLine(vColumn.DataType)

DestInst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In DFDestination.InputCollection(0).InputColumnCollection

exCol = DFDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

DestInst.MapInputColumn(DFDestination.InputCollection(0).ID, col.ID, exCol.ID)

Next

DestInst.ReleaseConnections()

End Sub

Please see if i did wrong any where(which I always happen to do). But based on the msdn material, the above code should work, i guess.