January 17, 2017 at 5:56 am
I have jobs which have SSIS job steps. The basic information for the job step is in sysjobsteps, but what I want to find is the more detailed information in the tabbed items below the "Run as" line on the properties. I want to be able to search on Package, Configurations, Command files, Data sources, Execution options, etc.
Any thoughts as to where this information could be hiding in MSDB?
January 17, 2017 at 6:59 am
Brandie
I would guess they're taken from the command line, aren't they, and vice versa? So if you add an option or a configuration file or something, the command line is amended accordingly. Conversely, if you amend the command line manually, the options automagically appear in the various tabs.
John
January 17, 2017 at 7:30 am
John Mitchell-245523 - Tuesday, January 17, 2017 6:59 AMBrandieI would guess they're taken from the command line, aren't they, and vice versa? So if you add an option or a configuration file or something, the command line is amended accordingly. Conversely, if you amend the command line manually, the options automagically appear in the various tabs.
John
Data Sources isn't taken from the command line. And even if it were, how do I read the command line from SQL Server via T-SQL?
That's my issue. I need to query the table holding this information. Not open the job manually and look at it or enter it.
January 17, 2017 at 7:47 am
Brandie Tarvin - Tuesday, January 17, 2017 7:30 AMJohn Mitchell-245523 - Tuesday, January 17, 2017 6:59 AMBrandieI would guess they're taken from the command line, aren't they, and vice versa? So if you add an option or a configuration file or something, the command line is amended accordingly. Conversely, if you amend the command line manually, the options automagically appear in the various tabs.
John
Data Sources isn't taken from the command line. And even if it were, how do I read the command line from SQL Server via T-SQL?
That's my issue. I need to query the table holding this information. Not open the job manually and look at it or enter it.
If I add a data source through the GUI, it goes into the command line. I haven't checked whether the same is true the other way round.
Try this query. It returns the command line in the command column. This is for a file system deployment - I haven't tried it for any other type.SELECT
j.name
, s.step_name
, s.command
FROM msdb..sysjobsteps s
JOIN msdb..sysjobs j ON s.job_id = j.job_id
ORDER BY
j.name
, s.step_id
John
January 17, 2017 at 8:17 am
Brandie Tarvin - Tuesday, January 17, 2017 5:56 AMI have jobs which have SSIS job steps. The basic information for the job step is in sysjobsteps, but what I want to find is the more detailed information in the tabbed items below the "Run as" line on the properties. I want to be able to search on Package, Configurations, Command files, Data sources, Execution options, etc.
Any thoughts as to where this information could be hiding in MSDB?
Is this information not in the SSISDB rather than in MSDB?
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 17, 2017 at 9:17 am
John Mitchell-245523 - Tuesday, January 17, 2017 7:47 AMBrandie Tarvin - Tuesday, January 17, 2017 7:30 AMJohn Mitchell-245523 - Tuesday, January 17, 2017 6:59 AMBrandieI would guess they're taken from the command line, aren't they, and vice versa? So if you add an option or a configuration file or something, the command line is amended accordingly. Conversely, if you amend the command line manually, the options automagically appear in the various tabs.
John
Data Sources isn't taken from the command line. And even if it were, how do I read the command line from SQL Server via T-SQL?
That's my issue. I need to query the table holding this information. Not open the job manually and look at it or enter it.
If I add a data source through the GUI, it goes into the command line. I haven't checked whether the same is true the other way round.
Try this query. It returns the command line in the command column. This is for a file system deployment - I haven't tried it for any other type.
SELECT
j.name
, s.step_name
, s.command
FROM msdb..sysjobsteps s
JOIN msdb..sysjobs j ON s.job_id = j.job_id
ORDER BY
j.name
, s.step_idJohn
Looking at a few of mine, they're in that command field in sysjobsteps. Parsing it is going to be a nightmare though.
January 17, 2017 at 10:06 am
John Mitchell-245523 - Tuesday, January 17, 2017 7:47 AMBrandie Tarvin - Tuesday, January 17, 2017 7:30 AMJohn Mitchell-245523 - Tuesday, January 17, 2017 6:59 AMBrandieI would guess they're taken from the command line, aren't they, and vice versa? So if you add an option or a configuration file or something, the command line is amended accordingly. Conversely, if you amend the command line manually, the options automagically appear in the various tabs.
John
Data Sources isn't taken from the command line. And even if it were, how do I read the command line from SQL Server via T-SQL?
That's my issue. I need to query the table holding this information. Not open the job manually and look at it or enter it.
If I add a data source through the GUI, it goes into the command line. I haven't checked whether the same is true the other way round.
Try this query. It returns the command line in the command column. This is for a file system deployment - I haven't tried it for any other type.
SELECT
j.name
, s.step_name
, s.command
FROM msdb..sysjobsteps s
JOIN msdb..sysjobs j ON s.job_id = j.job_id
ORDER BY
j.name
, s.step_idJohn
Nope. It's not coming up with the job that I know has a data source pointing to database X.
EDIT: Interestingly enough, it comes up with other non-SSIS job steps when I change my search value for another database. But again, doesn't come up with the SSIS job step itself which is the one I'm trying to search.
SELECT j.name, s.step_name, s.command
FROM msdb..sysjobsteps s
JOIN msdb..sysjobs j ON s.job_id = j.job_id
WHERE s.command LIKE '%MyDB%'
ORDER BY j.name, s.step_id
January 17, 2017 at 10:12 am
ThomasRushton - Tuesday, January 17, 2017 8:17 AMBrandie Tarvin - Tuesday, January 17, 2017 5:56 AMI have jobs which have SSIS job steps. The basic information for the job step is in sysjobsteps, but what I want to find is the more detailed information in the tabbed items below the "Run as" line on the properties. I want to be able to search on Package, Configurations, Command files, Data sources, Execution options, etc.
Any thoughts as to where this information could be hiding in MSDB?
Is this information not in the SSISDB rather than in MSDB?
I don't have a SSISDB. Not anywhere. This is the first I've ever heard of it and it is not in any of my instances. Further investigation indicates it has to be manually created.
Let me clarify what I'm looking for. I'm looking at the jobs that run the SSIS packages. I'm looking for the data sources of those packages attached to the jobs (not to the packages themselves, because run time values are different from development values saved in the packages).
I can search the config files for these values, but I'm interested to know if the Data Source tab on the job has different information than the config files (which would happen if something wasn't set up correctly). Hence, I would like to search the information in the job step to see what connections the job step has set up for the package.
January 17, 2017 at 10:25 am
I find myself wondering if perhaps the reason you're finding it difficult to get that information is, it's not actually in a table, if perhaps when you open the step-properties in the SQL Agent, it's reading the information from the actual SSIS package?
I've been poking around trying to find the info you want, and thus far, it doesn't seem to be in any tables (side note, I'm doing this on a SQL2008 R2 instance so I KNOW the packages aren't in SSISDB)
January 17, 2017 at 10:34 am
jasona.work - Tuesday, January 17, 2017 10:25 AMI find myself wondering if perhaps the reason you're finding it difficult to get that information is, it's not actually in a table, if perhaps when you open the step-properties in the SQL Agent, it's reading the information from the actual SSIS package?
I've been poking around trying to find the info you want, and thus far, it doesn't seem to be in any tables (side note, I'm doing this on a SQL2008 R2 instance so I KNOW the packages aren't in SSISDB)
I have a sneaky suspicion it's pulling the info from the config file into the job step. Which would be great as I have a powershell script for reading the config files. But not so great if the value is manually added because ... where then can I find the value?
EDIT: Well, I've been trying to manually update Data Sources and can't figure out a way to do it. At one point, I was able to update the Set values tab, but I have forgotten how to do that. So until I can figure that out and see if it reflects in sysjobsteps, I think I'm back to reading config files. Let me know if any of you figure this out or find anything extra. Thank you all for your input so far.
January 17, 2017 at 10:48 am
Brandie Tarvin - Tuesday, January 17, 2017 10:34 AMEDIT: Well, I've been trying to manually update Data Sources and can't figure out a way to do it. At one point, I was able to update the Set values tab, but I have forgotten how to do that. So until I can figure that out and see if it reflects in sysjobsteps, I think I'm back to reading config files. Let me know if any of you figure this out or find anything extra. Thank you all for your input so far.
AHHA!
So Set values actually is free-form typing. And if I enter a value there, it is definitely searchable in the command of the job step. I'm assuming that's the place where I would add or override a data source in the config file since data sources aren't allowing manual additions.
Thanks, everyone! That helps. Now I just have to figure out how to search the XML of a package stored in MSDB for connection strings, and I'm set.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply