0

I am working on a SSIS package which checks the performance of stored procedures using extended events. We have an execute SQL task having a SQL query which is determined at run time as the arguments for those (Read) stored procedures are already hard coded and stored in a table.

Now, I need to run (write) stored procedures calls as well for which I can not hard code the arguments as they are (dynamically generated) dependent on output set of the previous stored procedures. Arguments and result set are not identical for each stored procedure and so the difficulty to use a single execute SQL task.

I thought of creating separate execute SQL task for each (write) stored procedure and add them to sequence container but that doesn't seem like a practical approach.

Can somebody suggest any approach to this problem?

TT.
  • 15,774
  • 6
  • 47
  • 88
Nikhil Nilawar
  • 1
  • 2
  • If the stored procedures have different parameters then you have to do one execute SQL Task per stored procedure. You're going down the path of "code generated code" which _seems_ like a good idea but it usually isn't – Nick.Mc Nov 05 '18 at 06:19
  • As the ssis package is meant to automate the performance testing, modifying the package every time we add new write procedures does not seem to be a good idea. I am thinking of creating a sql script and executing it via a ssis task. That way I can add/update the procedures to the sql script and not to ssis package. Also i am thinking of starting and stopping the extended event before and after each procedure and catch the write/cpu/read stats. Is this a good idea? – Nikhil Nilawar Nov 05 '18 at 06:28
  • I can tell you that using SSIS to do things for objects with changing metadata is a bad idea. Does it have to be bound up in a test harness or can you just run it then use the query store? https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017 – Nick.Mc Nov 05 '18 at 06:35
  • Another option for data driven code is BIML which lets you build packages on the fly based on data – Nick.Mc Nov 05 '18 at 06:35
  • I've used a variable with an Execute SQL Task to accomplish something similar. You can dynamically set what that variable is at run-time, you would use SQLSourceType=Variable when setting up the SQL Task. How you then set that variable at run-time is up to you and what you are specifically trying to accomplish. A couple options would be another proc, could be data driven(list of procs stored in a table), a script task, etc. – Tim Mylott Nov 05 '18 at 16:13
  • Can you provide some examples of your source data and how you think those would work in your dynamic procedures? – billinkc Nov 05 '18 at 20:11
  • @billinkc I have a CreateAppointment SP which creates a appointment ID and the it is used as an argument for createInvoice SP. There are several SPs' with different different argument requirements. They are to be executed in one order though. – Nikhil Nilawar Nov 06 '18 at 06:49

0 Answers0