• Ok, here's the basis of my workaround.

    I created a script task to replace the Update Parameters data flow task. I also wrote a custom SQL function that parses the parameter values for me, although with a script task, this part could have also been done in vb.net.

    In the script, fill a datatable (you'll need to add System.Xml as a reference) with the results of the original select statement from dbo.ExecutionLogs. Then loop through the rows of the datatable and pass the parameters column to a parse function, which should return the Name and Value column. Once you have the logID, name, and value, send those to another function which will write them to the ExecutionParameters database.

    The concept is pretty typical of ADO.NET, it's just a shame that I had to resort to this. But even with my enormous volume of parameters, the script only took about 13 minutes to run, and it ran very smoothly.

    One note, I'm sure there are better ways to do it, but I ended up putting the connection string for my SQLConnection in a variable, because I couldn't figure out how to get the conn. string from a connection manager. (OLE DB connection uses the "Provider" syntax which throws an error from ADO, and the ADO connection needs Windows Authentication, which I can't provide b/c I'm running from scheduled batch)

    Hope this helps...

    Here's the code for the script, and below that is the code for the SQL function. I make no warranties for this code, but it works for me.

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    '--Notes--

    ' - Must add System.XML as a reference!

    ' - There are various ways to get connections/connection strings. I set this script

    ' to use a variable called "connString" that contains the connection string and

    ' is made available to this script in the task properties (ReadOnlyVariables).

    ' - This script takes the place of the "Update Parameters" data flow task. It does the

    ' exact same thing, only better!

    ' - There should now be no limit to the length of parameters in a report. Of course, the datatype

    ' of the column that is being written to could be a limitation.

    '--End Notes--

    Public Sub Main()

    Dim cnn As New SqlConnection(Dts.Variables.Item("connString").Value.ToString())

    Dim cmd As New SqlCommand

    cmd.Connection = cnn

    'Same query as data flow task. Get the raw parameter data from dbo.ExecutionLogs

    cmd.CommandText = "Select ExecutionLogID, Parameters from dbo.ExecutionLogs where Parameters IS NOT NULL and DATALENGTH(Parameters) > 0"

    cmd.CommandType = CommandType.Text

    Dim da As New SqlDataAdapter

    Dim dt As New DataTable

    Try

    cnn.Open()

    da.SelectCommand = cmd

    'Fill a datatable with the selected data so we can parse it with a datareader.

    da.Fill(dt)

    cnn.Close()

    Catch

    Throw

    Finally

    If Not cnn.State = ConnectionState.Closed Then

    cnn.Close()

    End If

    End Try

    Dim cmd2 As New SqlCommand

    cmd2.Connection = cnn

    cmd2.CommandType = CommandType.Text

    'Call custom function that will return two columns of the parsed values.

    cmd2.CommandText = "Select [Name], [Value] from fnParse_Parameters(@str)"

    Try

    cnn.Open()

    Dim ExecutionLogID As Guid

    Dim name As String

    Dim value As String

    Dim dr As DataRow

    'Loop through the rows and parse get the name and values

    If dt.Rows.Count > 0 Then

    For Each dr In dt.Rows

    cmd2.Parameters.Clear()

    cmd2.Parameters.AddWithValue("@str", Trim(dr.Item("Parameters").ToString()))

    ExecutionLogID = CType(dr.Item("ExecutionLogID"), Guid)

    Dim dr2 As SqlDataReader = cmd2.ExecuteReader()

    While dr2.Read()

    If dr2.HasRows = True Then

    name = Trim(dr2.Item("Name").ToString())

    value = Trim(dr2.Item("Value").ToString())

    'Write the values to dbo.ExecutionParameters

    WriteValues(ExecutionLogID, name, value)

    End If

    End While

    dr2.Close()

    'Added dr2.Close() because I kept getting an error that it needed to be closed.

    'I guess that the 'End While' doesn't "close" the datareader, although I never

    'seem to need to do this in a normal VB app.

    Next

    End If

    cnn.Close()

    Catch

    Throw

    Finally

    If Not cnn.State = ConnectionState.Closed Then

    cnn.Close()

    End If

    End Try

    'Return Success. Don't handle any failures because you want any exception to bubble up.

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Private Sub WriteValues(ByVal ExecutionLogID As Guid, ByVal Name As String, ByVal Value As String)

    Dim cnn As New SqlConnection(Dts.Variables.Item("connString").Value.ToString())

    Dim cmd As New SqlCommand

    cmd.Connection = cnn

    cmd.CommandType = CommandType.Text

    cmd.CommandText = "INSERT INTO [dbo].[ExecutionParameters] ([Name],[Value],[ExecutionLogID]) VALUES " & _

    "(@Name, @Value, @ExecutionLogID)"

    cmd.Parameters.AddWithValue("@Name", Name)

    cmd.Parameters.AddWithValue("@Value", Value)

    cmd.Parameters.AddWithValue("@ExecutionLogID", ExecutionLogID)

    Try

    cnn.Open()

    cmd.ExecuteNonQuery()

    cnn.Close()

    Catch

    Throw

    'MsgBox("Error: " & ex.Message)

    Finally

    If Not cnn.State = ConnectionState.Closed Then

    cnn.Close()

    End If

    End Try

    End Sub

    End Class

    Here's the SQL Function:

    GO

    /****** Object: UserDefinedFunction [dbo].[fnParse_Parameters] Script Date: 03/31/2008 16:25:30 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[fnParse_Parameters]

    (@str nvarchar(MAX))

    RETURNS @Values TABLE ([Name] nvarchar(MAX), [Value] nvarchar(MAX))AS

    BEGIN

    DECLARE @Delim char(1)

    SET @Delim = '&'

    DECLARE @Delim2 char(1)

    SET @Delim2 = '='

    DECLARE @chrind INT

    DECLARE @chrind2 INT

    DECLARE @Piece nvarchar(MAX)

    DECLARE @Name nvarchar(MAX)

    DECLARE @Value nvarchar(MAX)

    SET @chrind = 1

    WHILE @chrind > 0

    BEGIN

    SELECT @chrind = CHARINDEX(@Delim,@str)

    IF @chrind > 0

    Begin

    SET @Piece = LEFT(@str,@chrind - 1)

    End

    ELSE

    Begin

    SET @Piece = @STR

    End

    SET @chrind2 = CHARINDEX(@Delim2,@Piece)

    IF @chrind2 > 0

    Begin

    SET @Name = LEFT(@Piece,@chrind2 - 1)

    SET @Value = RIGHT(@Piece, LEN(@Piece) - @chrind2)

    End

    ELSE

    Begin

    SET @Name = @Piece

    SET @Value = @Piece

    End

    INSERT @Values([Name],[Value]) VALUES(@Name, @Value)

    SET @STR = RIGHT(@str,LEN(@str) - @chrind)

    IF LEN(@str) = 0 BREAK

    END

    RETURN

    END