How to get SQL JobStep connection strings

2019-08-03 09:00发布

问题:

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:

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;

回答1:

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';

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.

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.