sql-server - 如何检查 SSIS 包是否连接到服务器
问题描述
我是SSIS的新手,我有几个问题请帮助我。
- 如何检查哪个解决方案连接到服务器以运行包,因为作业正在拉的包不起作用,我已经在 prod 服务器中运行了包,并且工作正常。
- 如何检查从服务器选择的 ISPAC 文件/
谢谢
解决方案
此查询将为您提供服务器上运行的所有包的包和作业名称。
;WITH CTE1 AS (
SELECT
J.job_id
,JobName = J.name
,JS.step_id, JS.step_name, JS.command
,StartIndex =
CASE
WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%' OR JS.command LIKE '/ISSERVER%' THEN CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) --'
WHEN JS.command LIKE '/SERVER%' THEN CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1
ELSE 0
END
,EndIndex =
CASE
WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%' OR JS.command LIKE '/ISSERVER%'
THEN CHARINDEX('"',JS.command, CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1)) --'
- CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) - 1 --'
WHEN JS.command LIKE '/SERVER%'
THEN CHARINDEX('"',command, CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1)
- CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) - 1
ELSE 0
END
FROM msdb.dbo.sysjobsteps JS
INNER JOIN msdb.dbo.sysjobs J
ON JS.job_id = J.job_id
WHERE JS.subsystem = 'SSIS'
)
SELECT
C1.job_id
, C1.JobName
, C1.step_id
, C1.step_name
, PackageFolderPath =
CASE
WHEN C1.command LIKE '/DTS%' OR C1.command LIKE '/ISSERVER%' THEN
SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
WHEN C1.command LIKE '/SQL%' THEN '\MSDB' + SUBSTRING(C1.command,
C1.StartIndex, C1.EndIndex)
WHEN C1.command LIKE '/SERVER%' THEN '\MSDB\' + SUBSTRING(C1.command,
C1.StartIndex, C1.EndIndex)
ELSE NULL
END
, C1.command
FROM CTE1 C1
ORDER BY C1.job_id, C1.step_id
推荐阅读
- firebase - Firebase 托管找不到我的 CSS
- javascript - Using a value to compare against an array of objects to return a different value within that object?
- kotlin - Does Kotlin support passng a custom function as a variable in another function?
- android - 为什么 altbeacon 库只检测少量信标
- r - nCr 中所有可能的 r 值的组合
- tensorflow - 集成两个张量流模型
- ruby-on-rails - FactoryBot 和 Devise 用户在使用 ajax 表单的测试期间无法登录
- python-3.x - 神经网络回归
- android - 警报对话框视图设置不正确
- reactjs - 如何测试在该组件中呈现 HOC 或模拟 HOC 的组件?