OLE DB Command not updating

I have a data flow task, and in that is an OLE DB Source that upon success, connects to an OLE DB Command. The OLE DB Source runs a sql command from a variable. (I've tested the sql; it parses and returns values.) The OLE DB Command is a very simple update sql statement with one column mapping. When I execute the task, it says it is successful, but when I check the table, nothing has been updated. I have ensured the connection is successful, and I am connecting to the correct db. Any suggestions would be helpful! Thanks!

Use SQL Profiler to capture the SQL statements that are being sent to the database. Odds are there is something related to the parameter mapping that is preventing the command from doing what you need it to.

Another thing to check is to ensure that the commands are being executed against the database that you think they are. If you have multiple connection managers, this can be easy to do.

|||

I found the problem. The variable used in the OLE DB source I thought I had changed to call a new stored procedure. However, its value was not actually changing and it was calling the wrong procedure, returning no values. My next question is why when I change the value in the properties window, does the variable not take the change?

|||Is the variables EvaluateAsExpression property set to true? This would cause that behavior.|||

Lindsay wrote:

I found the problem. The variable used in the OLE DB source I thought I had changed to call a new stored procedure. However, its value was not actually changing and it was calling the wrong procedure, returning no values. My next question is why when I change the value in the properties window, does the variable not take the change?

Did SQL Profiler end up being useful?

|||

Phil, yes the EvaluateAsExpression property was set to True; how embarassing LOL. Thank you!!

|||

Matthew, I've never used SQL Profiler before today, and am not familiar with it. I must need to modify the trace, because it has been running for a few hours now (actually I totally forgot about it)!!

|||

Lindsay wrote:

Matthew, I've never used SQL Profiler before today, and am not familiar with it. I must need to modify the trace, because it has been running for a few hours now (actually I totally forgot about it)!!

Oh no! It's probably best to just stop the trace at this point.

I'm sorry - I didn't mean to make things more complex. If you start the trace right before you run the package, and then stop it right after the package completes, you can then look through a (relatively) small set of queries sent to the server. In t his context, one of them should leap out because it the same query repeating over and over again. There are many ways to filter the data before and after it's recorded, but this is often the quickest and easiest way to see what the client application (in this case, SSIS) is REALLY sending to the server.