Confusing layout in SSIS with regard to "Execute SQL Task".

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.

Conflicting Members

I have a report that includes a set, customer divisions as columns and products as rows. At certain intersections members are filtered by the same attribute (ie customer is filtered by product flag as is the product). Some of these have conflicting attribute values (ie product flag is true for the customer but false for the product). In these cases the customer setting is taking precedence. However I need the member setting for the product to override the customer. Is there a way of achieving this?

From your description it is not clear to me what your issue is. Axes are resolved independently, so there could be no conflicts in filtering customers and products. And if you refer to cell values, then what does filtering means ? It would be best if you will provide the exact MDX you are using and results you want to get, then this forum will be able to help you with MDX modifications.|||

In response to your question I am referring to cell values. My MDX query is basically SELECT [Customer Set] on Columns, [Product Set] on rows. I have members in my customer set and my product set which are filtered by the same attribute (Product Flag):

Product Member = ([Product - Own Keg Total] , [Product].[Product Flag].[TRUE])

Customer Member = ([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Customer].[Product Flag].[FALSE])

The values results returned indicate that the attribute value for the product flag of the customer member has been used.

Hope this makes it clearer. If not let me know. Thanks.

|||Can you please provide a definition of named sets [Customer Set] and [Product Set]. I am still not sure what you meant by Product Member and Customer Member in your response, but one thing contradicted what you said about same attribute (Product Flag) being used - in your tuple expression once you used attribute in the Product dimension and once in Customer dimension, so it cannot be the same attribute.|||

Sorry to add to the confusion. The customer member should have read:

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE])

Ignoring sets, if I run the query with just the product member on the row and the customer member on the column I get the same result:

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True])

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE])

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

|||

Thanks - it is clear now. The mechanism to control order of conflict resolution on calculated members is through SOLVE_ORDER property. I.e. in the following query Customer Test has higher solve order, therefore the Product Flag=True would win

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True]), SOLVE_ORDER=1

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE]), SOLVE_ORDER=2

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

And in the following query, the SOLVE_ORDER is reversed, therefore Product Flag=False will win.

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True]), SOLVE_ORDER=2

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE]), SOLVE_ORDER=1

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

|||Is there a way of configuring the query so that where there is a conflict neither member wins and nothing is returned?

Conflict

Hello
How can i delete the conflict tables rows? any SP command is there?
Sam
Sam,
there is a stored proc : sp_deletemergeconflictrow
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)