I hope someone can help.
I'm trying to read rows from a SQL Server Table and for each row use a few columns as parameters into a query to be run against oracle which will delete oracle rows.
I add OLDEB connections for Oracle and SQL and then I try to add a "Execute SQL Task". I've also tried a "OLE Command" but I can't get the mapping of the columns to the parameters to work.
There is lots of articles on the web that talk in general around parameterized queries but no clear examples.
I also find the difference between the Control Flow and Data Flow tabs confusing as its not intuitive where to place things. It also appears to force me to re-define things that it should already know (this is no doubt because I'm interpreting what I've done / acheived wrongly).
I have my source and destination on the "Data Flow" tab along with a "Execute SQL Task" object in the middle.
I'm setting its "connection manager" the Oracle (i.e. the destination where I want the deletes to be executed). I don't follow why this also has a "connection property, surely this it set when I drag the output of the SQL Server OLEDB Source to the input of the "Execute SQL Task".
Perhaps I'm expected too much from the wizards / dialogs and I have to create "variables" and "parameters" myself?
Any help or suggestions would be very much appreciated.
Thanks in advance
Craig
Scotland
Craig,
In general, you can think in the control flow as the one responsible for the workflow of the package; it could be also use to perform batch operations against a table. In you case, for example you could issue a delete statement using an execute sql task against the Oracle table to delete all rows at once. In order to do so, you would need to have a staging table that holds the rows that need to be delete.
The DataFlow; is deemed to move data and perform operations in a row-by-row basis. You could solve your problem by using an OLE DB command transformation to perform the delete. The drawback with this approach, it is that the delete statement would be performed for every row in the dataflow pipeline; so the performance is affected considerably.
If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction.
In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement.
I hope this helps you
|||Thanks,
Some comments.
"If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction."
One table exists in SQL Server, then other in Oracle, does you comment still apply?
"In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement."
MUST I do this. This does not appear to me to be in the nature of a parameterized query? Would this method still use "Prepared Statements" ?
Lastly, I'm still stuck in that my real issue is on how to map input data (from SQL) to parameters (to Oracle), I think if I followed your example, I'd still have that same problem only now I would be trying to map to a variable that was my entire query instead of just a parameter?
Thanks for the help so far and best regards
Craig
|||Can you create and populate a staging table in the Oracle side? If so, you could load all the data in the SQL server table into a staging table in the Oracle side and then use an execute sql task to perform a 1 time update. This is the way I do this kind of things because it's more efficient performance wise.
If that is not possible; I guess you have to stick with the data flow/OLE DB command approach. But I cannot help with that as I don't have an Oracle instance to test how the parameters get mapped.
