如何获取 SQL JobStep 连接字符串 [英] How to get SQL JobStep connection strings

问题描述

在我获得服务器作业并获得每个作业步骤后，我想获取与每个步骤相关的连接字符串，因为您可以在这样的作业中打开 SQL 管理工作室时找到它:

After I get the server jobs and I got each job steps I want to get the connection string related to each step as you could find it while opening SQL management studio in jobs like this:

是否有合适的方法通过 C# 代码获取每个包的连接字符串?

is there is a suitable way to get the connection strings for each package by C# code?

ServerConnection conn = new ServerConnection("localhost");
//new SqlConnection("data source=localhost;initial catalog=CPEInventory_20101122;integrated security=True;"));
Server server = new Server(conn);
JobCollection jobs = server.JobServer.Jobs;
var stepInformationsDetailsList = new List<StepInformationsDetails>();

foreach (Job job in jobs)
{
    foreach (JobStep jobstep in job.JobSteps)
    {
        stepInformationsDetailsList.Add(new StepInformationsDetails() {
           ServerName = job.Parent.MsxServerName,
           ReportName = job.Name,
           StepName = jobstep.Name,
           Command = jobstep.Command,
           Schedual = jobstep.DatabaseName,
           StepID = jobstep.ID
        });
    }
}
dataGridView1.DataSource = stepInformationsDetailsList;

推荐答案

这些数据都将在您的 Command 变量中.当您在作业步骤选项卡上覆盖/添加任何内容时，最终结果是传递给 dtexec 的命令行具有这些值.UI 只是从 msdb.dbo.sysjobsteps 表中的命令列中切出参数以将它们链接到各种选项卡.

That data will all be in your Command variable. When you override/add anything on the job step tabs, the net result is that the command line passed to dtexec has those values. The UI is simply slicing arguments out of the command column in the msdb.dbo.sysjobsteps table to link them to various tabs.

在你的本地主机上，这个查询应该返回完整的命令行.

On your localhost, this query ought to return back the full command line.

SELECT
    JS.command
FROM
    dbo.sysjobs AS SJ
    INNER JOIN dbo.sysjobsteps AS JS
    ON JS.job_id = SJ.job_id
WHERE
    sj.name = 'Concessions made by Simba Auto-Report';

由于您没有覆盖连接管理器Simba Replica..."的值，它不会显示在命令的输出中.如果是，您将有一个字符串，如 /SQL "\"\MyFolder\MyPackage\""/SERVER "\"localhost\sql2008r2\""/CONNECTION SYSDB;"\"Data Source=SQLDEV01\INT;Initial Catalog=SYSDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;\""/CHECKPOINTING OFF/REPORTING E/CONNECTION 部分将与您的Simba Replica..."相关联价值.

Since you are not overriding the value of the Connection Manager 'Simba Replica...', it is not going to show up in the output of the command. If it was, you'd have a string like /SQL "\"\MyFolder\MyPackage\"" /SERVER "\"localhost\sql2008r2\"" /CONNECTION SYSDB;"\"Data Source=SQLDEV01\INT;Initial Catalog=SYSDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;\"" /CHECKPOINTING OFF /REPORTING E The /CONNECTION section would correlate to your 'Simba Replica...' value.

我怀疑但不知道 UI 正在通过 API 检查 SSIS 包 Microsoft.SqlServer.Dts.Runtime 并通过 Connections 属性来构建该对话框.

I suspect, but do not know, that the UI is examining the SSIS package via the API Microsoft.SqlServer.Dts.Runtime and identifying all the Connection Manager, via Connections property to build out that dialog.

这篇关于如何获取 SQL JobStep 连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！