Pivoting data in SQL and SSIS Dynamically

There is a pviot function in SSIS and a pivot query in SQL - both of which I find laborious, ESPECIALLY the one in SSIS!
Here is a dynamic SQL query that will fix the problem:

DECLARE @columns NVARCHAR(2000)
SELECT  @columns = COALESCE(@columns + ',[' + [Column2] + ']', '[' + [Column2] + ']')
FROM    TableName
ORDER BY [Column2]

--print @columns
DECLARE @SqlQuery nvarchar(4000)
set @SqlQuery = 'select [Column1],' + @cols + ' into dbo.NewTable from (select [Column1],[Column2], [Column3] from TableName) p 
   PIVOT (MAX([Column3]) FOR [Column2] in (' + @columns + ') ) AS pvt order by [Column1]'

--print @SqlQuery
execute (@SqlQuery)

I recommend adding this as an 'Execute SQL Task' in your Control Flow - even if it means using a Staging Table. Will Allow for a change in columns and everything!

One more thing! Notice the 'into dbo.NewTable'. You'll need to drop that table before performing this task. This will allow for a change to the dynamic columns. Dynamic column queries such as this are great even if the columns are fixed.
I've just done one where there were 50 columns after the pivot and I don't have time to be defining and writing all that extra code! It is espcially irritating in SQL (before SQL Server 2012 - which I understand takes care of tasks such as this much better!).
Good luck.

Large Tables/Dimensions and the Modulo Algorithm in SSIS

While working in a limited memory environment I needed to be frugal with the amount of RAM a package would take up. To this end, I had a Slowly Changing Dimension that contained about a million records, which when running on a 32 machine would crash and run out of memory part-way through.

This also applied to large transformations, where although I didn't run out of memory, going to capacity greatly slowed the process down.

What I found to ease these troubles is the Modulo Algorithm. By using this to split the data, or in the case of the dimension to process smaller chunks, is that performance and in fact the ability to complete the processing at all was greatly improved!

Here's an example of how to implement it in a simple enough Slowly Changing Dimension:

Let's start with the data. A large dimension of changing size (new records can be added at anytime - especially with a customer, client or address dimension). Limited RAM (2GB per transaction) with an optimal and easily controlled 10,000 records processed with each iteration.

I will use a For Loop Container to loop through each iteration. To work out the number of iterations I will divide the number of records in the dimension by 10,000 and round this UP to the nearest integer.

We want to create a table in our database that will hold each iteration of records - I make this table on the fly each time, so I can use it for multiple packages and tables.

Drag an Execute SQL Task onto the DataFlow and set the ResultSet to 'None'. Set the SQL to the following:

