Modified Connection In Package Cionfiguration - Sql Server Agent Job Refuses To Run
Jun 18, 2007
Hello,
Here is the following mind-numbing problem I have (and wished I did not have to experience)
A set of 2 SSIS packages is scheduled to run in a sql server agent job on the same server. Both packages use an environment variable that point to a package configuration file. In this file there are 2 connections, one to a sql server with a sql server user id and passwordn, another to an AS400 DB2. Both packages are deployed on the same server in SSIS server under MSDB sql storage, with package protection level set to 'rely on server storage and roles for access control'.
Today the connection to the As400 needed to change, it is now connected to another AS400 server. The packages have been modified to use the new connection. In the configuration file the old connection has been commented out and the new connection string was added, the connection itself was given a new more meaningfull name in the packages.
Running the packages from visual studio 2005 works. After testing I have deployed the packages to SSIS server in MSDB storage.
Now when I start the sql server agent job that runs these packages, the job quits with an error, in the history I see an error message that it failed to connect to the sql server with the given sql server user account.
When I the step in the sql server agent job properties for both packages, under the Tab 'Data sources' I see that it is using the new AS400 connection. I can also see the connectionstring for the sql server with the user id (but no password).
To make it possible for my packages to run (the users are waiting for the data) I have solved it like this:
- under the 'configurations' tab I have added the name of my package configuration file.
- i did this for both packages in both job steps
when I run the job , it works without problems.
Now , my question is: I have hardcoded the pathname for the package configuration file in my job. Instead of the package using the environment variable to find th epackage configuration file. I would prefer my packages , when in a sql server agent job to also use the environment variable. What can I do to make this happen?
When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.
The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.
If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?
I have a couple of queries that i run on my server but i need to automate them now. I have created a new job in the sql agent jobs and set up my steps accordingly.
My queries all run in a query window without trouble and they also parse in the command window of the sql agen job-step screen. When I run them manually, it fails with the first query moaning about my variables etc etc.
Is there some sort of limitation that i am not aware of or something?
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:
It seems there a lot of problems running SSIS packages under the sql agent. I have read the knowledgebase articles regarding permission issues etc but I still can't get my job to run. I can run any package as a job apart from a package that connects to an external database via an odbc connection. Has anyone had any luck with this and can let me in on the secret.
We have a large number of SSISDB packages running happily, connecting to our SQL Servers using ADO.Net or Sql Native Client, making their connection using NTLM. (We don't have our SQL Server SPNs correctly configured to support Kerberos).
The SSISDB packages are hosted on and run on a dedicated SQL server, different to the SQL Servers they are connecting to.
Very occasionally, the connection attempt is made using Kerberos instead of NTLM, and the connection attempt to sql server fails. (This is going by the Windows Security event log, which reveals a Kerberos login - a successful one at the Windows level - at the precise time that the calling agent job is informed of a connection timeout and fails, approx 23 seconds after the job starts).
The correct configuration of our SPNs is something we may wish to look into for security best practice, and would of course fix this. However, that may not be my decision to make.
I'd like to know if there's a way to pass parent package parameters to a package executed by SQL Server Job Agent? It appears that sp_start_job doesn't have any variable that could accomodate this.
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
Today, my server began refusing connections for no apparent reason. I'm getting 2 different errors. 1. Server rejected the connection; Access to the selected database has been denied. (from one remote DSN) 2. Client unable to establish connection. (from another DSN)
It seems as if my server has lost it's mojo!!?? I have no clue as to why this is happening all of a sudden. Please help.
thanks,
Joe
Also, is there a better way I can trace my SQL Server ODBC errors (other than through Profiler)?
We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.
Kindly give respond to this if any one has worked on this.
I'm using SSIS package to extract text file, and load into SQL Server. I test the SSIS package import manually, it works. Then I'm using SQL Server Agent to set up schedule. According to the log, the job agent work at the scheduled time, yet job agent throw out the error message.
Message1: The job failed. The Job was invoked by User sysAdmin. The last step to run was step 1 (Extract Data).
Message2: Executed as user: SQLSERVERsysAdmin. The package could not be loaded. The step failed.
Now I only have 1 step and set up "on success action" then "quite the reporting success". Any clue? Thanks
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.
I create a package in SSIS and I can run it without any problem in BI studio. However, if I deploy it to a sql agent package and run it thru sql agent job.. I keep getting an error one of lookup transfomation is invalide..
I don;t understadn I can run the package in debug mode but why i keep getting an error when I deploy to a sql agent job?
I would like to find out, if there is something I can implement/or should I need to implement to avoid running two concurrent processes of 1 SSIS Package. I mean by default, I will schedule the SSIS package to run utilizing SQL Server Agent. But How I avoid/prevent it to run concurrently or what do I need to setup where I only allow 1 instance of my package to run. Thanks.
Hi, I have been searching through the posts hoping to find an answer to my problem. Most errors and problems seem to deal with Agent not being able to run packages because of permission issues.
My problem has to do with SQL Server Agent not finding the package to run. I have a proxy with a high enough level of permissions (SqlAgentOperatorRole).
The error message I get is:
Executed as user: ___/____. The package could not be found. The step failed.
The package is in a folder on our network. When I create the step I use the browser to go find and select the package. So why does it not find it at runtime? I can run it manually
My group is using 2005 for the first time. Any help you can provide would be very much appreciated.
When I try to run a DTSX package from SQL server agent, I get this error message:
Started: 3:59:42 PM Error: 2007-08-13 15:59:42.39 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSroperty" 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: 2007-08-13 16:00:00.98 Code: 0xC001602A Source: CFASerialNoExport Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-08-13 16:00:00.98 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The packag... The package execution fa... The step failed.
All my other packages run fine. This one contains an FTP task and will not run, although it will run if I execute it through the visual studio.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'I:MyFolderMyRptsMyApplicationMyDatabase.mdb' 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.".
The above path "I:...." is mapped on the server that SQL Server is running on.
This package runs with NO problems in MS Visual Studio.
I've been looking everywhere for a hint on how to tackle this, but can't get it to work.
I have an SSIS package that I am trying to run from SQL Server Agent.
I have been able to run it fine from the IDE, and from within the Integration Services system on my database server. However when I try to run the package via SQL Server Agent I get the following error: "Executed as user: MyDomainSQLServer. The package execution failed. The step failed."
The login name is the SQL Server service account, which is a domain account on our domain. The package is set with EncryptSensitiveWithPassword and the password is supplied on the command line via the /DECRYPT flag.
I am thinking that maybe there is a permissions problem with the service account, but I can't find any detailed information about what actual permissions this account requires. I have tried expanding its permissions, but continue to get this error.
How should the MyDomainSQLServer account be configured?
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???
I have a problem running an SSIS package in a SQL Server job. The package runs fine if I run it from the MSDB location, but if I try to run the job it fails. The job is set to Run as: SQL Agent Service Account. The SQL Service Agent service runs as a domain user SQLExec. I have logged in as this user and run the SSIS package and it runs fine, but if I create a job with only this step it fails. There isn't much information about where there is a problem. Any ideas or ways to troubleshoot this problem would be very much appreciated.
I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.
I success to launch the package but the execution failed.
I try the same job launching from the server : it doesn't work either.
So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).
Why is it working with this utility and not with a job ?
I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)
Okay, i've got an ETL package which works flawless, we've created a scheduled job for it which also worked. This ETL accesses a flat file located on a server on a different location in the same domain.
Last week we start a migration to a new network, the location where the flat file is stored has already moved to the new domain but the SQL server agent is still on the old domain. There is a trust between the 2 domains, the network user from the old domain has been added on the server in the new domain where the flatfile is located.
If i run the etl package manually it works flawless, now if i run the etl using the SQL server agent it won't run anymore.
I get the following error:
Executed as user: OIdDomaindwh. ...n 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:15:00 Error: 2007-10-02 21:15:02.10 Code: 0xC001401E Source: Rolmer_Product Connection manager "Flatfile Product" Description: The file name "\fs-cha-fs01dwhproduit.csv" specified in the connection was not valid.
Don't tell me its a problem related to the user that the SQL Server Agent is set to because its set to OldDomaindwh which is the same user i use to login on the server where i can manually run it without problems. The problem only occures when i run it using the SQL server agent which uses the same user to execute packages.
This is a very difficult problem i doubt anyone here will be able to crack this nut, but if they do i'll be very impressed because it would require a real smart network admin with SQL experiance to figure this one out...
I have a need to force a package to run using the 32-bit runtime from the SQL Server Agent. The machine is a x64 unit. I'm having to use an ODBC driver to extract data from our ERP package that will only run in 32 bit. Any help would be appreciated.
I created a SSIS package using VS2005 with the "ProtectionLevel" set to "DontSaveSensitive" . I executed the package and it completed without an error. I then built the package and deployed it to the SSIS inside the folder "MSDB". I run the pakage in SSIS and it worked perfectly. Then I created a job in SQL Server Agent and have it run in a per-set schedule. It failed to run withthe following error:
Message Executed as user: SRVSOUDB01SYSTEM. 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: 10:11:02 PM Error: 2008-05-12 22:11:03.17 Code: 0xC001401E Source: STDM Connection manager "Target_AS_STDM.abf" Description: The file name "\Svmppodb01OLAP DataSql DataBackUpAS_STDM.abf" specified in the connection was not valid. End Error Error: 2008-05-12 22:11:03.17 Code: 0xC001401D Source: STDM Description: Connection "Target_AS_STDM.abf" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:11:02 PM Finished: 10:11:03 PM Elapsed: 0.422 seconds. The package execution failed. The step failed.
The error mentioned the step in SSIS Package that requires to copy the backup of the OLAP database file "AS_STDM.abf" from the Source Server location (local processing server SRVSPOYDB01) to the Targer Server location (remote production server SVMPPODB01) "Svmppodb01OLAP DataSql DataBackUpAS_STDM.abf". This step worked in the SSIS(SRVSOUDB01) when executing there. Why it is not working in Sql Server Agent(SRVSOUDB01). It also works in VS 2005.
when I try to run a package as Job I receive the following error
Executed as user: SITAsqlserveragent_svc. The package execution failed. The step failed.
Has someone an idea how I can get more detailed information about WHY the job failed? Tha package was created in BIDS and imported into MSDB. Running the package from the Integration Services context menu works, but starting it as a job from SQL Server Agent fails. Any idea or hint what the reason could be or how I can get more information?
Hello All, My database application has certain stored procedures which are scheduled to run at certain predefined interval (say once everyday). Now to implement this logic I have two options with me, first being to use the EXECUTE SQL TASK of the SSIS package and schedule it using the windows scheduler and the other is to use the SQL Server Agent Jobs and schedule it there itself. Now the current implementation is using the SSIS packages and I even know that the second approach of using the SQL Server Agents Job is better. The only thing I want to know is whether it makes sense to invest time in converting my SSIS packages (20 in all) to SQL Server Agent jobs or not. Can somebody briefly explain or give some pointers regarding the performance benefits of Agent Jobs over the SSIS packages.
