How can I execute OLEDB Command without Result Set

Question

How can I execute OLEDB Command without Result Set

Owen White

Ten Centuries

Points: 1250
More actions
January 20, 2022 at 10:41 pm

#3975415
I am working with an application that has many INSERT and UPDATE stored procedures for our tables. I am migrating data from one version of our system to another version using a formatted text file. The text file has columns that I can map to the parameters of a stored procedure using an OLEDB Command. The problem is that the stored procedure is just updating the data in a table and there is no result set that is returned.
For example
```
CREATE PROCEDURE dbo.spUpdateTable
@ID int
,@FieldName varchar(10)
AS
BEGIN
UPDATE dbo.TableName 
SET FieldName = @FieldName
WHERE ID = @ID
END
```
I want to call this stored procedure from an SSIS OLEDB Command object. Before the suggestion of adding a return value to the stored procedure, I have over 100 stored procedures that I want to call. The reasoning is that the stored procedures are called from our application and I want to use the business logic located inside of these stored procedures rather than recoding everything again in SSIS via a SQL script. Also, many of the tables have over 50 fields that require updates.
I am hoping someone has a logic solution to calling stored procedures in SSIS that do not return values.
Thank you in advance to anyone who can provide me the answer!
Owen White

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

The fact that you wish to use an OLEDBCommand suggests that you wish to put this in a data flow, is that correct?

I presume that you realise the impact of this ... any proc would be called for every row in the file being imported, which is a performance killer.

A more performant solution would likely be to import the text file to a staging table and then to run your various procs against the imported data. Doing it this way would mean that you can call the various procs from ExecSQL tasks and your output resultset issue goes away.Is there a reason you have decided against this approach?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Owen White Ten Centuries Points: 1250 More actions · Answer 2

Phil,

Thank you for the reply. Unfortunately, a ExecSQL Task will not work for our scenario. I do understand the RBAR process that we are doing as time is not an issue with the processing of data.

To give all of the information, here is the setup (this currently is not an automated process but we are working towards a hands-off solution):

We receive a text file (.csv) from a customer
We launch the SSIS project
1. Load the text file into a staging table
2. Data flow task loops through each record in the staging table
  1. Source is the staging table
  2. Derived column object has variables that I need to pass to the stored procedures
  3. Lookup object to see if the current record exists in the destination table
    1. if the "matching" record does not exist then create the record by calling a dbo.spTableName_INSERT(param1, param2, param3,...) stored procedure
      1. Result Set is the new primary key value of newly inserted record - dbo.TableName.ID
    2. If the matching record does exist then update the record by calling a dbo.spTableName_UPDATE(param1, param2, param3,...) stored procedure
      1. THERE IS NO RESULT SET SINCE THERE IS NOTHING TO RETURN

The create code is complete because the INSERT stored procedures return the new primary key of the inserted record so this does have a result set. However, the UPDATE stored procedures do not return any result sets because it is just updating records and then verifying missing data into a validation table all behind the scenes with advanced business logic. I also have a Conditional Split object that executes the appropriate stored procedure based on the version of the record. Each record is stamped with a version number (1.0 or 2.0) for the data collection process since the various records could have different business logic based on version of the record.

I hope that you can help provide guidance on the proper way to design an ETL process since I am by no means an expert. I have been doing this a long time but perhaps I have been doing it the wrong way for a long time (like playing golf for a long time does not mean you are good at it since you may have been reinforcing bad habits for a long time). I am self-taught and will listen to any advice. I am just hoping I do not have to design the entire project that has over 100 tables in it.

Currently I do not see an easy approach to this process and looking for a magic SSIS DFT object that does not require a Script Task.

Owen White

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

I'm not an SSIS guru by any means, but won't an execute SQL task work?

One of the few SSIS packages I've had to work with does a nightly ETL. There are numerous execute SQL tasks that truncate tables and execute procs do updates and inserts. None of those return a result set.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Owen White Ten Centuries Points: 1250 More actions · Answer 4

Michael,

Unfortunately, I am not able to purge the data prior to loading. This must be a RBAR - find and create or update based on the existence of the record. I agree with you that this would be great if I could do it this way. I have two different files that feed data to the same record. If I were to delete the records prior to loading the csv file then half of the data elements would have missing column values. It looks as though I am stuck with recoding the update code based on the fact that no one has offered a solution to my problem. I appreciate your comments.

Owen

Owen White

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

It seems to me you are making it much more complex than needed. As you have stated, you need to cursor over the data and determine insert vs update - and then perform that action.

So why not do that in SQL Server?

The SSIS portion would be to get the data into the staging table, then call an execute SQL task. That task could be setup to call a stored procedure - which has the logic to identify whether it is an insert or update.

I am assuming that the insert/update procedures you are calling have specific business logic. If not - then it really doesn't make sense to use those procedures. If the volume is a problem then batch the inserts...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Owen White wrote:

I have over 100 stored procedures that I want to call.

You using regurgitated OLTP procs because you don't want to or won't make the time to do it right with set-based technology. Basically, you're stuck. OLTP and batch processing are two very different animals and when you try to use the former for the latter, you end up with a chimera that will eat your CPUs and I/O and processing time, etc, etc.

If you insist on using the existing "100 stored procedures", all of which I imagine have RBAR OLTP roots (and probably call each other several layers deep), then you're going to be stuck with the chimera solution.

The good thing is, you could pipe the excessive heat coming from the CPUs to warm the rest of the building in this unusually cold winter we're having. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Owen White Ten Centuries Points: 1250 More actions · Answer 7

Jeff M,

I can't stop laughing at your response. You are correct that I am trying to leverage previous work done in the stored procedures instead of rewriting the logic again. The problem is that I live in Florida and it is 75 degrees along with all of the crazy people here. So there is nothing that will cool my CPU down and it tends to overheat from all of the cached data in my SSIS packages. I think you hit the nail on head that I am leveraging ETL for OLTP purposes. I am very familiar with set based updates and I need to revisit my strategy and make some executive decisions as this is costing more and more money every day. Thank you.

Owen White

Owen White Ten Centuries Points: 1250 More actions · Answer 8

Jeff W,

You have provided a very strong argument for rewriting this in SQL code itself. Based on all of the feedback, I think no matter what I do I am going to have to rewrite this process. I am going to look at your strategy and see what I can come up with. I dread the creation and mapping of all of the variables to pass to the various stored procedures. Although I may abandon the stored procedure call and just do a set based import and then validate the records after they have been imported instead of validating one by one on insert and update. I appreciate your time and perspective on this project.

Owen White

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Owen White wrote:

Jeff M,
I can't stop laughing at your response. You are correct that I am trying to leverage previous work done in the stored procedures instead of rewriting the logic again. The problem is that I live in Florida and it is 75 degrees along with all of the crazy people here. So there is nothing that will cool my CPU down and it tends to overheat from all of the cached data in my SSIS packages. I think you hit the nail on head that I am leveraging ETL for OLTP purposes. I am very familiar with set based updates and I need to revisit my strategy and make some executive decisions as this is costing more and more money every day. Thank you.

BWAAA-HAAA!!! I lived on Key West for 26 months in the 70's while I was in the U.S. Navy so I can certainly identify the humor there. The first time I went skin diving there, I thought my mask had a leak in it because I kept getting salty water in my eyes. It wasn't a leak... it was sweat... while I was swimming in the water! Fond memories of "love bugs" (ton's of fun while riding a motorcycle at highway speeds) and sweating.

About 15 years ago, I went to Disney world with my parents and the shuttle driver was funny as hell but he was totally correct. He said that the one thing that was guaranteed to happen to us in Florida is... sweat. 😀 And, sweat we did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Owen White Ten Centuries Points: 1250 More actions · Answer 10

Nothing like going to the beach in the middle of the summer to cool off only to find out that the water is almost 80 degrees! Not the cool off I was hoping for. Thanks again for your insight and laughs! Thank you also for your military service. I have many relatives who served in the military and one of which was "almost" a Navy Seal. He claims he broke his arm during the training but I think he just rang the bell and needed some story to tell why he did not make it.

I hope our paths cross again,

Best regards

Owen White