July 13, 2011 at 6:16 am
I am having a problem while running the package through SQL Server Agent (as most of the people have) and I am new to SSIS and think problem is to deal with context under which package is running through SQL Agent.
I need to understand the contexts, used during execution and deployment of SSIS package.
There 3 different user contexts used in SSIS execution and deployment.
1) Context used in Connection manager in SSIS package (can be windows or SQL server authentication) or specified in config file
2) Context used while deploying an SSIS package (can be windows or SQL server authentication)
3) Context specified while running the package from deployed SSIS package on SQL Server (Only windows authentication)
Can anybody please tell me how these credentials are being used?
Also, is there any relationship among these contexts and if yes, how are they related?
July 13, 2011 at 6:48 am
what are the problems that you are having?
may be more useful to tell us more information before we answer your questions so we are not heading down the wrong track
July 13, 2011 at 7:59 am
The SSIS package runs using BIDS or SSIS but when used with SQL Agent, it continues to run without showing any success or failure.
We have to manually stop the job, and error logs do not show any errors.
The SSIS package does the job of transferring data from Oracle To SQL Server 2008 R2 database table.
The package uses source connection to Oracle and destination connection to SQL Server.
This destination connection uses the SQL authentication (SQL login having server role public and dbcreator) and having access to only particular user database.
ProtectionLevel has been selected as "DontSaveSensitive".
I have created and added a package configuration file in project to include the connection string for source and connection string. Also set CreateDeploymentUtility to true in Deployment Utility tab of properties of SSIS project.
Here I am able to run the package through BIDS if I modify config file to include passwords.
While deploying package on the SQL Server (Currently deployment server is same as where package has been created), I am using windows authentication. This windows autheticated user is domain user and is sysadmin in SQL Server as well as admin on the server. I am selecting path of deployment as Maintenance plans in SSIS Packages.
Now I am able to see this package in MSDB database - Maintenance Plans of SSIS if I logged to SSIS instance.
I am able to run this package with windows authentication mode.
Now I am creating a job for this package.
In the job, owner is this domain user.
While creating Step, type is SSIS package and run as SQL Server Agent Service Account.
This account is also another domain account.
Package source is SSIS package store and selected windows authentication mode, and maintenance plan from MSDB database.
Now when I run this job manually, it just shows that it is running but I am not getting what it is doing since it not showing any success or failure nor any errors in the logs.
I have also used SSIS Proxy account which uses the credentials (Identity - windows autheticated user) but of no use.
Could you please help in resolving this?
July 20, 2011 at 9:54 pm
Hi Vividus
When you schedule the package in the agent. Make sure you specify run as 32bit.
It usually solves most driver related issues. The GUI is 32bit and hence if you run it in debug it runs fine.
Cheers
Jannie
August 1, 2011 at 11:17 am
To clarify, does the job ever report success or failure or will it simply show as running indefinitely until you manually stop the job?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 10:38 pm
It does not report any success or failure. We have to stop it manually.
August 3, 2011 at 6:26 am
What else is the package doing? Many times when I see this type of issue it's related to an interactive prompt showing up when the SSIS package is run unattended by a user for the first time on a new machine.
- Do you have anything happening in the package having to do with encryption keys, e.g. SFTP, Certificates or PGP? The first time a key is used it is expected that someone will explicitly accept the use of that key so it can be added to that user's keyring. This usually requires a person to type 'Y' at a console window and cannot be automated as it would defeat the purpose.
- Are any third-party components being used within the package? Licensing is sometimes setup on a per-user basis. When a new user makes use of a component sometimes prompts are brought up to accept license agreements, or validate information, etc.
The recommendation overall is to log into the server as the user who will be running the job, whether that be your proxy account login or the login used to start the SQL Agent service, and run the job from the command line. You can get the command line from SSMS for SSIS job step types and just copy and paste it on the command line.
Let us know how it goes.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply