scheduling a SSIS package

Question

Post reply

scheduling a SSIS package

Sam-170626

Old Hand

Points: 342
More actions
October 29, 2007 at 2:35 pm

#153924

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

Adam Angelini

SSCrazy Eights

Points: 8548

October 29, 2007 at 3:29 pm

#746413

try ssis package store as your source

Mayank Khatri SSCertifiable Points: 7934 More actions · Answer 2

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.

ravikumar n-447656 Old Hand Points: 347 More actions · Answer 3

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.

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 4

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.

xyvyx@hotmail.com SSC-Addicted Points: 425 More actions · Answer 5

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]

Catherine Eibner Ten Centuries Points: 1344 More actions · Answer 6

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.

Kindest Regards,

Catherine Eibner
cybner.com.au

kyoungdahl SSC Rookie Points: 35 More actions · Answer 7

kyoungdahl

SSC Rookie

Points: 35

December 27, 2007 at 3:18 pm

#763103

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?

Lowry Kozlowski SSCommitted Points: 1868 More actions · Answer 8

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!!!