Persist A Variable Value In SSIS Package

I have a package that contains a script task. I extent it by editing the script. The package runs periodically and I need to persist a date value in a variable. The value is the l

Solution 1:

Since you're running the package through the agent, couldn't you pull the last run date through some combination of the msdb.dbo.sysjob% tables?

msdb.dbo.sysjobactivity

SELECT  TOP 1 sja.start_execution_date
FROM    msdb.dbo.sysjobs sj
LEFT JOIN msdb.dbo.sysjobactivity sja
    ON  sj.job_id = sja.job_id
WHERE   sj.name = '<Agent Job Name>'
ORDER BY sja.start_execution_date DESC;

msdb.dbo.sysjobhistory

SELECT  TOP 1 run_date, run_time
FROM    msdb.dbo.sysjobs sj
LEFT JOIN msdb.dbo.sysjobsteps sjs
    ON  sj.job_id = sjs.job_id
LEFT JOIN msdb.dbo.sysjobhistory sjh
    ON  sjs.job_id = sjh.job_id
    AND sjs.step_id = sjh.step_id
WHERE   sj.name = '<Agent Job Name>'
    AND sjs.step_name = '<Job Step Name>'
ORDER BY sjh.run_date DESC, sjh.run_time DESC;

Solution 2:

You'll need a script task to directly manipulate the xml config file. I'm no guru on xml, so there may be a better way, but I can tell you how to append text to a file, so lacking a better solution, you could do this with Visual Basic.

Imports System.IO
Public Sub Main()

dim fileName as string
fileName = "C:\some\file\path\and\name.dtsconfig"
File.Delete(fileName)
File.AppendAllText(fileName, "<?xml version=" & chr(34) & "1.0" & chr(34) & "?>"
File.AppendAllText(fileName, "<DTSConfiguration><DTSConfigurationHeading></DTSConfigurationHeading>")
File.AppendAllText(fileName, "<Configuration ConfiguredType=" & chr(34) & "Property" & chr(34) & " Path = " & chr(34) & "\Package.Variables[User::lastRunDate].Properties[Value]" & chr(34) & "ValueType=" & chr(34) & "String" & chr(34) & ">")
File.AppendAllText(fileName, "<ConfiguredValue>" & DateTime.Now.ToString("yyyy-MM-dd") & "</ConfiguredValue></Configuration></DTSConfiguration>

End Sub