If (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Modulo_Temp'))
BEGIN

DROP TABLE dbo.Modulo_Temp

END

ELSE

BEGIN

CREATE TABLE dbo.Modulo_Temp
(Field1 int,Field2 varchar(100))

END

The CREATE TABLE part of the query needs to match the structure of the relational table used to match to the existing dimension.

Create two variables called 'Iterations' and 'CurrentIteration' as below:

Drop a script task in to your Control Flow, add in the below SQL Statement, set the ResultSet to SingleRow and map the ResultSet to variable 'Iterations'.

select (count(ID) / 10000) + 1
from dbo.TableName

Next drop a For Loop Container into the Control Flow and set it up as below:

Here's a close up of the configuration:

Not the best picture quality so:
InitExpression : @CurrentIteration

EvalExpression : @CurrentIteration <= @Iterations

AssignExpression : @CurrentIteration = @CurrentIteration + 1


Next add an Execute SQL Task into the Loop and set the SQL to:

TRUNCATE TABLE dbo.Modulo_Temp

Now here's the fun part! We will now use the Modulo Algorithm to split the source table into chunks of 10k records. We will treat each 10k chunk as it's own dimension.

You can use the Slowly Changing Dimension task provided with Integration Services, but I prefer the Kimball method one available on CodePlex. It allows a lot more customisation, is faster and works well with this task as you can set your own dimension source using SQL, rather than just pointing to a table in the database. This facet is key as we need to restrict the dimension records to the ones contained within our 10k chunks of each iteration.

Drag a DataFlow component into the ForLoop.

In the DataFlow add an OLE DB Source. Add a variable called 'SourceSQL' with a data type of string. In the properties set 'Evaluate as Expression' to TRUE. In the expression write your select statement for your source table, but add in the Modulo part as below:

"SELECT
Field1,
Field2...
FROM dbo.SourceTable
WHERE (pID % " + (DT_WSTR,10)@[User::Iterations]  + ") =  " + (DT_WSTR,10)@[User::CurrentIteration]  + "
ORDER BY ID
OPTION(MAXDOP 1)"

Next we need to set our Existing Dimension source. Simply modify the code the below to meet your requirements.
/

select
Field1,
Field2...
from dbo.ExistingDimension
where YourBusinessKey in (select ID from dbo.Modulo_Temp)

This code will restict the records in the dimension to only those 10k you pulled through from the source code. Attach your Slowly Changing Dimension task to these two inputs as you normally would and BINGO!!

N.B. Do not use the Delete/Missing outputs from the Slowly Changing Dimension tasks as this will not work. I'll leave you to work out why!
/


Round up of Modulo:
Add (IDField % 5) to split table into 5 chunks. On to this add what part of the table you want like so: (IDField % 5) = 0. It uses zero based index for the chunks.

I have also seen this technique used to greatly improve the speed of large imports from SQL. Create 3 OLE DB Sources in one DataFlow, each one taking a third of the table to be imported and union them together. Much faster than one source.

I know this post is quite brief, and skips a few things, but it meant for basic guidance rather than instructions. Always experiement and play around.

Dynamic CREATE TABLE scripts

Not taking credit for this, but I really like the code and have not come across this sort of thing before.... Basically, if you want to generate CREATE TABLE scripts for multiple tables, or just on-the-fly this will save you....

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

While Loop - Cursor Alternative

I don't like cursors much. They're simple to write, easy to implement and good starting point for understanding loops. However, they are slow and memory-thirsty...(is that the right phrase?). So here is an example of using a simple WHILE loop which has the same behaviour as a cursor but it much faster and uses less memory than a cursor.

 DECLARE @RecordCount int
 DECLARE @RowCount int

 DECLARE @Field1 varchar(100)
 DECLARE @Field2 int
 DECLARE @Field3 char(2)

 select distinct IDENTITY(int,1,1) as ID, Field1, Field2, Field3into #loop from dbo.TableName order by ID DESC
 
 SET @RecordCount = (select max(ID) from #loop)
 SET @RowCount = 1

 WHILE @RowCount <= @RecordCount
 BEGIN
  
  select @Field1 = Field1, @Field2 = Field2, @Field3 = Field3 from #loop where ID = @RowCount

  EXECUTE [dbo].[usp_RandomCodeToReplace] 
    @Field1
   ,@Field2
   ,@Field3 

  SET @RowCount = @RowCount + 1

 END

 DROP TABLE #loop

Sequence number (Rank function) in SSIS

This example takes a table from SQL Server, orders the columns and creates a Rank (or sequence number). Works well and I've used it many times. First drag a new DataFlow into your package:

Next write your SQL statement to gather your rows. Pay attention to the ordering. WK is the field I will be ranking on and WeekID is the field I will be partitioning on.

Now we have to tell SSIS that we have ordered the way we have (this circumvents the need for a Sort component, which is slow and inefficient). This has to be done for the 'Error Output' too.

Next we tell the package what order the fields are ordered in... In this case WK is sort position 1 and WeekID is position 2.

Next drag a Script Component on to the DataFlow.

Select the fields you want to work with in the code.

Add an new column that will contain the rank numbers.

Next click on 'Design' to add your code.

Here is the code you need to add:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Dim WeekID As Integer

    Dim Counter As Integer = 0

    Dim PrevWeekID As Integer


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        WeekID = Row.weekID

        If WeekID = PrevWeekID Then
            Row.Rank = Counter + 1
            Counter = Counter + 1
        Else
            Row.Rank = 1
            Counter = 1
        End If

        PrevWeekID = Row.weekID

    End Sub

End Class

Rank and pick best record SQL

I used to use loops for this kind of thing. This is far more efficient.

WITH Table AS
( 
SELECT
    Field1, Field2, 
    Rank() OVER (PARTITION BY Field2 ORDER BY Field1 desc) AS Rank 
FROM dbo.TableName 
)
SELECT * from Table WHERE Rank = 1

;

Hope this helps.

Run an SSIS package on a remote server without SQL Agent

Situation: no SQL Agent, need to run SSIS package on a server remotely via a button-click event in a winforms app (C#).

This solution will run an SSIS package on a remote server while showing the progress in a multi-line textbox.  To do this effectively we will need to use a BackgroundWorker.

I used an executable called PsExec that is freely available to download.

To set up the logging, right click in your package ControlFlow and set up the logging to a SQL Server table.  This by default will be dbo.sysdtslog90 (for 2005 anyway; for 2008 it will be dbo.sysdtslog100 etc.).

PsExec allows for running command line routines on a remote server.  In this case I am calling a package through a command line routine using DtExec.  If you are unsure on DtExec please google it, I may do another post on it but it is pretty simple stuff and easy to work out.  PsExec simply allows you to pass a computer name that you would like to run the process on.

I start the PsExec, give it a computer name and pass in the Command Line routine to run a SSIS package.

The Background Worker then loops through the log table displaying the log results as the package runs.  Once an 'End of Package' log has been recorded the Background Worker stops. Simples.

If PsExec fails, or DtExec fails, you will also get an error message returned.  I was getting error code 128 for a while - it turned out the DBAs where remote desktoping on to the server and then not logging off, so there were too many users logged in.  Once I got them to log out all worked well.

private void RunSSISPackage()
        {
            try
            {
                SqlConnection conn = new SqlConnection(ConnectionsClass.Server);
                conn.Open();

                string TruncateSQL = "truncate table dbo.sysdtslog90";

                SqlCommand TruncateCMD = new SqlCommand(TruncateSQL, conn);

                TruncateCMD.ExecuteNonQuery();

                conn.Close();

                Process process = new Process();
                process.StartInfo.FileName = @"C:\Documents and Settings\Geoff.Parsons\My Documents\PsExec.exe";
                process.StartInfo.Arguments = "\\\\ServerName -d \"C:\\dtexec.exe\" /SQL PackageName /SERVER ServerName";
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.CreateNoWindow = true;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.RedirectStandardError = true;

                process.Start();
                process.WaitForExit();

                string strOutput = process.StandardOutput.ReadToEnd();
                string errOutput = process.StandardError.ReadToEnd();


                if (!process.HasExited)
                {
                    process.Kill();
                }

                PackageResultTB.AppendText(strOutput);
                PackageResultTB.AppendText(errOutput);

                if (errOutput != "")
                {
                    if (!errOutput.ToString().Contains("dtexec.exe started on SERVERNAME with process ID"))
                    {
                        return;
                    }
                }

                BW = new BackgroundWorker();
                BW.WorkerReportsProgress = true;
                BW.WorkerSupportsCancellation = false;
                BW.DoWork += new DoWorkEventHandler(BW_DoWork);
                BW.ProgressChanged += new ProgressChangedEventHandler(BW_ProgressChanged);
                BW.RunWorkerCompleted += new RunWorkerCompletedEventHandler(BW_RunWorkerCompleted);

                PrepareControlsForThreading(false);

                BW.RunWorkerAsync();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

This DoWork event loops through a select query to return new rows. I added a column to sysdtslog90 called 'Logged' which I populated each time a row was read to prevent repeating lines from the log table. On the front-end of the application I have a multi-line textbox called PackageResultTB.

private void BW_DoWork(object sender, DoWorkEventArgs e)
        {
            BW.ReportProgress(0, "Loading Package...");

            SqlConnection conn = new SqlConnection(ConnectionsClass.Server);
            conn.Open();

            bool Continue = true;
            while (Continue)
            {
                string SqlQuery = "select id,event,source,message,datacode, Logged from dbo.sysdtslog90 order by id";
                
                SqlCommand cmd = new SqlCommand(SqlQuery, conn);
                da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dt.PrimaryKey = new DataColumn[] { dt.Columns["id"] };

                string Update = "";
                

                foreach (DataRow row in dt.Rows)
                {
                    if (row["message"].ToString().Contains("End of package"))
                    {
                        Continue = false;
                    }
                    if (row["Logged"].ToString() == "")
                    {
                        BW.ReportProgress(0, "\r\n" + row["event"].ToString() + "\r\n        " + row["source"].ToString() + "\r\n        " + row["message"].ToString() + " - " + row["datacode"].ToString() + "%\r\n");
                        Update = string.Format("Update dbo.sysdtslog90 set Logged = 'TRUE' where id = '{0}'", row["id"].ToString());
                        UpCmd = new SqlCommand(Update, conn);
                        UpCmd.ExecuteNonQuery();
                    }

                }
                Thread.Sleep(1000);
            }
            conn.Close();
        }


private void BW_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            PackageResultTB.AppendText(e.UserState.ToString());
        }


private void BW_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            try
            {
                //Catch any errors
                if (e.Error != null)
                {
                    MessageBox.Show(e.Error.Message);
                    return;
                }
                else
                {
                    return;
                }
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

Basic Cursor and Loop usage with examples

This is a basic cursor, useful for smaller amounts of data.  It enables you to carry out a query against each record independantly before moving on to the next one. 

declare @Parameter1 varchar(50)

declare @Parameter2 varchar(50)

declare cur_cursor cursor for (select fieldname1,fieldname2
                                                           from server.[database].[dbo].[table with data])

open cur_cursor

Fetch Next from cur_cursor into @Parameter1, @Parameter2

While @@fetch_status = 0

begin

update table
set fieldname = @Parameter1
where otherfieldname = @Parameter2

Fetch Next from cur_cursor into @Parameter1, @Parameter2

end
close cur_cursor
deallocate cur_cursor

The following cursor uses dynamic SQL, which can allow you to add columns to a table, or deduce which columns you wish to query. 
declare @Parameter1 varchar(50)

declare @Parameter2 varchar(50)

declare @SQL nvarchar(4000)

declare cur_cursor cursor for (select fieldname1,fieldname2

                              from server.[database].[dbo].[table with data])

open cur_cursor

Fetch Next from cur_cursor into @Parameter1, @Parameter2

While @@fetch_status = 0

begin

set @SQL = 'update tablename

                set fieldname = ''test' + @Parameter2 + '''
           where otherfield = ''' + @Parameter1 + ''''

exec sp_execute @SQL

Fetch Next from cur_cursor into @Parameter1, @Parameter2

end

close cur_cursor
deallocate cur_cursor

There will be more to follow with more detailed explanations, examples of loops for larger datasets and nested loops, after the weekend.


Any questions let me know.

Data-Driven Subcriptions in Standard Edition of SQL Server

Recently I was asked to come up with a solution where we could emulate the usefulness of a data-driven subscription without paying out for the Enterprise edition of SQL Server.  This was the result.

The idea is to create an ‘empty’ standard subscription in SSRS and then, using a stored procedure, manipulate the parameters and run the report.
First create a dummy subscription that isn’t scheduled to run:

Set the parameters and delivery information as above.  For now we will use only one parameter, but the stored procedure can easily be changed to accommodate as many as required.

Once the subscription is saved, you need to get the SubscriptionID from the report server. Use the following code:

SELET

c.Name AS ReportName,   

rs.ScheduleID

FROM ReportServer.dbo.[Catalog] c

INNER JOIN ReportServer.dbo.Subscriptions s ON c.ItemID = s.Report_OID

INNER JOIN ReportServer.dbo.ReportSchedule rs ON c.ItemID = rs.ReportID

AND rs.SubscriptionID = s.SubscriptionID

Where

c.Name = 'Subscription Name'

You’ll need the SubscriptionID when calling the report.

Next create the following stored procedure that will be called to run the report(s):

(I found this SP on the internet, but can’t remember where so can’t credit where it’s due), either way it has a couple of changes and I hope it helps out other people)

USE [ReportServer]

GO

/****** Object:  StoredProcedure [dbo].[data_driven_subscription]    Script Date: 04/11/2012 16:38:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER   procedure [dbo].[data_driven_subscription_1_Param]

       ( @scheduleID uniqueidentifier,

         @TO varchar (2000) = ' ',

         @CC varchar (2000) = ' ',

         @BCC varchar (2000) = ' ',

         @ReplyTO varchar (2000) = ' ',

         @BODY varchar (8000) = ' ',

         @param1 varchar (256) = ' '

       )

as

DECLARE

       @ptrval binary(16),

       @PARAMptrval binary(16),

       @TOpos int,

       @CCpos int,

       @BCCpos int,

       @RTpos int,

       @BODYpos int,

       @PARAM1Pos int,

       @length int,

       @subscriptionID uniqueidentifier

       -- set the subscription ID
       SELECT @subscriptionID = SubscriptionID
       FROM ReportSchedule WHERE ScheduleID = @scheduleID
      
       -- set the text point for this records Email info
       SELECT @ptrval = TEXTPTR(ExtensionSettings)
       FROM Subscriptions WHERE SubscriptionID = @subscriptionID


       -- set the text point for this records Parameter info
       SELECT @PARAMptrval = TEXTPTR(Parameters)
       FROM Subscriptions WHERE SubscriptionID = @subscriptionID
      
              -- set the start position for the TO Address
              SELECT @TOpos = patindex('%|EmailTO|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID
             

                     IF isnull(@TOpos, '') <> '' and @TOpos > 0 and len(@To) > 0
                           -- change the TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                   @ptrval
                                  @TOpos
                                  4
                                  @To

              -- set the start position for the CC Address
              SELECT @CCpos = patindex('%|CarbonCopy|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID
             

                     IF isnull(@CCpos, '') <> '' and @CCpos > 0 and len(@CC) > 0
                           -- change the TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                  @ptrval
                                  @CCpos
                                  4
                                  @CC
      
              -- set the start position for the BCC Address
              SELECT @BCCpos = patindex('%|BlindCopy|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID
             

                     IF isnull(@BCCpos, '') <> '' and @BCCpos > 0 and len(@BCC) > 0
                           -- change the TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                  @ptrval
                                  @BCCpos
                                  4
                                  @BCC

              -- set the start position for the REPLY TO Address
              SELECT @RTpos = patindex('%|ReplyTo|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID
             

                     IF isnull(@RTpos, '') <> '' and @RTpos > 0 and len(@ReplyTO) > 0
                           -- change the REPLY TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                  @ptrval
                                  @RTpos
                                  4
                                  @ReplyTO

              -- set the start position for the BODY Text

              SELECT @BODYpos = patindex('%|Comment|%', ExtensionSettings) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

             

      

                     IF isnull(@BODYpos, '') <> '' and @BODYpos > 0 and len(@BODY) > 0

                           -- change the REPLY TO address

                           UPDATETEXT Subscriptions.ExtensionSettings

                                  @ptrval

                                  @BODYpos

                                  4

                                  @BODY

              -- set the start position for the Parameter 1

              SELECT @PARAM1Pos = patindex('%|Parameter1|%', Parameters) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

     

              IF isnull(@PARAM1Pos, '') <> '' and @PARAM1Pos > 0 and len(@parameter1) > 0

                           -- change the Parameter 1 value

                           UPDATETEXT Subscriptions.Parameters

                                  @PARAMptrval

                                  @PARAM1Pos

                                  4

                                  @parameter1

       -- run the job

       exec msdb..sp_start_job @job_name = @scheduleID

       -- this give the report server time to execute the job.

       -- there is probably a better way to do this, so let me know if you know it ...

       WAITFOR DELAY '00:00:10'

       -- now change everything back so you can run this again

              -- set the start position for the TO Address

              SELECT @TOpos = patindex('%' + @TO + '%', ExtensionSettings) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

             

              SELECT @length = len(@TO)

        IF @length > 0

                     -- replace the addresses with the original |TO|

                     UPDATETEXT Subscriptions.ExtensionSettings

                           @ptrval

                           @TOpos

                           @length

                           '|EmailTO|'

              -- set the start position for the TO Address

              SELECT @CCpos = patindex('%' + @CC + '%', ExtensionSettings) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

             

              SELECT @length = len(@CC)

        IF @length > 0

                     -- replace the addresses with the original |CC|

                     UPDATETEXT Subscriptions.ExtensionSettings

                           @ptrval

                           @CCpos

                           @length

                           '|CarbonCopy|'

              -- set the start position for the TO Address

              SELECT @BCCpos = patindex('%' + @BCC + '%', ExtensionSettings) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

             

              SELECT @length = len(@BCC)

        IF @length > 0

                     -- replace the addresses with the original |BC|

                     UPDATETEXT Subscriptions.ExtensionSettings

                           @ptrval

                           @BCCpos

                           @length

                           '|BlindCopy|'

              -- set the start position for the REPLY TO Address

              SELECT @RTpos = patindex('%' + @ReplyTO + '%', ExtensionSettings) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

             

              SELECT @length = len(@ReplyTO)

        IF @length > 0

                     -- replace the addresses with the original |RT|

                     UPDATETEXT Subscriptions.ExtensionSettings

                           @ptrval

                           @RTpos

                           @length

                           '|ReplyTo|'

              -- set the start position for the BODY Text

              SELECT @BODYpos = patindex('%' + @BODY + '%', ExtensionSettings) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

             

              SELECT @length = len(@BODY)

        IF @length > 0

                     -- replace the addresses with the original |BD|

                     UPDATETEXT Subscriptions.ExtensionSettings

                           @ptrval

                           @BODYpos

                           @length

                           '|Comment|'

              -- set the start position for the Parameter

              SELECT @PARAM1Pos = patindex('%' + @parameter1 + '%', Parameters) - 1

              FROM Subscriptions WHERE SubscriptionID = @subscriptionID

      

              SELECT @length = len(@param1)

        IF @length > 0

                     -- replace the addresses with the original |P1|

                     UPDATETEXT Subscriptions.Parameters

                           @PARAMptrval

                           @PARAM1Pos

                           @length

                           '|Parameter1|'

You’ll notice the name of the SP ends with ‘1_Param’.  To add more use the following code in the relevant part of the SP:

              -- set the start position for the Parameter 2

              SELECT @PARAM2Pos = patindex('%|Parameter2|%', Parameters) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID
      

             IF isnull(@PARAM2Pos, '') <> '' and @PARAM2Pos > 0 and len(@parameter2) > 0
                           -- change the Parameter 1 value
                           UPDATETEXT Subscriptions.Parameters
                                  @PARAMptrval
                                  @PARAM2Pos
                                  4
                                  @parameter2

                SELECT @length = len(@param2)
         IF @length > 0
                     -- replace the addresses with the original |P2|
                     UPDATETEXT Subscriptions.Parameters
                           @PARAMptrval
                           @PARAM1Pos
                           @length
                           '|Parameter2|'

It would be prudent to create a few versions of this SP with carrying numbers of parameters to meet all your needs.

The next SP is the one that runs and controls your new data-driven subscription capabilities.

declare @ReportParameter varchar(50)
declare @email_address varchar(255)
declare cur_cursor cursor for (select report_parameter,email_address

                                          from server.[database].[dbo].[tablewith data])

open cur_cursor
Fetch Next from cur_cursor into @ReportParameter, @email_address
While @@fetch_status = 0
begin

exec data_driven_subscription_1_Param
@scheduleID = 'FE98A2D4-4430-4D5C-806C-717470CB1332',
@TO = @email_address,
@CC = '',
@BCC = 'AdministrativeEmail@CompanyName.com',
@ReplyTO = 'RelevantTeam@YourCompany.com',
@BODY = 'Hello testing 123',
@param1 = @ReportParameter

Fetch Next from cur_cursor into @ReportParameter, @email_address
end
close cur_cursor
deallocate cur_cursor

This cursor uses a table directly, but you can create any complicated query you wish to determinethe parameters and emails, just as you would with a normal data-driven subscription.

Notice the @scheduleID – this is the scheduleID you collected after you saved the standard report subscription.

The above code can either be turned into an SP, or placed directly into a SQL Agent Job.

Once you have an Agent job to run this procedure you can schedule it when and as required.

Any questions please don’t hesitate to contact me.