SSIS Execute SQL Task 传递上月首末日期参数调用Oracle存储过程
SSIS Execute SQL Task 调用带上月时间参数的Oracle存储过程实现流程
核心实现逻辑
完全替换掉原来硬编码写死固定日期的写法,无需每个月手动修改包内SQL的日期值,通过SSIS内置表达式自动计算上月首日、上月最后一天的日期值,再映射为存储过程的入参传入,实现任务运行时自动匹配对应业务周期。
分步实现流程
- 第一步:新建SSIS用户变量
在SSIS包的变量面板创建两个String类型的用户变量,命名保持直观即可,比如v_LastMonthFirstDay、v_LastMonthLastDay,暂时无需填写默认值,后续通过表达式自动赋值。 - 第二步:为变量配置自动计算的表达式
两个变量都勾选EvaluateAsExpression属性,分别填写对应的计算逻辑:v_LastMonthFirstDay(上月首日)的计算逻辑:取当前时间往前推1个月,截断到当月1号的时间点,转成通用日期字符串。对应表达式为:(DT_STR, 12, 1252) DATEADD("m", DATEDIFF("m", (DT_DBTIMESTAMP)"1900-01-01", DATEADD("m", -1, GETDATE())), (DT_DBTIMESTAMP)"1900-01-01")v_LastMonthLastDay(上月最后一天)的计算逻辑:取当前时间所在月的1号,往前减1天,即为上月的最后一天。对应表达式为:(DT_STR, 12, 1252) DATEADD("d", -1, DATEADD("m", DATEDIFF("m", (DT_DBTIMESTAMP)"1900-01-01", GETDATE()), (DT_DBTIMESTAMP)"1900-01-01"))
上述表达式转出的字符串为yyyy-MM-dd格式,相比原来的1-Jan-2021格式,不会因为月份缩写的语言配置问题出现解析错误。
- 第三步:改写Execute SQL Task内的SQL语句
原来的硬编码语句替换为带参数占位的版本,为规避Oracle隐式转换日期的问题,直接在语句中增加显式日期转换,无需依赖数据库会话的默认格式配置,改写后的SQL如下:
同时调整Execute SQL Task的基础属性:Connection项选择提前配置好的、能正常连通目标Oracle库的连接管理器,SQLSourceType选择Begin SP_TEST( TO_DATE(?, 'yyyy-mm-dd'), TO_DATE(?, 'yyyy-mm-dd') ); end;Direct Input,IsQueryStoreAsUnicode属性根据所用Oracle连接驱动的要求调整即可,常规OLE DB连接下选择True即可正常运行。 - 第四步:配置参数映射关系
打开Execute SQL Task的Parameter Mapping配置页,添加两个入参映射项:- 第一个映射:变量选择
User::v_LastMonthFirstDay,参数方向选Input,数据类型选VARCHAR,参数序号填0(OLE DB连接下参数从0开始计数,如果使用ODBC驱动连接Oracle,参数序号从1开始计数),参数大小填20 - 第二个映射:变量选择
User::v_LastMonthLastDay,参数方向选Input,数据类型选VARCHAR,参数序号填1,参数大小填20
- 第一个映射:变量选择
- 第五步:本地测试验证
直接在SSIS调试模式下运行该任务,到Oracle端查询存储过程的入参日志,确认传入的两个值是否对应当前执行月份的上月1号、上月最后一天,比如5月中旬执行任务时,传入值应为4月1号、4月30号,和预期一致即可部署到调度环境长期运行。
实操注意点
- 无需重复手动修改包配置:配完表达式后,无论哪个月份触发任务,都会自动基于执行时间计算上月周期,省掉人工改包的重复操作
- 规避日期解析歧义:不要直接传递依赖月份缩写的日期格式(比如原硬编码的
1-Jan-2021),万一Oracle会话的语言设置为中文,January的英文缩写会直接导致日期解析失败,使用yyyy-mm-dd通用格式配合TO_DATE转换的方式最稳妥 - 对齐时区配置:如果SSIS包部署在独立调度服务器上,要确认服务器系统时区、SSIS包时区设置、Oracle数据库会话时区三者保持一致,避免计算出的日期和业务要求的周期差一天
内容的提问来源于stack exchange,提问作者Sandeep
相关产品推荐
相关产品推荐

