Introduction
How to Schedule and Run a SSIS package ( DTS ) Job
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.
If you think this is very helpful, please leave your comments online. If you have any questions or suggestions, please email me at hong_wei_li@yahoo.com.
Happy SQLing!
- Hongwei Li
|
|
 |
 | Wow... Thanks its works
anakbinus | 18:40 17 Aug '09 |
|
|
Thanks A lot Mr. Hong, now i can run shedule ssis package
|
|
|
|
|
| Great help!
Omoba | 6:07 28 Apr '09 |
|
|
thanks a lot, your article really helped me with scheduling my SSIS package. 
Is life easier, with or without technology?
|
|
|
|
|
| faster extraction and loading on SSIS.
blackpower2k7 | 20:54 25 Apr '09 |
|
|
hi...
check out the below link for getting some tips over faster extraction and loading on SSIS.
http://www.sqllion.com/
You will know me better sooner...
|
|
|
|
|
| SSIS execution from SQL agent
mmohsin | 7:40 20 Jan '09 |
|
|
Hi,
The article very precise and a good one. I am trying to implement the same on my servers and I have two issues here,
1) I created all my packages in my local servers and now I have to move these to dev, UAT and prod servers where I do not have the username under which these packages were created.
- How to convert the package creator name to a executor name?
2) When I running the package, the job start but it never calls the SSIS itself. In the sense the job monitor shows that it is running but but won't trigger the package at all.
Please advice.
Thanks,
-Mohsin
|
|
|
|
|
| Do I need a NT account for creating an credential?
Chris sc Lee | 15:55 9 Dec '08 |
|
|
Hi,
In step 2:
II. Create SQL proxy account and associate proxy account with job executor account
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
So does it mean I need a Windows account before creating a credential? Thanks.
Chris
|
|
|
|
|
| SSIS OLE DB Source oracle proc call
WTurcios | 10:49 27 Aug '08 |
|
|
hong wei li,
I am designing an SSIS package with Data Flow that will call an oracle proc.
Currently on my Data flow i have the following:
* OLE DB Source - using Microsoft OLE DB for oracle driver for OLEDB connection
*Data Access mode - SQL Command
* SQL Command text - {call report.usp_get_unit_info}
If I click preview I get a window and see the top 200 rows.
But when I click Columns I cannot see the columns to map to an OLE DB Destination.
In PL/SQL I would run the following block.
begin
report.usp_get_unit_info(out_cursor => :out_cursor);
end;
I can write this in a script task but can't figure out why this won't work.
I could not find any information calling io cursor procs in oracle.
WT
|
|
|
|
|
| unable to cast
superman100 | 15:12 13 Aug '08 |
|
|
hong wei, i m facing an error, i dont know why when i m going to create a new job, after i done every of my selection, then i click "OK", program prompt up the following msg...
Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)
i do need some help...thx!!
|
|
|
|
|
 | Error code Error 15404
