Debugging a SSIS OleDbCommand

oledbssis

I have an OleDb Command component in a dataflow that does an update(yes, I'm aware of the performance implications).

Running the pack in BIDS shows me that the component is being reached and runs correctly, turns green when complete, etc. However, I don't see the update in the database.

I am logging all events on the component, but there does not seem to be a way to get the actual command text sent to the server. I can see the ICommandText::SetCommandText, ICommandPrepare::Prepare and ICommandWithParameters::GetParameterInfo events complete, but nothing changes in the db. What else can I look at to find out what is really happening between these two systems?

Best Answer

You can check what's going on using SQL Profiler. When you execute an OLE DB task, you should see something like this as an RPC:Completed event:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 int',N'update testtable set id = @P1',1
select @p1

In this case, I'm just updating a test table (called testtable).

Then, for each iteration, you'll see an RPC:Completed event which reads something like:

exec sp_execute 1,50

This is saying to execute the first prepared event (our update statement--sp_prepare's first parameter is "1" here), and send in 50 as the new ID for testtable.

If you don't see these calls on your database as you debug your flow, it might indicate that you're actually doing the process on a different instance (or maybe there's something like a trigger preventing your change from occurring?).

Related Question