Error 0x80040E37

Has anyone ever run into this error? I have an SSIS package that works fine on our test server, but when I move it to production it's giving this error. The error is on the step that is using a variable/expression to connect to a "dynamic" database and server. It's a execute SQL command returning xml. I watched the variables in the locals and was lucky enough to find the the xml was getting this error. The exact error is:

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

Thanks,

Phil

The error message indicates that it can't find the 'exp_a_client_vw' object. Since you are using a dynamic connection, are you sure it is pointing to the correct database?|||

I'm pretty sure it is. Like I said, it's the same package that worked on dev and it found it's way to the correct database/server. I'm watching the variables in the locals as it gets to that step/container and they are correct. I'm trying to figure out now how I can write the entire connection string to a variable and watch it.

Phil

|||Use a script task to copy the value of the expression based variable to a regular variable immediately before the Exec SQL step.|||

Ok, I follow you so far. But how exactly can I tell the script task to grab the "ConnectionString" property of the my "Transform XML (SP) connection" which is the connections' exact name?

Thanks,

Phil

|||You're setting the connect string on the connection manager via an expression, right? Something like:

Code Snippet

"Data Source=" + User::DataSource + ";Initial Catalog=" + User::Catalog" + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

Move that value to a new expression based variable. Then, in the connection manager, set the expression for the connection string to the new variable you created.

|||

Thanks. I got it. Turns out that the SP I was running doesn't have that object. I appreciate your help.

Phil