SQL Server Agent And X64 Using OraOLEDB.Oracle.1: Dtexec From Cmd Works, But Job Fails
Aug 16, 2007
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.
View 9 Replies
ADVERTISEMENT
Mar 18, 2008
Hi,
I am executing a SSIS package using dtexec. 64 bit version of dtexec works fine. But when i use 32 bit version of dtexec, it fails. i have local admin rights. Following is error description. Please help.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:24:30 AM
Error: 2008-03-18 09:24:32.54
Code: 0xC0202009
Source: IMALCRM Connection manager "IMAL SRC"
Description: An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC020801C
Source: Load Fund Detail V_FUND_DETAIL [16]
Description: The AcquireConnection method call to the connection manager "IMA
L SRC" failed with error code 0xC0202009.
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC0047017
Source: Load Fund Detail DTS.Pipeline
Description: component "V_FUND_DETAIL" (16) failed validation and returned er
ror code 0xC020801C.
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC004700C
Source: Load Fund Detail DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-03-18 09:24:32.54
Code: 0xC0024107
Source: Load Fund Detail
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:24:30 AM
Finished: 9:24:32 AM
Elapsed: 2.078 seconds
View 5 Replies
View Related
Jan 11, 2007
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
View 3 Replies
View Related
Apr 2, 2007
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
View 10 Replies
View Related
Jun 7, 2006
Good day everyone....
I have installed the OraOLEDB.Oracle Provider on 3 different servers, and have made a link server on all three.
On two of the machines, I am successful in retrieving data from the Oracle database. But, on the third machine, I receive this error:
Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "employee_prod" reported an error. The provider did not give any information about the error.
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "tims2_prod".
On the machine which is generating the error, I can use SQL*Plus to connect to the remote database. I can also connect to the database using the "ODBC Data Source Administrator" when I create a system DSN. It would appear that the driver is installed and working correctly, but SQL Server can not access it.
I have also uninstalled/installed serveral times.
All of our servers are running W2003 Standard X64 with SQL Server 2005 Standard 64-bit Edition.
Does anyone have any suggestions?
View 16 Replies
View Related
Jun 7, 2007
Hi all of you,
Primary platform is Framework 2.0 running over XP.
I've got an issue with a SSIS package which uses Oracle Provider for OleDB:
Test connection failed because of an error in initializing provider 'OraOLEDB.Oracle.1' is not registered on local machine
Nevertheless, MS Ole DB Provider for Oracle works fine. Let me know where am I failing.
Thanks in advance,
View 10 Replies
View Related
Apr 4, 2006
Hello,
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.)
What options must be checked and modified ?
Thanks for your help !
View 3 Replies
View Related
Jun 11, 2007
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???
View 7 Replies
View Related
Feb 1, 2007
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.
Thanks, john
View 3 Replies
View Related
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
Jul 18, 2005
I have an ASP.NET webform:This connection works: "Server=localhost;uid=sa;pwd=;database=pubs"but this connection DOES NOT work: "Server=dnrsqlt1;uid=sa;pwd=;database=pubs"dnrsqlt1 is a sql server my network.Do I have to do something to users ASPNET or IUSER_Machinename on the remote machine
View 6 Replies
View Related
Dec 21, 2000
i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..
with regards,
vijay.
sql server 7.0 on winnt
pl/sql code on oracle 8.0
View 2 Replies
View Related
Jul 7, 2015
I have two calls to stored procedures that in an SSIS package fails silently. They are simply not executed in production but works fine in test, nothing happens and the sql server agent reports that everything has gone just fine.
In test they have 1 server with db A and B. No issue here.
In prod they have 2 servers with db A and B. On server 1 sql server agent executes a job that includes an SSIS package that on server 2 runs a couple of sp's. That user is db owner on server 2 db B and yet nothing happens. The sp's are not executed.
If I in prod run the job manually then it works, but not when run with the sql server agent account that as said is even db owner.
View 2 Replies
View Related
Sep 26, 2006
The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0
View 1 Replies
View Related
Mar 13, 2008
to DTEXEC from a agent job , what will be the permissions that are needed ?
I guess that can be SQL Server login with admin permissions will be fine and we don't need a proxy setup.
View 1 Replies
View Related
Sep 6, 2006
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
View 6 Replies
View Related
Sep 13, 2006
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.
View 2 Replies
View Related
Jul 5, 2006
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.
View 6 Replies
View Related
Aug 28, 2007
I've worked for awhile to get an SSIS package that connects to Oracle to succesfully run via DTExecUI from a 64 bit SQL Server. I completely understand that DTExecUI runs in 32 bit mode, but I need to initiate the package from a stored proc so I need to be able to utilize the 32 bit DTExec from either a xp_cmdshell call or from an sp_start_job (thanks to MatthewRouche for the suggestion). Both Ora92 and 10g were installed on the SQL Server.
Here's what I've tried unssuccessfully
1. SQL Job - Just calling the package from an SSIS step. I wouldn't expect this to run because it is 64 bit DTExec.
2. SQL Job - A job that calls the 32 bit version of dtexec from cmdexec. Here is 2 examples I have tried, the first is because I came across several postings saying that there is a problem with the "(x86)" for the Oracle driver.
\server_nameE$ProgramFilesLegacyMicrosoftSQLServerX8690DTSBinnDTExec.exe /DTS "PackageName" /SERVER "SERVER_NAME" /SET Package.Variables[PLT].Value;"8H" /CONFIGFILE "\SERVERConfigFile.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
\server_nameE$Program FilesMicrosoft SQL Server (x86)90DTSBinnDTExec.exe /DTS "PackageName" /SERVER "SERVER_NAME" /SET Package.Variables[PLT].Value;"8H" /CONFIGFILE "\SERVERConfigFile.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Both of these give me the error below:
System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
I'm assuming that it is running in 32 bit mode because this was at the beginning of the job history log:
Executed as user: "XXX". ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved
3. I then tried to call the package from a batch file on a file server that had integration services installed on. Got the same error as above.
I'm obviously missing something if I can get it to work in DTExecUI but not DTExec 32-bit.
Any ideas? Below are some postings I have already visited several times:
Windows 64 bit and Oracle
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=358365&SiteID=1
Connecting to Oracle on 64-bit (x64) machine
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1
One paragraph that stands out on this posting is:
Find the location of the application that is generating the error. Check the path to this location and see if it contains any parenthesis. If so, you must relocate the application to a directory without any parenthesis in the path.
This didn€™t work for me, unless I was doing it wrong.
Error retrieving numeric data from Oracle 10g on 64 bit Itanium SSIS server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1183296&SiteID=1
Connection Problems to Oracle Database
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1307460&SiteID=1
If this posting was complete, it looks like what I was looking for.
View 9 Replies
View Related
Dec 7, 2006
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 Related
Sep 29, 2000
Two 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!!
View 1 Replies
View Related
Jul 5, 2006
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
View 1 Replies
View Related
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
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
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
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
Jan 10, 2008
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.
View 5 Replies
View Related
Sep 6, 2006
Im getting the following error when I try to export from Oracle 9i and import into SQL Server 2005 using Oracle and SQL OLE DB Provider's respectively.
Cannot retrieve the column code page infor from the OLE DB Provider. If the component supports the "DefaultCodePage" property, the code page from that property value will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
I know there is a way to change the property settings for the OLE DB Provider in the Data flow section of the development studio BUT ...
1) Is there a way to change this outside of the development studio?
2) I can't create the package to setup in the studio because even when I uncheck execute immediately and check save package, it still fails and never creates anything.
Any help is much appreciated
Thanks
Scott
View 2 Replies
View Related
Apr 5, 2007
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 ?
View 3 Replies
View Related
Dec 21, 2007
Is there any built-in way of kicking off a job on SQL Server 2005 Agent whenever a package/job completes in Oracle?
Are there any (Triggers? Msft queue? Event Notification?) mechanisms to automate running a job on the SQL side?
Any article or knowledge articles would be appreciated also.
If not are there any built-in stardardized polling techniques? Or are there any timers in SSIS?
That way I can delay executing a child package until a certain record has been inserted into a control table in Oracle.
I don't want to write an inefficient for loop that blocks all other processing on the server and iterates once every second.
Thanks
View 1 Replies
View Related
Jun 14, 2006
Hi - I hope some of you can help me with this problem.
I'm trying to run the snapshot agent for 77 published tables, but the agent stops suddenly uninterrupted. It stops in the middle of taking a snapshot of a table containing 81,719,531 rows. The error message is pasted below.
Message
2006-06-14 05:33:33.53 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57200000 total rows copied)
2006-06-14 05:34:12.57 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57300000 total rows copied)
2006-06-14 05:34:51.23 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57400000 total rows copied)
2006-06-14 05:35:29.99 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57500000 total rows copied)
2006-06-14 05:36:09.82 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57600000 total rows copied)
2006-06-14 05:36:49.38 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57700000 total rows copied)
2006-06-14 05:37:28.56 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57800000 total rows copied)
2006-06-14 05:37:35.36 [89%] The replication agent was shut down in response to an abort request.
Does anyone know what is causing this error and how I can possibly solve it?
Best Regards,
JB Plum
View 14 Replies
View Related
Dec 18, 2007
Hi
I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails.
SQL Server agent is running with same account "xxx"
DTS connects to SQL Server with sa authentication
Job owner is same account "xxx"
Job error log
Executed as user: DOMAINNAMExxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed.
I copied the DTS to another one and scheduled it
This time I got the error log
Executed as user: DOMAINNAMExxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed.
Please help!
Thanks in advance
View 3 Replies
View Related
