2

I have a couple of SSIS packages in Production that are using the SqlConnectionStringBuilder to connect to the SQL Server database. These packages were working fine till about 10 days ago and we did not change anything on Prod. Then, all of a sudden, the package started failing with the error being

The connection string format is not valid. It must consist of one or more components of the form x=y separated by semicolons.

When I try to debug the script task, the script task always fails at the following:

string connstring = Dts.Variables["User::Static"].GetSensitiveValue().ToString();
//MessageBox.Show(connstring);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connstring);
builder.ConnectTimeout = 1800;

From the error message it seems I am passing a blank connection string, but that is not the case. I have hard coded the connection string to the Static variable so that it does not dynamically build (obviously for test and resolution purposes).

What are my alternatives to this method and what else can I do to troubleshoot this issue? Any help is appreciated.

EDIT:

Upon digging further, I get to the point where it is trying to execute the Stored proc on the database. 

SqlCommand command = new SqlCommand("[dbo].[GetdataALL]", sqlConn);
                    command.CommandTimeout = 1800;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@Columns1", Dts.Variables["User::Columns"].Value.ToString());
                    command.Parameters.AddWithValue("@Where_ID", Dts.Variables["User::WhereID"].Value.ToString());
                    command.ExecuteNonQuery();

What would be a probable cause of the failure? Thank you.

Hadi
  • 36,233
  • 13
  • 65
  • 124
rvphx
  • 2,324
  • 6
  • 40
  • 69
  • 1
    it's not really necessary to use a builder if you know the whole thing. Just a string works. – KeithL Feb 27 '20 at 18:32
  • 1
    How is this DTS variable set? Through an Environment var? Sensitive variables can get lost during an upgrade or database migration (at least to my experience). Try to set it again. – wp78de Feb 27 '20 at 18:33
  • I tried setting the variable again inside the script task, but it still fails at the same spot. Trying Keith's suggestion now. – rvphx Feb 27 '20 at 18:53
  • No luck either way. Its failing on the SqlCommand line (edited the question above with more details). – rvphx Feb 27 '20 at 19:38
  • Why not using Execute SQL Task instead? – Hadi Feb 27 '20 at 20:30
  • The idea is to generate a flat file for which the columns and filters are stored on the database. I can either generate 1 file or 100's depending on which files are active. I am not sure if I can do that in Execute SQL task – rvphx Feb 27 '20 at 21:34
  • what does this give to you: `//MessageBox.Show(connstring);`? – LONG Feb 28 '20 at 19:36
  • It gives me the exact connection string that my database resides on. Everything including the options and etc. – rvphx Feb 28 '20 at 19:46

1 Answers1

0

Are you getting this error only when debugging? If so, you need to change the protection level to EncryptSensitiveWithPassword. Check HodgyHodson's answer.

Refer this link

Mani Live
  • 168
  • 1
  • 7