OLE DB Source using T-SQL or SQL Server Stored Procedure

Question

I'd like to know what the general consensus is on using straight T-SQL or a SQL Server Stored Procedure as a SQL command within a Data Flow Task and an OLE DB Source.

Our shop doesn't really have a standard on one as opposed to the other. I guess maybe what I'm looking for are advantages and disadvantages of both.

Personally, I'd prefer straight T-SQL within the SSIS Package. To me, it seems as though that's one less entity you have to worry about for maintaining...deploying...etc..

I of course welcome your thoughts and reasoning on the subject.

Thanks in advance for your input.

Answer 1

Hi @Bobby P ,

My 2 cents:

• Stored procedure (SP) is easier to create, modify, and test on its own.
• SP is easier to deploy across environments.
• SP could be under a source control. On the other hand, SSIS package impossible to compare in a source control because it is XML.

But for a simple SELECT statement it is ok to be used in an SSIS Source Adapter.

Answer 2

You will be much happier if you use SSIS for as little as possible.

If you embed queries in SSIS, they are impossible to test outside of SSIS, and if you need to change logic it is in the SSIS package.

SP are much easier to manage and source control, as long as they return the same values SSIS will work fine. If you add something to the return set, you will need to update the SSIS package to reflect those new values.

Answer 3

Hi @Bobby P ,

From my point, if the SQL command is simple or not frequently use in the package, I would like to use straight T-SQL.

If the command is a bit complex or need to be often using, I preferred to use SP.

Generally speaking, it depends on your own habits. Both of them have advantages and disadvantages.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.