Scheduling SSIS package as a SQL job

Question

Scheduling SSIS package as a SQL job

ishaan99

SSCertifiable

Points: 6261
More actions
January 5, 2007 at 8:43 am

#114992

Hi ,
I am new to 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. In teh Log History fo eth job it shows up error as
Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 2).
When checking SQL Agent log the eror is,
“SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. “
Through research I have no clue as what I need to do, or where to look.
The package runs without error when I execute the package itself.
All that my DTS have is TSQL statement to truncate a sql table then connect to Oracle database using linked server and load data from Oracle to sql table which is truncated before. As i said the SSIS pckg exceutes by itself with no problem. Its only whn i try from sql agent that it fails.
Any help will be greatly appreciated.
TIA
January 8, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

scottwaltonmcse SSC Veteran Points: 217 More actions · Answer 1

I had the same problem. I used transact SQL to create my credential instead of SQL Server Management Studio. When I checked my work in SQL Server Management Studio, I found that the Identity was wrong.

Here is a quick summary of what to do from scratch:

1. In SQL Management Studio go to Security > Credentials > Right Click > New Credential

Fill in the Credential Name. Set the Identity to the login you want to use. If you are using a domain login DON'T FORGET THE DOMAIN. (Yeah I forgot this too.) Type and reconfirm the accounts password.

2. Next Click SQL Server Agent > Proxies > Right Click > New Proxy

Give it a name in the Proxy Name box. Select the Credential you just created by browsing for it in the Credential Name box. Assign it the proper security (I used CMDExec and SSIS Package Execution since I was using the proxy to run a SSIS package from a Job).

3. In the job STEP you are trying to run select the credential from the RUN AS drop down menu.

Hope this helps!

Scott

Crown Clit Old Hand Points: 374 More actions · Answer 2

Thanks Grasshopper

Nice and simple explanation.

Finally I’ve got my problem fixed

Cheers:

CC

Sunil Kumar P T-457024 SSC Veteran Points: 287 More actions · Answer 3

[font="Comic Sans MS"]Hi, i did all the procedures you mentioned above, but then also i am not able to schedule the job.. it is giving me following error

"Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)"

I seen in some forums regarding this error, they mentioned you need to reinstall SQL Server 2005.. Is it?

i don't want to install it again.. is there any solution apart from reinstalling? please tell me... if i ran from BIDS it is running fine, but when i schedule as job i facing problem

hope to see your reply[/font]

scottwaltonmcse SSC Veteran Points: 217 More actions · Answer 4

Sounds like there may be some missing components in your installation. A reinstall may not be necessary. Make sure that SSIS is installed as well as SQL Agent.

I've also seen a similiar error when I've developed a package on a different version/patch level of SQL Server than what I'm runnning the package on. Are you developing on Make sure the service pack level is current.

Sunil Kumar P T-457024 SSC Veteran Points: 287 More actions · Answer 5

[font="Comic Sans MS"]Hi i checked both SSIS and SQLAgent in Services list, both are installed and running fine....

then also i am getting the error.. any clue why it is happening like this? Help me[/font]

normkatz Valued Member Points: 71 More actions · Answer 6

The best thing you can do for yourself for errors in running a SqlAgent job on an SSIS package is to add a log provider to your SSIS package and point it to a standardized path for your organization. I use c:\ssis_log\package_name for all of my job logs. In SSIS GUI you can define what types of events you want the logger to record. It helps to be more verbose, by checking most of the checkboxes when you are first debugging. Before you go into production, you can turn off all but the most basic progress event notifications so you know which step it died on if you get an exception. It takes a little effort to learn how to create and enable the logger but it's described in several of the SSIS books. Since a package running in the SqlAgent context can behave differently than one in the designer, especially when you have parent/child packages and config files, I consider it essential to use logging.

nboyes Newbie Points: 9 More actions · Answer 7

nboyes

Newbie

Points: 9

July 30, 2008 at 1:40 pm

#850841

Thanks so much this help out a lot!!:D

srinivas pallikonda Say Hey Kid Points: 669 More actions · Answer 8

Hi Scott,

I performed all the steps one by one and my pkg executed like charm. That was a great help.

Thank you very much.

lil Valued Member Points: 71 More actions · Answer 9

Hi, Scott,

I have trouble at step 3:

3. In the job STEP you are trying to run select the credential from the RUN AS drop down menu.

When I right click on the step I was trying to run, there were the following options:

New Job

Start Job at Step...

Stop Job

Script Job as

View History

Disable

Reports

Rename

Delete

Refresh

Properties

There is no option "run as".

I'm using SQL 2005.

Would you please let me know the exact location to find that step. I still have the Error:15404.

Thanks in advance!

Lily

lil Valued Member Points: 71 More actions · Answer 10

Hi, scott,

I found the Run as in the step 3. It is at step/edit option. The property of the step job.

Thanks,

Lily

lil Valued Member Points: 71 More actions · Answer 11

Scott,

But I still have the error!

What else is missing?

I used administrator as SQL and AD user account.

My two SQL instances are in the same domain.

I followed your three steps.

There is still the error!

I'm pulling my hair to find the solution.

If you know anything to check, please let me know.

Thanks,

Lily

jreece Ten Centuries Points: 1138 More actions · Answer 12

I remember a co-worker had this exact same problem a few years ago. Sorry to say as far as I know it was never resolved! You asked for any ideas, and I believe it is the Oracle linked server that is the source of your problem, as it was with my co-worker's issue.

craig-1058671 Valued Member Points: 64 More actions · Answer 13

I'm still dying on this. I have run through the steps Scott provided which I totally agree with. I did it all. When I run the job I am getting this error:

Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 6). The step failed.

proxy_id 6 is the very proxy I'm using based on "select * from sysproxies".

I'm at a loss. SSID problem?

Craig