SQL Agent Job Fails Running SSIS Packages

Oct 16, 2006

I need some help from you on a problem with SQL Server Agent that I believe many of you have experienced. When searching on the web I find some loose answers, but it would not solve my problems. So here is my story.

1. I have created two SSIS packages, package 1 and package 2. Both packages have a Execute SQL Task making a select sql statement to a table, say Customer in AdventureWorks DB. Package1 also makes a Execute Pacakge Task to Package 2. Both packages have protectionlevel property set to EncryptSensitiveWithUserKey

2. Then I created two package configuration files per package, one indirect xml configuration and one xml configuration file. The former I created an environment variable called pack1, which was specifying the path and file name of second config file. Likewise, I did for package2. Maybe I could have reused thie first configuration file, but I am not sure since package1 calls package2.

3. Now I build and deploy my packages. Then I run my manifest file, going through the deployment wizard setting the necceccary properties like server name, user, password, I have also create the same environment variables on the deployment machine. I ensure that the package1 and package2 can be run under integration services (file system). And both package works. So far so good

4. Now I want to create a SQL server agent job that runs package1 and package2. Here I found an article that decribed How to Schedule and Run a SSIS package ( DTS ) Job in SQL Server 2005 http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

5. I created a job executor account and then Create SQL proxy account and associate proxy account with job executor account as described. I created a new ssis job, specifying type ssis integration services, run as proxy, setting package source to SSIS package store, naming the server and package name (package2).

6. Now, I try running job1 which runs package2. ANd it works. I was aware if I needed to give file permission on users (read and execute) on my dtsx/dtsconfig/log files. So far so good

7. Now, I try to run job test which runs package1 (which also runs package2). But now the job fails :-(. The job runs for some seconds, but fails. I have to say that I have some script tasks with message boxes inside!!! hope this doesn't matter. I have created all neccessary accounts, proxies, credentials and so on. here is the view history

Date 17.10.2006 00:50:53
Log Job History (test)

Step ID 1
Server ABEO-GEIR
Job Name test
Step Name test
Duration 00:00:07
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: abeo-geir
as_jobuser. The package execution failed. The step failed.



Continue: This error is well known for you. It has been mentioned in many postings. Well, how do I go from here. Setting logging on the job results in only this information

PackageStart,ABEO-GEIR,ABEO-GEIR
as_jobuser,Package1,{3F5BD166-D24B-4838-B3F1-018B6C2C7523},{8E1B8E45-7F1D-4C24-852C-2694F993E6D2},17.10.2006 00:59:40,17.10.2006 00:59:40,0,0x,Beginning of package execution.

PackageEnd,ABEO-GEIR,ABEO-GEIR
as_jobuser,Package1,{3F5BD166-D24B-4838-B3F1-018B6C2C7523},{8E1B8E45-7F1D-4C24-852C-2694F993E6D2},17.10.2006 00:59:41,17.10.2006 00:59:41,1,0x,End of package execution.



Well, I read some place that I could run the job as type operating system (cmdexec). Here i could not choose Run as, but had to select SQL SErver agent account. NOw I got this message

Date 17.10.2006 01:05:21
Log Job History (test)

Step ID 0
Server ABEO-GEIR
Job Name test
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (test).



As you see, I can't come any further. I NEED HELP FROM YOU. I AM STUCK. I know many of you have solved this and I hope you can guide me through thise. I don't know how many hours or days I have used on this.



Regards

Geir F

View 2 Replies


ADVERTISEMENT

SQL Agent Running SSIS Packages

Apr 18, 2006



Hi there,

if I have some packages (.dtsx) that are depolyed to Server A and I have SQL Agent installed on Server B - if I run a run a package from SQL Agent, does it run on Server A or Server B?

Thanks in advance.

View 1 Replies View Related

Behavior Of Ssis Packages Running Under SSMS Manually Vs. Job Agent Automatically

Sep 5, 2007



I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is
"An out-of-memory condition prevented the creation of the buffer object. "


Anyway, the package runs successfully when it runs automatically under the job agent.

I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.

js40

View 2 Replies View Related

Running A Large Number Of SSIS Packages (with Dtexec Utility) In Parallel From A SQL Server Agent Job Produces Errors

Jan 11, 2008

Hi,

I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.

I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.

The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.

The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).

We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).

I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.

In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).

If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.

The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.

Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.

I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).

Additional information:

The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.

The request:

Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!

Eero Ringmäe

View 2 Replies View Related

Running Packages Through Sql Agent (best Practices)

Apr 5, 2007

Hi All

I have a meaty job that currently runs as a set of sql scripts under sql agent and I'm transferring this job to run as a SSIS package. I have managed to set it up so that it runs as a sql job (cheers guys!), and I'm watching the job run now, maxing out the CPU usage as it tries to run 10 different tasks at the same time . great stuff!

However, I've been thinking about how to view the progress of this job/package (the job only has one step; "run SSIS package"). With the current job as single scripts, in SQL agent you can view which step of the job you are at.... is it possible to somehow view which package task you're at (ideally with nice shiny yellow green and red updates like in debugging), or do you have to output each step result into a log file?

Oh, and another question! If i have a checkpoint in a package, and the package falls over, when the job is rerun through SQL agent, will it start at the checkpoint, or run from the start?

Thanks!

michal

View 10 Replies View Related

Cannot Run SSIS Packages As A SQL Agent Job

Feb 22, 2008

We are just migrating to sql servr 2005. I have created ssis pkg that runs fine when run mnaulli under BI. However, when I setup the ssis pkg to run as a SQL job. It errors out. Err is listed below. Seems like it is running the job under the SQL Agent account which does not have permissions to access the server i am pulling data from. Can u assit.
1984-2005. All rights reserved. Started: 1:30:51 PM Progress: 2008-02-22 13:30:57.55 Source: DTSTask_DTSDataPumpTask_1 Validating: 0% complete End Progress Error: 2008-02-22 13:30:58.33 Code: 0xC0202009 Source: Laser- Connection manager "FTWSQL" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'CFC0SQLA'.". End Error Error: 2008-02-22 13:30:58.33 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "FTWSQL" failed with error code 0xC0202009. There m... The package execution fa... The step failed.

View 4 Replies View Related

SSIS Packages Failing To Run. In SQL Agent

Apr 28, 2008


They wre runnning fine until 4/21/2008 then all SSIS packages started to fail.

Simple SQL jobs are still running fine thouhgt and reporting success.

Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

View 1 Replies View Related

SSIS Packages Fails Using Cmdshell

Mar 17, 2008

Folks:

When I run this from Windows Command Prompt (DOS) it runs successfully but when run from SQL Management Studio (Query nalyser) using xp_cmdshell it fails. Any help is appreciated.


This runs with SUCCESS.
-------------------------
Dtexec /F "E:PackSSIS_Total1.dtsx" /SET package.variables[User::strStatusEmailTo].Value;"xyz@abc.com" /SET package.variables[User::strFileSystemRoot].Value;"Z:" /SET package.variables[User::strServerName_Admin].Value;"pacers"

This FAILS.
------------
exec master..xp_cmdshell 'Dtexec /F "E:PackSSIS_Total1.dtsx" /SET package.variables[User::strStatusEmailTo].Value;"xyz@abc.com" /SET package.variables[User::strFileSystemRoot].Value;"Z:" /SET package.variables[User::strServerName_Admin].Value;"pacers"'


ERROR:
------
End Progress
Progress: 2008-03-17 12:28:53.13
Source: DFT Format Log Info
Validating: 100% complete
End Progress
Progress: 2008-03-17 12:28:53.15
Source: DFT Table Extract to File
Validating: 0% complete
End Progress
Error: 2008-03-17 12:28:53.15
Code: 0xC0202009
Source: MyPackage Connection manager "encore_cm"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".
End Error
Error: 2008-03-17 12:28:53.18
Code: 0xC020801C
Source: DFT Table Extract to File OLE_SRC tblFileConfigMaster [1]
Description: The AcquireConnection method call to the connection manager "encore_cm" failed with error code 0xC0202009.
End Error
Error: 2008-03-17 12:28:53.20
Code: 0xC0047017
Source: DFT Table Extract to File DTS.Pipeline
Description: component "OLE_SRC tblFileConfigMaster" (1) failed validation and returned error code 0xC020801C.
End Error

View 4 Replies View Related

SQL Agent Jobs Hung - SSIS Packages

Jun 26, 2007



I'm having an issue in SQL Server 2005 with jobs that execute SSIS packages. The jobs run fine for a week or so, then I'll come to find that four or five (of the ten or so jobs) are hung in "executing" status. They seem to hang indefinitely (as some have been "executing" for hours with no end. The schedules of the hung jobs are all different, varying from every 10 minutes to nightly. The packages perform completely diffent tasks, as well. I can't seem to find any common thread with the jobs that get hung, other than they are all executing SSIS packages.



I've tried manually stopping the jobs and restarting the agent and SQL Server but the jobs hang again on there next scheduled run. The only thing that fixes the issue is rebooting the box, and then the jobs hang again in a week or so. Could some sort of memory leak be consuming resources throughout the week and be causing the jobs to eventually hang? I just rebooted the box and the sqlagent90.exe process is currently using about 7mb of memory. I'll keep an eye on it. Any other suggestions?



I've thought of creating another job that stops jobs that are hung, but what's to say that this job won't get hung as well? Plus this seems like a band-aid fix...



I don't recall having these problems until installing SQL Server 2005 SP2. Could this be related? I've searched like crazy and still can't find a resolution to this. It's becoming a big PITA...



Anyway, any suggestions would be very much appreciated!

View 6 Replies View Related

Using Proxy Accounts To Run SSIS Packages By SQL Agent

Feb 13, 2008



Hello!

I have SSIS Package that needs special permissions in network. I want to schedule SSIS running using SQL Agent. I don't want to other Jobs starts with special permissions. So, I decided to use proxy account to impersonate SSIS starting process by account with those permissions. I've perfomed following steps:
1. Create Login for the future proxy account and add it to sysadmins role.
2. Create Credential with account name and password information.
3. Create proxy account and give to it rights to run SSIS, CmdExec and ActiveX create.
4. Set Run as for step that starts my package to this proxy
5. Start my job.
and I receive error "Process couldnot be created for step 1 job xxx. Reason: 1314"
SQL Agent account and proxy accounts has also administrators rights on SQL Server computer and rights to login as service.
When I change step configuration to run as SQL Agent account job completes succesfull.
What do I do wrong?

Thx,
Olga

View 8 Replies View Related

SSIS Running Packages

Sep 20, 2006

Is there any way to check from SQL code if a specific SSIS stored package is running like a SQL system view or stored procedure? Management studio option works fine but not if you need to determine if a specific package already is running or not?

View 3 Replies View Related

SSIS Package Fails From SQL Agent Job

Mar 12, 2008

Hi,
I created a SSIS Package and now i want to run this package from SQL Agent Job. I set up the job and when i run it, it failed

Job Properties:
Type: SQL Server Integration Services Package
Run As: SQL Agnet Service Account
Package Source: File System
Package: \pc17917c$Documents and Settingskdesai1DesktopSSISTest1Test1Package.dtsx

Error i got when i execute the job.

Description: Fauiled to decrypt protected XML node "PassWord" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-03-12 10:50:54.48 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-03-12 10:50:55.07 Code: 0xC0048006 Source: Drop Table ActiveX Script Task ... The package execution fa... The step failed.


I'm new to the SQL 2005 SSIS. Can you please help resolve this problem?

Thanks,
KD

View 13 Replies View Related

SSIS Package Fails In SQL Agent

Sep 27, 2006

Okay, I see that dozens of posters have this problem, but none of the threads has a solution: Why does an SSIS package run perfectly fine in VS and in Integration Services, but fail with no details in SQL Agent? Is there another way to have an SSIS package run regularly?

Thanks.

View 5 Replies View Related

SQL Agent Fails To Run A Ssis With All Accounts But One

Nov 10, 2005

Hello,

View 4 Replies View Related

SSIS Running Packages NOT Being Displayed

Sep 5, 2006

When I run an SSIS package from the MSDB in SQL Server Management Studio it never shows up under 'Running Packages'.

I have confirmed that the package is indeed running.

I've also tried to check the packages running on the server programatically by using the GetRunningPackages() method. This too returns nothing when clearly packages are running.

Any ideas?

Malcolm Barclay

Disney Consumer Products UK

View 4 Replies View Related

Executing SSIS Package From Agent Fails

Feb 7, 2007

Hi,

I have an SSIS package that utilises a 3rd party ftp program to transfer files (over HTTPS). This software stores details in the users profile relating to addresses, user names and password for transfers. As this is the case the Package needs to be executed by the domain user who has the details set in their profile. The package needs to be executed at a scheduled interval - so I have set up an Agent job to do this, and have the the 'Run As' setting, as a proxy which maps to the required domain user.

The package works fine when executing manually when the required user is logged in. If, however, the user is not logged in - ie when the job kicks off at the schecduled time, the file transfer fails. On debugging I can see that the agent job does not load the user's profile -but instead uses the 'Default User' profile.

The job owner is set as the same domain user that the Run As setting for the step is set. The SQL Server Agent services runs as a different Domain user.

Has anyone else had similar problems - Are there any extra permissions I need to set?

cheers..

View 4 Replies View Related

Missing Log Output Section For SSIS Packages In The SQL Agent Job Properties

May 6, 2008



Hi All,

I have a requirement of logging the failure and the error of a job executing a SSIS package to text file.
However, when I go to job step -> Advanced, in the section "SQL Server
Integration Services Package" where that info to setup logging is, it
is blank and just says:

"There are no options for this command type"

Any ideas?

I thought that this might not be supported in Standard Edition as I am running standard edition. Then I checked on two other servers, where I have Enterprise edition with SP2. On one of the installations, I am able to give the log file, the other one still shows,

"There are no options for this command type"

What is the problem? Both of them I am using the sysadmin role account. I have proxy account created on both the SQL instances. I don't think this is the problem, but still I checked this.

Can anybody help me?

Regards,
Virendra

View 8 Replies View Related

Monitoring The Running And Execution Of SSIS Packages With MOM

Jan 11, 2006

Hi,

Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?

What is the recommended way to monitor SSIS execution?

Thanks,

- Joel

View 2 Replies View Related

Running SSIS Packages In MS Server 2005

Jan 31, 2008

Hi
Just a brief question,
is there a way of displaying a success or not success message when running (testing) an SSIS package on SQL 2005 ?

Because i have to go through the steps to see if there was an error or not and for large SSIS packages this is a pain.

With DTS 2000 after it ran you got a message saying successfull or not, is there a way to activate this in SQL 2005 ?

I suppose i could just run the job ... but I prefer the other way as sometimes a job fail is a permissions problem, not a SQL/datapump task problem...

View 1 Replies View Related

Inconsistent OLE DB Failure When Running SSIS Packages

Jan 24, 2007

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @[User::ConString] , "Royalty", @[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.



I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto



Any ideas welcome



View 5 Replies View Related

Running SSIS Packages In Management Studio

Mar 18, 2006

We are using SQL Server 2005 Standard edition. We have the development studio installed on a developer€™s workstation. The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.

The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server. I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.

When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.

I€™m sure this is a permission based problem since it runs fine on my workstation. I tried logon onto the SQL Server with my id (I€™m an admin to the server). I tried running the agent with my windows id. It looks like I€™m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.

In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.

View 4 Replies View Related

Running Remote SSIS Packages Dynamically

Dec 19, 2007

I seem to be missing something with SQL 2005 SSIS and I am wondering how I would accomplish my taks. With SQL 2000 and DTS we designed our DTS packages so that we could load one file or database from start to finish. We then have controlling programs written in VB that will monitor directories, when a file shows up in a directory it loads the package, changes the necessary properties and executes it. These programs handle all of our error notifications and dealing with the files afterwards, making sure files are complete in the directory before the DTS runs, etc. With .NET and SQL 2005 I have learned that I can change the properties by using variables and expressions, which I think would handle all of our situation but I can not run them remotely. The documentation says that I could setup a scheduled job and then call sp_start_job from my program. That seems like it would require a lot of scheduled jobs and then I still need to get the parameters into the packages. The other option is to use a web service, that would require IIS on my SQL box which everywhere says I don't want. Am I using SSIS for something I am not supposed to be or how do I accomplish this in 2005?
I have noticed that you can not even run packages through the SQL Server Management Studio. We have a number of packages that are built that are run on demand when needed. In SQL 2000 I could just click and execute the package. In 2005 is my only option to setup some sort of fake job in the job scheduler?

View 6 Replies View Related

Running SSIS Package Through SQL Agent

Jun 18, 2008

Hello,

I'm new here and hope you will be able to help me.

I have created several SSIS packages with Visual Studio 2005.
They all work fine in debug mode.
I have been able to make them work with a ODBC connection by using a ADO.NET connection.

Then I exported them to the file system in my SQL Server 2005 database and created a task in SQLAgent to run them.

All the packages using the ODBC connection fail with the following error :

Login failed for user XXX
Error : 18456; Severity : 14 , State : 8

This error is a password mismatch.

I tried several database users and checked the passwords multiple times.

It looks like SQL Agent is not able to retrieve the password although it is stocked in both the ODBC connection and the SSIS connection.

Can you help me?

Thanks,

Ares

View 19 Replies View Related

SQL Server Agent Fails SSIS Package Execution At The End.

Oct 1, 2007

I have an SSIS ETL Package that runs perfectly in debugger.

It is saved to the local SQL Server using "rely on server security".

The SQL Server Agent job runs at night and the job fails right at the last step. It is actually loading the data, because I write a record with row counts to an audit table, and they are successfully incrementing.

The real issue to me is that it is failing randomly, sometimes it will run 7 times then fail once, others it will go 2 or 3 days then fail. The job runs at the same time every night, and takes within a minute or two variance.

I've looked at the logs, and done a trace, and I can't seem to find anything that would cause the failure. The closest thing to an error is a join parameter note in the trace that's on tthe MSDB database.

Not a lot to work with, I know... but does anybody have any advice for me? Thank you in advance!

View 2 Replies View Related

Executing SSIS Package Through SQL Server Agent Fails

Nov 28, 2007

Hello,

I try to run a job from SQL Server Agent to execute an SSIS package, but it always fails. However, if I execute the SSIS package directly, it will succeed.

The SQL Agent Job History shows "Unable to start execution of step 1 (reason: Error authenticating proxy DB1DB1_CREDENTIAL, system error: Logon failure: unknown user name or bad password.). The step failed."

P.S. DB1 is my server name, DB1_CREDENTIAL is the account creating the credential

The SQL Server Agent Error Log shows "[298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000]"


I've created an account on OS and assigned it to a credential.
Then, I created a proxy with the credential and then created the SQL Server Agent job with that proxy.

P.S. My SSIS package protection level is created as "Rely on server storage and roles for access control", because if it is set as "Do not save any sensitive data", package execution will fail even I run it directly.

How can I successfully run SSIS package from SQL Server Agent?
Do I lose any setting steps?

Thanks for help.

Lilia

View 18 Replies View Related

SSIS Package Scheduled In SQL Agent Fails On Occasion

May 13, 2008

I have one SSIS package that fails on occasion. And I can then run this in the job by itself after it fails and it runs fine. Any ideas? Any ideas on what is causing this. It is not every day but about once a week lately but it just happened again today to this is 2nd time in 4 days this has happened. It is always on this same part in the SSIS that it fails when it does fail. 4 times in last 2 weeks.

This step it is on here has a OLE DB - DB2 source and SQL server as destination. It does a couple data conversion, derived columns and then just copies the data from the db2 table to the sql server table.


Message
Executed as user: PERFORMANCEstacyadmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:06:12 AM Error: 2008-05-13 06:09:22.84 Code: 0xC0202071 Source: Copy SalesTender Retail TmpSalesTenderRetail [97] Description: Unable to prepare the SSIS bulk insert for data insertion. End Error Error: 2008-05-13 06:09:23.42 Code: 0xC004701A Source: Copy SalesTender Retail DTS.Pipeline Description: component "TmpSalesTenderRetail" (97) failed the pre-execute phase and returned error code 0xC0202071. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:06:12 AM Finished: 6:09:23 AM Elapsed: 191.157 seconds. The package execution failed. The step failed.


Stacy

View 2 Replies View Related

FTP Task Fails - SSIS Scheduled With SQL SERVER AGENT As JOB

Nov 21, 2006

Hi,
I have created a SSIS package and tested it in V.Stdio where it runs fine and uploads file on the remote server.

Now I have scheduled it to run as a job in SQL SERVER AGENT as JOB, but it fails when it comes to the upload task?

I am quite sure as it is something with FTP connection string or security, but can't figure it out.

I have simply added the IP/login/password in the connection manager for upload task.

Any help/ guidance would be great.

Thanks,
Ad

View 4 Replies View Related

Scheduling SSIS Package From SQL Server Agent Job Fails.

Feb 21, 2008



HI,

I am having a strange error coming in SQL server 2005.

I had a DTS package developed in SQl server 2000. Then i had migrated that DTS package to SSIS package using Execute DTS 2000 package. The conversion is successfull.

Then when i manullt execute this package it runs successfully without giving any error.
But then wheh i schedule a SQL server agent job for the same above SSIS package it fails giving me some cryptographic error as follows:

Message
Executed as user: CATOS-CGDBTUW02SYSTEM. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-02-20 23:00:01.00 Code: 0x00000000 Source: Execute DTS 2000 Package T... The package execution fa... The step failed.

Not sure why is this happening.

Manually running the SSIS package from Visual studio does not gives error, but the same SSIS package when is been schedule from SQL server 2005's SQL server agent job it fails giving above error.

Please help me out how to rectify whith this.

Thanks.. in advance

Regards,
Ashok

View 3 Replies View Related

SSIS Writing To MSMQ Fails Only When Scheduled By Agent Job

Feb 12, 2008



Hi All,

I'm a bit lost on this...

I have an SSIS package which writes to a configured public MSMQ. When run directly through Visual Studio, file system or msdb it works fine (I guess these use me as the user).
However, when I schedule it through an SQL Agent Job it fails with the following:

Failed to write message ... to the queue.

This seems like a permissions thing to me, but I've tried giving full control to everyone in the MSMQ permissions, also explicitly giving full control to the user that the agent job executes as. Neither made a difference.

Any help would be greatly appreciated.
Cheers,
Tony Vaughan.

View 2 Replies View Related

Errors While Running Ssis Packages From The Command Prompt.

Dec 23, 2005

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
   Code: 0xC00470FE
   Source: Data Flow Task DTS.Pipeline
   Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
   Code: 0xC00470FE
   Source: Data Flow Task DTS.Pipeline
   Description: The product level is insufficient for component "Script Component" (9).
End Error


i have entered the command as follows.

dtexec /f "c:somefolderpackage1.dtsx"

 

Any points regarding this issue would be helpful.

 

View 7 Replies View Related

Usage Question - Running SSIS Packages Remotely

Jan 29, 2007

Hello,

I currently use DTSRun to run packages on my scheduler box. I would like to use the same set up to run SSIS packages.

Is there any way to duplicate this without installing SSIS on my scheduler box? Can I specify the server on which it should run?

Just don't want topurchase another license, if possible.

- Dave-

View 3 Replies View Related

Scheduled Job Running SSIS Packages Not Completing As Expected

Oct 2, 2007

I have a SQL Scheduled Job in SQL 2005 SP2 that executes a few different SSIS packages. When I update the SSIS Packages, SQL Agent runs the job and shows success; however, the underlying packages are not executed. The packages are located on the server but we are using UNC paths in the jobs since much of the time they are started manually from a remote workstation and the jobs seem to look at the initiating workstation's file server rather than that of the SQL Server. Is this a known issue?

Thank you,

Jennifer

View 7 Replies View Related

Cloud Of Confusion: Running SSIS Packages On 64 Bit SQL Server

Sep 25, 2007

It is clear to me that in order to be able to use certain SSIS components (for example the Excel jet provider) I must launch my packages using the 32bit DTEXEC located at Program Files (x86)Microsoft SQL Server90DTSBinn. However, when I do this it seems that there are other components of the package that no longer work as expected.

To test this I have created a simple package with two tasks (Run64BitRuntime is set to False):
1. Data flow task importing data from Excel
2. Execute SQL Task which does a simple select (select 1) from a Native OLE DB SQL data source (same SQL Server on which packages are stored). This task contains no input or output parameter.

When I try to execute the package using the 64bit DTEXEC, task 1 fails with the following error (as expected):
Code: 0xC0202009
Source: connection1 Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

When I execute the package with the 32bit DTEXEC, task 2 fails with the following error
Code: 0xC002F210
Source: Execute SQL Task Execute SQL Task
Description: Executing the query "" failed with the following error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Now here is the confusing part: When I change task 2 to use the .Net provider instead of the OLE DB the package works fine. According to the MS documentation, both of these providers are supported on 32 and 64 bit so am I missing something? One more thing to note: before I was able to use the 32 bit DTEXEC I had to re-register it as described in this KB article: http://support.microsoft.com/kb/919224

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved