Contents
How to set SQL server connection for SQL Server Agent?
Right-click SQL Server Agent and select Properties. In the SQL Server Agent Properties dialog box, under Select a page, click Connection. Under SQL Server connection, select Use Windows Authentication to enable SQL Server Agent to connect to an instance of the SQL Server Database Engine with Microsoft Windows Authentication.
How to create SQL Server Agent jobs for packages?
To create a job step for the package you want to schedule, click Steps, and then click New. Select Integration Services Package for the job step type. In the Run as list, select SQL Server Agent Service Account or select a proxy account that has the credentials that the job step will use.
What happens after SQL Server Agent job step?
After a SQL Server Agent job step starts a package, the package execution may fail or the package may run successfully but with unexpected results. You can use the following tools to troubleshoot these issues.
Do you use proxies for Transact-SQL job steps?
Transact-SQL job steps do not use SQL Server Agent proxies. Instead, the job step runs as the owner of the job step, or as the SQL Server Agent service account if the owner of the job step is a member of the sysadmin fixed server role.
How to find connection of SSIs package in SQL Server?
You can then join on the result sets, to get the step Id, PackageName and the connection strings in that package. Run this query over the server you want to check: Cheers! Super old thread but the below code is slightly adapted and much more useful as it shows package level details.
How to find SSIs package in SQL job step?
Here is what I would suggest, use the query that Rodrigo A has provided and tweak the column ‘Command’ further to get the packages name from it by using string functions and have the Step ID and Package Name as output. use the list of packages that you have obtained and put it in the IN clause of the following query and run it against SSISDB –
How to use SQL Server Agent with Windows Authentication?
In the SQL Server Agent Properties dialog box, under Select a page, click Connection. Under SQL Server connection, select Use Windows Authentication to enable SQL Server Agent to connect to an instance of the SQL Server Database Engine with Microsoft Windows Authentication.