CODEWITHSUNDEEP
c#visual-studio-2010variablesssisparameter-passing
user2433705
user2433705

Reputation: 151

How to pass Variable to script task SSIS, Visual Studio 2010, c#

I'm trying to find the way how I can pass the server name as a variable to script task within SSIS solution. I'm using VS 2010 and c#.

Within my SSIS I have Name (string) variable defined and I added this variable as ReadOnlyVariable within my Script task.

c# code looks like this:

enter image description here

Problem starts (I guess) when I'm trying to pass the local ServerName to the OleDbDataAdapter which works perfectly with strConn, but doesn't work with my ServerName variable.

I have tried to pass it in a form "User::Name" but it didn't work.

Any suggestion would be appreciated :)

Best

Upvotes: 0

Views: 2046

Answers (1)

Lkor
Lkor

Reputation: 454

Did you try to write exactly this code? (I assume that you have variable Name defined as string). And you can also parameterize your DB name as well.

string strConn = Dts.Connections[Dts.Variables["Name"].Value + "\\MY_DB_NAME"].ConnectionString;

UPDATE: If you would like to connect to your DB via script task, the best practise is to configure new connection manager (see the picture) enter image description here. The most important is the name of your connection (in my case "MY_NEW_CM")

If you have your Server name or DB name saved inside a variable, you can use expression editor inside the connection manager properties (see the picture) enter image description here

Later you can use this configured connection in the code inside Script task as follows:

ConnectionManager cm = Dts.Connections["MY_NEW_CM"];

I assume that you would like to use this connection manager to run some SQL command.

You can make it like that:

System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
ConnectionManager cm = Dts.Connections["MY_NEW_CM"];

// Request an open connection
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

// Do your work
sqlComm = new System.Data.SqlClient.SqlCommand("UPDATE YourTable SET YourColumn = 'SomeValue'", sqlConn);
int rowsAffected = sqlComm.ExecuteNonQuery();

Upvotes: 2

Related Questions