October 5, 2006 at 9:25 am
I have saved or imported SSIS packages that runs fine when I execute them in SQL Server Business Intelligence Development Studio.
So now I want to schedule them to run every day. The only way I have found is to create a job on my own then tell the package to execute but instead of executing it I go to the command section and copy that and then paste it in a step in the job I created and tell the job to use CmdExec to run the code I put there.
I get the following error:
The process could not be created for step 1 of job ... (reason: The system cannot find the file specified). The step failed.
I'm probably doing it the hard way but I haven't been able to find any other way.
How can I get a SQL Server job to successfully execute SSIS packages?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 5, 2006 at 9:43 am
When you create the job step, set the Type drop-down box to "SQL Server Integration Services Package".
Then you should be able to specify the server and package to execute.
October 5, 2006 at 12:43 pm
I'm getting a little farther now. Thanks!
Now I get this error:
Executed as user: DomainName\administratoraccount. The package execution failed. The step failed.
And the account it is using is an sa. Its not a very descriptive error it gave me. I am investigating now to try and see if it is even executing any part of the package, right now I don't think it is.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 5, 2006 at 2:23 pm
Is this a default or named instance?
October 6, 2006 at 6:44 am
I believe it is a default instance. There are no other SQL Servers installed on that maching and I only needed the name of the computer to register it.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 6, 2006 at 9:26 am
If it was a named instance it might need the config file changed. You might double check your config settings any (BOL:Configuring the Integration Services Service)
October 18, 2007 at 3:21 am
[font="Comic Sans MS"]In SQL Server Agent while creating a job step, in the Type drop-down box i am not finding "SQL Server Integration Services Package".
Can any body please tell me why it is not showing?
is it because 2005 is not installed or anything is missing?
Please help me out, i am in very much need of it..[/font]
October 18, 2007 at 3:49 am
October 18, 2007 at 5:26 am
[font="Comic Sans MS"]Yew during installation i have checked the Integration Services,
I am able to connect to integration services.
while scheduling the packages using SQL Server Agent under type i don't find SQL Server Integration Services Package to select..
Can you please help in this
thanks in advance
Sunil [/font]
October 18, 2007 at 3:01 pm
Check and see whether SQL integration service is running by going to SQL Server Configuration Manager.
October 18, 2007 at 11:23 pm
[font="Comic Sans MS"]I checked in the sql server configuration manager for Integration services and integration service is running...
what else may be the problem.. do i need to install SQL Server 2005 again?
I don;t think that is the right soln..
Plz help me[/font]
October 19, 2007 at 12:04 am
plz go through the following code instructions. I think this will work for you.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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.
***********************************************************************
October 27, 2007 at 10:04 am
Refer the below link for scheduling SSIS package
Regards..Vidhya Sagar
SQL-Articles
December 17, 2007 at 3:07 am
[font="Comic Sans MS"]Hey thanks for your help...
Now i can able to execute the imported package..
The problem is Packages are running fine when i ran from BIDS..
when i want to schedule package as job i am getting following error..
"Unable to cast oject of type 'Microsoft.Sqlserver.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'.(Microsoft.SqlServer.Smo)"
Any thing i need to Configure in order to run package as Job
Please help me where i am wrong.. if scheduling of package is completed almost my job is done... Plz any one who know regarding this error plz help me[/font]
December 17, 2007 at 3:16 am
[font="Comic Sans MS"]Hey thanks for your help...
Now i can able to execute the imported package..
The problem now is Packages are running fine when i ran from BIDS..
when i want to schedule package as job i am getting following error..
"Unable to cast oject of type 'Microsoft.Sqlserver.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'.(Microsoft.SqlServer.Smo)"
Any thing i need to Configure in order to run package as Job????
Please help me where i am wrong.. if scheduling of package is completed almost my job is done... Plz any one who know regarding this error plz help me
Regards
Sunil [/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply