July 16, 2012 at 1:03 pm
I have a large SSIS project with about 30 FOREACH Container Loops in it to execute numerous queries across all the databases in my environment using a dynamic connection. Each Container starts out with a Script Task that basically does nothing more than tell me what database is currently processing.
Public Sub Main()
Dim cm As ConnectionManager
Dim p As DtsProperty
cm = Dts.Connections("MultiServer")
p = cm.Properties("ServerName")
'MsgBox(p.GetValue(cm).ToString())
Dts.Events.FireInformation(1, "Processing DB - ", p.GetValue(cm).ToString(), "", 0, False)
Dts.TaskResult = ScriptResults.Success
End Sub
I want to add a stored procecdure call to this process to log the following:
ExecutionContainerName
Database
Status /* S - Start */
Timestamp
I'm currently having trouble capturing the NAME property of the FOREACH Container. If I can resolve this I can incorporate the stored procedure. I'm thinking its something on the order of the following:
Public Sub Main()
Dim cm As ConnectionManager
Dim p As DtsProperty
Dim FEL as ForEachLoop /* Microsoft.SqlServer.Dts.Runtime.ForEachLoop */
Dim FELValue as String
FELValue = FEL.Properties.Item("Name").ToString()
MSGBOX (FELValue)
/* Proposed EXECUTE [Server].[Database].[dbo].[ExecutionDurationLog] FEL.Properties.Item("Name").ToString(), p.GetValue(cm).ToString(), 'S', GetDate() */
cm = Dts.Connections("MultiServer")
p = cm.Properties("ServerName")
'MsgBox(p.GetValue(cm).ToString())
Dts.Events.FireInformation(1, "Processing DB - ", p.GetValue(cm).ToString(), "", 0, False)
Dts.TaskResult = ScriptResults.Success
End Sub
However, when I reference FEL.Properties.... I'm getting "Variable 'FEL' is used before it has been assigned a value. A null reference exception could result at runtime."
I don't want to hardcode the container NAME property string since this is an evolving project and managing all the hardcoded strings would be one more thing I have to remember to do as the project grows. Being able to access the container property NAME value programmatically would allow me to simply copy the Script Task into the new container without any modifications.
Has anyone ever tried to access the properties in this manner and can you please provide the technique you used to wire this all together?
July 16, 2012 at 3:47 pm
I would recommend a slightly different approach. Setup an Event Handler at the Package level for the OnPreExecute Event and add a Script Task to the Event Handler that will log information for you. Add the variable System::SourceName to the Script Task's ReadOnlyVariables collection. System::SourceName will contain the name of the object that fired the event each time it fires. If you institute a naming convention for all your ForEachLoop Containers (FELCs) then you can filter on that in the Script Task code so you only log the events you are interested in logging. This way you'll only have one Script Task for all current and future FELCs, and you'll have a mechanism to log other containers as well based on their name, if you choose to extend it that way in the future.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply