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