Member 3826275 | 9:34 11 Aug '08 |
|
|
Your article is so good. But I got an error when I am running the Job from SQL SERVER LOCAL. It gives me the following error.
“The job failed. Unable to determine if the owner (MEDICAHCP\ksenthil) of job FirstJob has server access (reason: Could not obtain information about Windows NT group/user 'DomainName\ksenthil'<c/> error code 0x5. [SQLSTATE 42000] (Error 15404))”
I am trying to fix this error more than 4 hours but I couldn’t do it. Please help me.
Thanks,
Kumar Senthil.
Note: I can run the package from VS with no errors. But not from the SQL SERVER AGENT.
|
|
|
|
|
| OMG Thank you so much
anvie | 7:32 1 Aug '08 |
|
|
I was wondering for HOURS why my Agent job doenst work until I read your article. Yhank you so much! 
|
|
|
|
|
| Great working solution - I think there may be more though
James Towell | 4:00 31 Jul '08 |
|
|
Thanks Hongwei
It works really well.
I couldn't create a new login, so I used my own. Maybe that's what you intended.
So I have eunet\jtowell instead of devlogin
What that means is that the proxy has my account and password. And thus that when my password changes, as it is required to do every 30 days, I'll have to edit the proxy.
And, further, when my contract expires... well you can guess!
So, I need to search for a slightly more long lasting solution.
But the main thing is that it works for now and I can get on with the other parts of this solutions.
Cheers
|
|
|
|
|
| DTS to SSIS conversion
SayCode | 5:50 24 Jul '08 |
|
|
I used the coversion wizard to convert DTS to SSIS. The SSIS package is running great as myself, but when I schecule a job to run it (using SQL Service account) it fails at step trying to execute a SQL task, as opposed to data load tasks which are working fine in the same package. Any help would be appreciated.
|
|
|
|
|
| Running an SSIS package
Stevie G | 2:02 22 May '08 |
|
|
Excellent! If only Microsoft were as succinct.
|
|
|
|
|
| Good Job Man!
JonCR | 12:30 6 May '08 |
|
|
It is a short and good explication. And the most important: it works!!
|
|
|
|
|
| How do I resolve error after following Step 1? [modified]
Paul Cho | 11:16 7 Feb '08 |
|
|
After following the step 1:
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
Then following error message box popped up:
Title of error message box is "Microsoft SQL Server Management Studio".
Content of error message box is
"Create failed for login 'devlogin'. (Microsoft.SqlServer.Smo)
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
'devlogin' is not a valid Windows NT name. Give the complete name: <domain\username>.
(Microsoft SQL Server, Error: 15407)".
Would you please respond me what should be done to do step 1 exactly as described in this article?
Thank you very much in advance.
Paul
<div class="ForumMod">modified on Thursday, February 14, 2008 9:44 AM</div>
|
|
|
|
|
|
|
I got it to working by using "Domain Name\Proxy Name" instead of just "Proxy Name" for Step 1.
Thank you.
Paul
|
|
|
|
|
| Great
Cool_rajdevil | 4:40 12 Jul '07 |
|
|
Great description.
It worked in a single shot!!!
|
|
|
|
|
| I am not able to find the option: Package Source: File System.
WadeDing | 8:23 29 May '07 |
|
|
Hi Hongwei,
I followed you instruction, and I am not able to find the option: Package Source: File System.
I am using the SQL Server 2005 Developer Edition. Please let me if you know what I did wrong.
Thanks,
Wade
Moving forward every day, even just 0.00000000001 mms
|
|
|
|
|
|
|
The Package Source: File System is close to the bottom of the user interface. Be patient. A lot of people used my article to get the job done! Have fun!
- Hongwei Li
|
|
|
|
|
|
|
I guess I didn't deploy it as File System.
Thank you,
Wade
Moving forward every day, even just 0.00000000001 mms
|
|
|
|
|
|
|
found it. I am silly. Thank you for the post. Wade
Moving forward every day, even just 0.00000000001 mms
|
|
|
|
|
| SQL Server Integration Service Package Type [modified]
Aunty B | 5:46 13 Feb '07 |
|
|
When I go to set up a new job, I don't have the SQL Server Integration Service Package in the 'Type' list. I haven't been able to find out how to add it, do any of you have any ideas or references that tell me why it's not there and how to fix it? Is it possible that SSIS isn't installed? (This is something I will be asking our infrastructure guys when they are available.)
Cheers,
Aunty B 
http://www.auntybs.com
|
|
|
|
|
|
|
Don't worry, for some reason it now appears in the list. I got access to the server and it was in the list there, and when I looked on my local version of Management Studio (after connecting to the server again just to make sure - it's not showing the little white arrow in the green circle to show the server is running) it was there. I think it is just one more example of Murphy's Law.
Aunty B 
http:www.auntybs.com
|
|
|
|
|
| Verify SSIS Account
jlminer | 11:41 5 Jan '07 |
|
|
just wanted to add this...i spent all afternoon trying to troubleshoot a job that kept failing trying to run an ssis package. after creating the proxy account and trying all the other suggestions on the microsoft kb article, i finally checked the account that the sql server services were using to run. i found the sql agent to be a local windows account and the ssis service to be using a network account. i changed them both to use a local system account and it worked right away. hopefully it saves you some time.
|
|
|
|
|
| Microsoft - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
coby cai | 18:21 26 Sep '06 |
|
|
Here is the English version of the file. The translation of Chinese version is quit ugly.
http://support.microsoft.com/kb/918760/en-us
coby
|
|
|
|
|
| A Tip
coby cai | 18:18 26 Sep '06 |
|
|
Hi, guys,
Here is a tip to below line from this article. 'yourdomain\myWindowAccount' should be the windows account you used to create the SSIS package. When I tried to use others, such as the one for Sql Server Agent, I got failure.
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Good luck!
coby
|
|
|
|
|
|
|
Last Updated 9 Jun 2006 |
Advertise |
Privacy |
Terms of Use |
Copyright ©
CodeProject, 1999-2010
