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.

OLE DB Command

I am having trouble figuring out how to use the OLE DB Command (for updating a record). On the first tab (Connection Managers), I can select my database server under 'Connection Manager'. When I looked at the second tab (Component Properties) I thought, this does not seem to have anything related to choosing a data TABLE to use. The third and fourth tabs do not seem to work because they require an output and none is there, and I can't add one (?)

When I selected the 3rd tab, I see the following at the bottom of the window :

Error at ReadAL3Files [OLE DB Command [6638]]: An OLE DB error has occured. Error code: 0x80040E0C. An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object"

So I look at the properties for the components, to see if there is one where 'text' is not set. Then I think, maybe I have to enter the update statement directly (under SQL Command). There didn't seem to be a way to build this kind of statement, so I guessed at the syntax

update [dbo].[BriteMeter]
set sampledatetime = [Column_7],
GradeID = [Column_8]
where SampleID = SampleID;

and switching to tab 3 and 4 showed a different error :
Statement(s) could not be prepared
Invalid column name 'Column 8'
Invalid column name 'Column 7'

I've tried entering those column names as described here : http://technet.microsoft.com/en-us/library/ms141138.aspx. Tried putting them in quotes, square brackets, I always get that error.

Am I approaching this right or am I missing something?

Dear Friend,

Create the update SQL statment inside database as a stored procedure with input and output parameters.

And in the OLE DB Command text property write: EXEC StoredProcedure ?,?

This example has 2 input parameters.

In my blog check the label SSIS and there is an example.

Helped?

regards!

|||Seriously? There is no simpler way to do an update than this? Well... ok.

|||

Dear friend,

In my opinion, is the better way to do that. The most advantages with this way is that:

1. You avoid SQL injection

2. The SP is already compiled in your database.

3. in the case you need to make some changes, you only need to change the SP in spite of change the stored procedure...

Helped? :-(

I hope so... if not tell me!

Kind regards!