在 SSIS 包中保留一个变量值 [英] Persist a variable value in SSIS package

问题描述

我有一个包含脚本任务的包.我通过编辑脚本来扩展它.该包定期运行，我需要在变量中保留一个日期值.该值是最后一次运行包的时间.该包从 SQL Server 代理运行.我已将该变量放在包的配置 xml 中.一旦我的包从脚本任务运行，我想更新 xml 中的值.

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 last time the package was run. The package is run from the SQL server agent. I have put the variable in the configuration xml for the package. I want to update the value in the xml once my package is run from the script task.

我写了这样的代码

Dts.Variables["lastRunDate"].Properties["Value"].SetValue(Dts.Variables["lastRunDate"], DateTime.Now.ToString("yyyy-MM-dd");

程序运行，我只是不确定 SetValue 函数的第一个参数的正确值是多少.文档没有多大帮助.脚本运行但未使用新值更新 xml 配置文件.

The program runs, I am just not sure, what is the correct value for SetValue function's first parameter. the documentation is not much of a help. The script runs but the xml configuration file is not updated with the new value.

更新:我可以将值保存到注册表中.我不确定这里的最佳实践！！！它在 SQL Server 下部署和运行包的方式存在安全问题.:(

Update : I could save the value to the registry. I am not sure of the best practice here !!! It has security issues with the way packages are deployed and run under SQL server. :(

有人可以帮忙吗?

推荐答案

既然您通过代理运行包，难道您不能通过 msdb.dbo.sysjob% 表的某种组合来获取上次运行日期吗?

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;

这篇关于在 SSIS 包中保留一个变量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！