SSIS oledb command error

Question

I have used  oledb command  many time in sevral packages for type one/two updates and inserts. I am running into a strange problem when i use alias to table name in a updates statement. It errors out at the alias name

Error: "Invalid object name 'aliasname' "

example: UPDATE A SET COLUMN1 = ?,COLUMN2 = ? FROM TABLENAME A WHERE COLUMN3 = ?

Is it the way SSIS OLEDB Command works or is the somethng that I am missing???

Started it thinking its a minute job and still digging into it :)

Any help

Answer 1

Try this"

UPDATE TABLENAME SET COLUMN1 = ?,COLUMN2 = ? WHERE COLUMN3 = ?

---

Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Answer 2

This query is  not that straight forward

UPDATE A SET COLUMN1 = ?,COLUMN2 = ?

FROM TABLENAME A inner join table name b on a.x = b.x  and a.y<>b.y  WHERE COLUMN3 = ?

it does not allow subquery as filter.

Answer 3

The OLEDB command works a little differently. See Configure OLEDB Command Transformation - http://msdn.microsoft.com/en-us/library/ms141773.aspx

---

Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Answer 4

I have used the OLEDB Command Transformation in many places. I know how it works but here i am trying to give alias to the table that is been updated as it has to join to itself and update the most recent record and SSIS is not likeing it. The error i am getting is from the very first line of the statement

"update a"

error: "invalid object name 'a' "

Answer 5

This query is  not that straight forward

UPDATE A SET COLUMN1 = ?,COLUMN2 = ?

FROM TABLENAME A inner join table name b on a.x = b.x  and a.y<>b.y  WHERE COLUMN3 = ?

it does not allow subquery as filter.

did you tried to create a stored procedure from your statement and then just run that stored procedure with parameters in OLE DB Command?

store procedure:

create procedure dbo.MyUpdateSt
@Column1 as int,
@Column2 as int,
@Column3 as int
as
begin
UPDATE A SET COLUMN1 = @Column1,COLUMN2 = @column2

FROM TABLENAME A inner join table name b on a.x = b.x  and a.y<>b.y  WHERE COLUMN3 = @Column3

end

Execution command in OLE DB sql statement:

exec dbo.MyUpdateSt ?,?,?

---

http://www.rad.pasfu.com

Answer 6

thats one way of doing it, what i  did was hod the changes in a temp table and run update in the controle flow.

thanks all for helping.