|
|
You're in the
sql server dts
group:Best practices w.r.t. use of Stored Procs in SSIS
sql server dts:
I need to know whether it is good to use a Stored Procedure in the Execute SQL Task inside a SSIS package or whether the functionality of the SP should be emulated using the SSIS tasks and transformations. We have some DTS packages which are calling a Stored Procedure to do all the transformations, INSERTs, DELETEs and UPDATEs. Should we go ahead and do the same thing in SSIS (we are migrating to SQL Server 2005) or emulate the behavior of the Stored Procedure using the SSIS Tasks and Transformations? Thanks in advance.
Hello B@ns:
You might as well have asked "What kind of computer should I buy?" Answer: It depends on what you will be doing with it. The answer to your question kind of depends on what exactly you are doing in the SP. If it is a simple "INSERT INTO <Table 1> ... SELECT ... FROM <Table 2> then I would probably stick with a stored procedure. But if you are pulling from one Database/server to another, PLUS converting data, PLUS have a lot of *IF* statements to control the flow of the SP, then I would go with SSIS. I have seen a simple SP beat an SSIS package by a magnitude of 10 times faster. But you can't possible manage some of the Control Flow complexities in a stored procedure. Nor do you get the rich Configuration options, Error handling, logging, etc. Can you give us a taste of exactly what is going on the your SP? -- Todd C [quoted text, click to view] "B@ns" wrote:
> Hi, > > I need to know whether it is good to use a Stored Procedure in the Execute > SQL Task inside a SSIS package or whether the functionality of the SP should > be emulated using the SSIS tasks and transformations. > > We have some DTS packages which are calling a Stored Procedure to do all the > transformations, INSERTs, DELETEs and UPDATEs. > > Should we go ahead and do the same thing in SSIS (we are migrating to SQL > Server 2005) or emulate the behavior of the Stored Procedure using the SSIS > Tasks and Transformations? > > Thanks in advance. >
[quoted text, click to view]
Todd C wrote: One thing to note in SSIS - if your stored procedure uses temp tables to > Hello B@ns: > You might as well have asked "What kind of computer should I buy?" > Answer: It depends on what you will be doing with it. > > The answer to your question kind of depends on what exactly you are doing in > the SP. > > If it is a simple "INSERT INTO <Table 1> ... SELECT ... FROM <Table 2> then > I would probably stick with a stored procedure. > > But if you are pulling from one Database/server to another, PLUS converting > data, PLUS have a lot of *IF* statements to control the flow of the SP, then > I would go with SSIS. > > I have seen a simple SP beat an SSIS package by a magnitude of 10 times > faster. But you can't possible manage some of the Control Flow complexities > in a stored procedure. Nor do you get the rich Configuration options, Error > handling, logging, etc. > > Can you give us a taste of exactly what is going on the your SP? > > extract the data from the source system you will have problems.
Yes. Also, don't forget the PIPELINE and the non-blocking transfomtions that
you can use in the pipeline. Some of the packages I converted from DTS to SSIS have had their execution time cut in half since I was performing most of my updates using Variables and pushing the values into the derived column transformation. The pipeline allows you to reduce the amount of IO hits and speeds things up a bit. Will [quoted text, click to view] "Jeffrey Williams" <jeff.williams3188@verizon.net> wrote in message news:O170frn9HHA.1208@TK2MSFTNGP05.phx.gbl... > Todd C wrote: >> Hello B@ns: >> You might as well have asked "What kind of computer should I buy?" >> Answer: It depends on what you will be doing with it. >> >> The answer to your question kind of depends on what exactly you are doing >> in the SP. >> >> If it is a simple "INSERT INTO <Table 1> ... SELECT ... FROM <Table 2> >> then I would probably stick with a stored procedure. >> >> But if you are pulling from one Database/server to another, PLUS >> converting data, PLUS have a lot of *IF* statements to control the flow >> of the SP, then I would go with SSIS. >> >> I have seen a simple SP beat an SSIS package by a magnitude of 10 times >> faster. But you can't possible manage some of the Control Flow >> complexities in a stored procedure. Nor do you get the rich Configuration >> options, Error handling, logging, etc. >> >> Can you give us a taste of exactly what is going on the your SP? >> >> > One thing to note in SSIS - if your stored procedure uses temp tables to > extract the data from the source system you will have problems. > > Jeff
Hi Todd,
Thank you for your reply. I have 2 kinds of SPs: 1) Does a bulk insert into the "Load" tables in SQL Server 2005. Then inserts this data into the master tables using Joins. Here we Insert new records, Delete obsolete records and Update existing records. 2) The second type uses Cursors to browse through the recordset and sends mails based on some conditions. Regards, B@ns [quoted text, click to view] "Todd C" wrote:
> Hello B@ns: > You might as well have asked "What kind of computer should I buy?" > Answer: It depends on what you will be doing with it. > > The answer to your question kind of depends on what exactly you are doing in > the SP. > > If it is a simple "INSERT INTO <Table 1> ... SELECT ... FROM <Table 2> then > I would probably stick with a stored procedure. > > But if you are pulling from one Database/server to another, PLUS converting > data, PLUS have a lot of *IF* statements to control the flow of the SP, then > I would go with SSIS. > > I have seen a simple SP beat an SSIS package by a magnitude of 10 times > faster. But you can't possible manage some of the Control Flow complexities > in a stored procedure. Nor do you get the rich Configuration options, Error > handling, logging, etc. > > Can you give us a taste of exactly what is going on the your SP? > > -- > Todd C > > "B@ns" wrote: > > > Hi, > > > > I need to know whether it is good to use a Stored Procedure in the Execute > > SQL Task inside a SSIS package or whether the functionality of the SP should > > be emulated using the SSIS tasks and transformations. > > > > We have some DTS packages which are calling a Stored Procedure to do all the > > transformations, INSERTs, DELETEs and UPDATEs. > > > > Should we go ahead and do the same thing in SSIS (we are migrating to SQL > > Server 2005) or emulate the behavior of the Stored Procedure using the SSIS > > Tasks and Transformations? > > > > Thanks in advance. > >
Hi Jeff,
Thank you for the reply. One of my SPs is indeed using a temp table to extract data. It will be great if you could also state the problems associated with this. Does this mean that I should leave the SP as is or should I try to emulate this functionality in the SSIS package? Regards, B@ns. [quoted text, click to view] "Jeffrey Williams" wrote:
> Todd C wrote: > > Hello B@ns: > > You might as well have asked "What kind of computer should I buy?" > > Answer: It depends on what you will be doing with it. > > > > The answer to your question kind of depends on what exactly you are doing in > > the SP. > > > > If it is a simple "INSERT INTO <Table 1> ... SELECT ... FROM <Table 2> then > > I would probably stick with a stored procedure. > > > > But if you are pulling from one Database/server to another, PLUS converting > > data, PLUS have a lot of *IF* statements to control the flow of the SP, then > > I would go with SSIS. > > > > I have seen a simple SP beat an SSIS package by a magnitude of 10 times > > faster. But you can't possible manage some of the Control Flow complexities > > in a stored procedure. Nor do you get the rich Configuration options, Error > > handling, logging, etc. > > > > Can you give us a taste of exactly what is going on the your SP? > > > > > One thing to note in SSIS - if your stored procedure uses temp tables to > extract the data from the source system you will have problems. > > Jeff
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |