我有一个包含脚本任务的包.我通过编辑脚本来扩展它.该包定期运行,我需要在变量中保留一个日期值.该值是最后一次运行包的时间.该包从 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 包中保留一个变量值
发布评论