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!