MSBI (SSIS/SSRS/SSAS) Online Training

Monday, February 16, 2009

SSIS - HOW TO EXECUTE A PACKAGE THROUGH THE CMD LINE

Hi All,
In Production environment,we should execute the packages by creating the jobs.
Jobs are internally execute the package by using the CommandLine Prompt.
This problem occurs when one of the following conditions is true:
The user account that is used to run the package under SQL Server Agent differs from the original package author.
The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
The package may not run in the following scenarios:
The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.
A registry-based SSIS package configuration uses the HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are user-specific.
A task or a connection manager requires that the current user account has correct permissions.
To resolve this problem, use one of the following methods. The most appropriate method depends on the environment and the reason that the package failed.
Method 1: Use a SQL Server Agent proxy account
Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions. This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.
Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows access control through SQL Server database roles.
Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword
Change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password.
Method 4: Use SSIS Package configuration files
Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are adequately protected if they contain sensitive information.
Method 5: Create a package template
For a long-term resolution, create a package template that uses a protection level that differs from the default setting. This problem will not occur in future packages.

The following is an example of a command line:dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1
" /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT



See the below link for more details:

http://bi-polar23.blogspot.com/2008/06/ssis-and-sql-server-agent-choosing.html

No comments: