October 29, 2007 at 2:35 pm
i created a SSIS package and imported into Integration services. I'm trying to schedule a job to run the SSIS package. I created a new job. for type i selected SQL integration services. for package source i selected SQL server but when i tried to select my package it wasnt in the list.
Am I looking in the right place
thanks
sam
October 29, 2007 at 3:29 pm
try ssis package store as your source
October 29, 2007 at 3:42 pm
In order to execute other multiple packages, “Execute Package Task” under control flow(In SSIS) needs, sp_dts_listpackages execute permissions. This package is in MSDB database and uses sysdtspackages table. So u might have to give “Public access on sysdtspackages”
or another simple thing would be
to save the package as a file then locate or browse the file where u had saved ur package and then schedule it.
October 30, 2007 at 12:57 am
hi,
TO schedule SSIS package,first you have to copy the package to Sql server by selecting copy option in File menu of SSIS Package Designer. Then it is available when you select the
package source as SQL Server. This is one method. You can choose File System as source and select your package, you have to create an SQL agent proxy account to run the package.
code is as follows:
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'windowsServer\login', secret = 'pass'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' sqllogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
and select that proxy account for run as option. I think this will solve your problem. [Smile]
ravikumar n.
October 30, 2007 at 1:09 am
In the SQL Server 2005, after you create a SSIS package ( DTS), you want to create a job and schedule to run it. You will get the error to prevent you to run the job. What is the problem?
Here is why: SQL Server 2005 is quite different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, you need to go through the security layer in order to run the job.
The logic is like this:
Ø The job executor account needs the roles of sysadmin, SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole
Ø The job needs to be run under Proxy account
Ø The job executor account is better to be used to create SSIS package and so you are sure the job executor account has the right to run this SSIS package.
The following steps can be followed to get the job done.
The work environment is MS SQL Server Management Studio and you log in as sa.
I. Create job executor account
Highlight Security->New Login, say to make login as devlogin, type your password, default database can be your target database.
Server roles: check “sysadmin”
User mapping: your target database
Msdb database: you make sure to include SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole
Then click OK
II. Create SQL proxy account and associate proxy account with job executor account
Here is the code and run it the query window.
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
III. Create SSIS package
In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business Intelligence Development Studio. Compile/build this package.
IV. Create the job, schedule the job and run the job
In SQL Server Management Studio, highlight SQL Server Agent -> Start. Highlight Job ->New Job…, name it , myJob.
Under Steps, New Step, name it, Step1,
Type: SQL Server Integration Service Package
Run as: myProxy
Package source: File System
Browse to select your package file xxx.dtsx
Click Ok
Schedule your job and enable it
Now you can run your job.
November 5, 2007 at 4:35 pm
personally, I'm a fan of running packages via CmdExec. That way, you can have the agent log the output to a standard textfile for simple debugging (vs. using the SQL logging to the sysdtslog90 table), or both!
ie:
[font="Courier New" color="blue"]dtexec.exe /FILE <YOURPACKAGE.dtsx> /CONFIGFILE <YOURCONFIG.dtsConfig> /REPORTING EWCDI[/font]
November 6, 2007 at 4:07 am
You may just want to check that when you deployed your packaged you selected to deploy to SQL server & not the file server. This should be a very simple process from there on in.
Also worth possibly doing is using the Sql management studio connection manager to connect to the Integration services instance that you deployed your packages to - and test you can manually run the packages individually from there.
Catherine Eibner
cybner.com.au
December 27, 2007 at 3:18 pm
I've been struggling to get an SSIS package to run unattended. It works every time in Business Intelligence, most of the time in interactive dtexec, never from command line dtexec or a batch file.
I'm following the steps described in earlier posts, but I'm getting a syntax error on the sp_add_proxy statement
Any ideas?
January 25, 2008 at 8:45 am
I am struggling to lock down security for the sql2005 production system. For ssis users I have set up the group of developers to have in msdb db_dtsadmin, db_dtsltduser, db_dtsoperator and SQLAgentUserRole. Prior to this I had one user setup before I was able to figure the right security set up as sysadmin and he created a couple of packages that ran fine. Now that I dropped his account and he is using the group account (same login as originally just now a member of the group) his packages fail. I changed one of the packages in Integration Services under MSDB to package roll SQLAgentUserRole for both reader and writer, and it still fails...
What am I missing here? Need help!!!
January 25, 2008 at 8:48 am
can you provide an error msg?
January 25, 2008 at 8:48 am
Sorry though I posted the last to the general list... Will be doing that! Sorry again!
January 25, 2008 at 9:22 am
Message
Non-SysAdmins have been denied permission to run CmdExec job steps without a proxy account. The step failed.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply