Hello
I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.
Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.
Thanks
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
dtexec /f "C:Program FilesMicrosoft SQL Server90DTSPackagesIntegration Services Project estcom.dtsx"
pause
Why it's not running with SQL Server Agent???
Hi,
Just need some help on a job issue.
The job in question gives the below mentioned error when run
Error 22022: Unable to post notification to SQL
ServerAgent (reason: OpenFileMapping()returned error
2, 'The system cannot find the file specified')
This seems to have started after I changed the owner for the Agent and MSSQL service from local service to a domain ID.
FYI, I have also changed the job owner from sa to the domain ID and have tried restarting the agent service in the hope that the Job can speak to the agent .. but it doesnt :(
Microsoft suggests to change some registry entries but I am not sure if that'd work
http://support.microsoft.com/default.aspx?scid=kb;en-us;911841&sd=rss&spid=2852
The server on which this job is hosted is a production box .. so am a litle apprehensive about making any registry changes
Any Suggestions ?
Thanks.
Warm Regards,
Ranjit.
Hi,
I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.
I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."
I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:Program FilesMicrosoft SQL Server90DTSBinnDTEXEC.EXE" /SQL "AFAReductionAFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\<anotherservername>PCStatsliveCurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."
Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.
Please let me know why I might get this error, when the package runs OK on its own.
Many thanks,
Keith.
Hello,I have a SQL Server Agent job that has two steps. The job fails when I run it normally, with both steps. However, when I tried to troubleshoot, I ran each step individually, and both succeeded without errors. The first step runs an SSIS package under a proxy. The second is a T-SQL statement that simply runs a stored procedure. (There is no option for "Run As" for the T-SQL statement.) Again, each step runs fine when I run it under SQL Server Agent; also, the SSIS package runs fine when I run it from the file system, and the T-SQL statement completes successfully when I run it as a query.Does anyone have an idea why this is happening? Thanks!
View 3 Replies View RelatedTwo NT accounts have been created on the server.
MSSQLSERVER
MSSQLSERVERAGENT
These accounts have been given:
a. logon rights
b. unchangeable passwords
c. passwords which never expire
d. NT admin rights
Questions:
a. Do I have to create a login account within SQL Server 7 itself for these accounts?
b. Must the properties for "this account' box on the general tab include the domain name?
c. If the answer to (b) is yes, then can I use ".MSSQLSERVERAGENT" in the "this account"
box on the general tab?
d. Does the SQL Server Agent function require the "BUILTINadministrators" login in order
to work?
Any help in figuring out why I can't get my SQL Server Agent to startup will be appreciated!!
H!
Very critical!!!! some body help..
I am execute paakge correctly from my local machine through Agent and Command line. but when i try to execute in another server it fails. I am invoking and executing as an administrator. when i run through the command prompt or as an individual pacakge in file system it works fine.
I am exceuting the package with Protection level Don'tSaveAsSensitive.
I also want to know a way to fine out what the error is exactly.not just the View History. If i set the logging for the package where do i view them???
Thanks,
Jas
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!
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
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
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
I created a SSIS package which logs to the event log (containing a script task and a file system task). I deployed the package using the manifest utility on a 2003 server. If I run the package using the DTexec utility logged on with a domain user account it works fine but if I use the same domain account as credential for my proxy used to run a job with the SQL Server Agent, the job fails. The log viewer tells me the following:
Started: 13:07:19 Error: 2008-01-10 13:07:19.97 Code: 0xC001403D Source: Description: The log provider type "DTS.LogProviderEventLog.1" specified for log provider "SSIS log provider for Windows Event Log" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name. End Error Error: 2008-01-10 13:07:19.97 Code: 0xC0010018 Source: Description: Error loading value "<DTS:LogProvider xmlnsTS="www.microsoft.com/SqlServer/Dts"><DTSroperty DTS:Name="ConfigString"></DTSroperty><DTSroperty DTS:Name="DelayValidation">0</DTSroperty><DTSroperty DTS:Name="ObjectName">SSIS log provider for Windows Event Log</DTSrop" from node "DTS:LogProvider". End Error Could n... The package could not be lo... The step failed.
The job succeeds if I grant the domain account local administrative rights.
What's wrong, who can help? Thanks.
I am extracting data from an Oracle database and have installed the latest x64 ODAC. When I run the 64 bit dtexec in cmd the package runs fine with no errors, but when creating and executing a SSIS job in the agent it fails. I've tried creating the job using CmdExec as well and I get the same errors:
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: 11:12:43 AM
Error: 2007-08-15 11:12:45.63
Code: 0xC0202009
Source: Load Dimension Data Connection manager "ora"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2007-08-15 11:12:45.65
Code: 0xC020801C
Source: CopyCustomers CustomerSource [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ora" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2007-08-15 11:12:45.65
The CmdExec job step uses the _exact_ same command as the one I execute successfully in cmd:
"c:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
I have managed to find two work arounds, but they are quite ugly:
1) Schedule the package execution using Windows Task Scheduler, basically create a bat file which runs the package.
2) Schedule the package in SQL Server agent, but as T-SQL script and use xp_cmdshell, i.e. EXEC xp_cmdshell 'dtexec /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'
Both work and use the 64 bit dtexec. Not that elegant though..
CmdExec and the 32 bit version of dtexec works, but is not good enough for me since we bought new hardware and 64 bit software just to be able to address more memory. Has anyone managed to execute a SSIS package successfully using the agent and the 64 bit OraOLEDB.Oracle.1?
Any help would be greatly appreciated. Thanks!
Btw, I am using Windows Server 2003 x64 and SQL Server with SP2.
Hi All,
There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.
I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.
Any pointers ?
I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.
I tried all the possible fixes I found on the web but still can't get it to work.
Hi,
We have replication setup on a sql server 2000. We encountered issue that the distribution agent goes down (distrib.exe stop running) in the event of network connection broken. We would like to know:
is this the expected result that the distribution agent will go down in the event of communication failure between the distribution server and subscriber server?
if not, is there a way to programmatically control and restart the agent?
Is there any sp in SQL server which can monitor the replication communication error message?
is there any sp in SQL server which can be run to restart the agent?
For the best practice, what do you think we can do to achieve an €˜event-driven€™ kind of mechanism so when an communication breaks, the agent can be restarted by the triggered event (or at least a simple way to automatically restart)?
Thanks
Bill
Hi
I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)
This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"
I have made the following configurations:
The user group (windows group) that the user belongs has the following roles in msdb :
db_dtsadmin
db_dtsltuser
db_dtsoperator
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
i have made a proxy to sql server agent which has the following subsystems :
"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.
Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.
Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.
Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.
Hi friends,
Any idea about mass mailing system using SQL Server .Pls get back to me.
thanx and regards
Chinmay
Hi Everyone,
I am towards the end of setting up transactional replication but I am getting stuck.
I don't know if I am going about this in the right way but here is what I did:
- Setup Distributor
- Setup Publisher for Transactional Replication with updatable subs.
- Setup Subscriber with PULL Subscription
- Updated a record on Subscriber
Now I am getting stuck with setting up a job with SQL Server Agent. What do I do to get it to synchronize the changes? I tried a few different kinds of steps (e.g. Replication Distributor) but got errors. I have researched online and can't find anywhere a detailed description of how to set up the job to effect synchronization.
What am I missing?
I'm trying to set up SQL 7 for replication, so I set SQLAgent to start automatically, same as SQL Server itself. SQL Server starts fine, but SQL Agent "fails to connect in a reasonable time" according to the Event log (3000 milliseconds).
Can you suggest how I go about fixing this? They both are using valid logins, so I don't know what else could be wrong.
Advice appreciated!
Arthur
Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..
Executed as user: mydomainmylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.
How come it loses the path to the file when I dont run it directly?
Cheers
Mick
I am creating a report to be used to print mailing labels. Size 1" x 2 5/8" (3 columns of 10 labels). I seem to have everything but the vertical spacing working properly. I have used a rectangle set to the size of the labels which contains a list box containing text boxes containing expressions to pull in the fields. Some of the street addresses are two lines with the majority being only one line.
I have joined the two fields containing the street addresses and inserted a new line command so each field will print on its own line but yet be contained within the same text box. Everything except the text box containing the street addresses are set to not increase or decrease. My problem is that on the pages of the report where labels vary between the one and two line street addresses the labels tend to creep down which in some cases causes data to be printed over two labels. Any ideas on what I can do to format these to print properly?
Thanks
I have read many threads on this issue & tried multiple solutions to no avail.
Here's my environment: SQL 2000, Logged directly into server via VPN (so it should be like I am physically at the server).
I've set up a DTS Package that executes an Access procedure. The package runs fine when I execute manually. When I execute via schedule, it fails on the error "ActiveX component can't create object: 'Access.Application'. The actual statement is:
Set objDB = CreateObject("Access.Application")
I know that Scheduled jobs default back to the SQL Agent permissions. I have reset SQL Agent to use my Windows NT account login so it should have the exact same permissions, etc. that I have when I execute manually?
Any help will be GREATLY appreciated!!!
Thanks mucho,
Brian
I have a job scheduled to run daily every hour. If I log on to the server the jobs runs sucessfully but if I log off the job fails. I have scheduled a SSIS package to run as a job.. pLease help...
I am using SQL Server 2005. and a proxy account to run the job. I treid to run the job as One Time only and it runs sucessfuly.
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
Hey everyone --I've been having this problem for a couple weeks and was hoping maybesomeone can help out a little bit.Synopsis:This SQL agent job contains code to perform a full database backup withverification and is writing to a local disk array. I typically also usesome code for reporting - declaring variables and inserting them intoa linked server database that I use to monitor all my database backups.I have tested and used the code repeatedly and am confident it works.I have ran the code through Query Analyzer and it completes withsuccess.I have removed reporting code & used only the backup statement & itfails.I have 4 other Agent jobs doing other various tasks that function asexpected.I have tried deleting & recreating the job & it still fails.When I enable logging the application log has only one entry for SQLAgent:Event Type:WarningEvent Source:SQLSERVERAGENTEvent Category:Job EngineEvent ID:208Date:1/4/2005Time:9:36:00 AMUser:N/AComputer:ComputerNameDescription:SQL Server Scheduled Job 'DBName Full DB Backup'(0xA4B14FB5A5DE964B8340423E4AC4B67B) - Status: Failed - Invoked on:2005-01-04 09:36:00 - Message: The job failed. The Job was invoked byUser domainUserName. The last step to run was step 1 (Step 1).Any help or ideas would be greatly appreciated. I'm racking my brainhere and for some reason just not able to think of what the problem is.ChaddJoin Bytes!
View 9 Replies View RelatedOkay, 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.
Hello,
View 4 Replies View RelatedHi,
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..
Is it possible for to send an email if a job fails as an alert? Can the email tell me which step failed?
I know how the code to send an email, but unsure how to trigger this for an agent job failure.
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
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
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.