SSIS: Oracle Import Works, But Not When Scheduled
Dec 26, 2006
Hi,
I am running SQL Server 2005 and have built a simple SSIS package to import data from an oracle database to my SQL Server 2005 database. When I run it in SSIS, it works and it imports just fine. When I schedule it, it gives me problems. Help!
It might be a problem with Oracle Provider client I installed. Is there a client version I can download and install? the one I downloaded from oracle doesn't work. I bet i did something wrong though.
Here is my version:
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
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: IDE Standard
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00
Thanks,
Ryan
View 5 Replies
ADVERTISEMENT
Jun 2, 2006
I'm new to DTS packages, but managed to create one that successfully empties a table in SQL Server then imports data from a Foxpro file on another server into it. It runs fine if I execute it from DTS, but fails if I schedule it to run in SQL Server Agent (using the "Schedule Package" option in DTS). I think the relevant portion of the error returned when the job fails is:
Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'hrpersnl.dbf' does not exist.
The file does exist. I also tried to execute it from a stored procedure, but got a similar error. Any thoughts on why it runs one way but not the other?
TIA
View 14 Replies
View Related
Oct 10, 2007
We have a Process Task component setup in a couple SSIS jobs to call a command batch file to support transfering a file via Secure FTP to other servers and the process works fine if we start the SQL Agent job manually, however when the job is started via the scheduler, it fails with an exit code of 4. Even though there is a proxy setup on the agent job, is there a different user account being invoked by the scheduler??? We're on 2005 SP1 Hotfix 1 (2153). Thanks
Some more info...have found that if we leave a login session open on the server (login is the proxy account) the process works. It appears the issue is associated with a need to render/create a command window for the command line/batch process to run in and without an active windows session it fails....would seem to be that a product setup to run on a server in a batch mode would be able to work without this...is this the case? if so, how? Thanks.
View 11 Replies
View Related
Sep 27, 2007
Hi msdn forum members,
I need to import data from another server using Express edition every minute.
So I ran the import wizard, and I saved the packaged on the hard drive.
Then when I try to make a new job and new schedule for scheduling, I can't seem to find SQL server integration service option for the Type.
does anybody have any idea why I can't see SSIS there or had the same problem??
Or is there another way of importing data every minuite other than this?
BTW my server is 2005 standard edition.
Thanks in advance,
Mike Kim
View 10 Replies
View Related
Apr 19, 2007
I am getting this error when connecting to Oracle db. I tried using Microsoft OLEDB provider for Oracle it give me error and tells me the error could not be retrieved from Oracle. When I try the Native OLDDB provider for Oracle I get
Warning at {0F67F2FA-E3F8-4F44-93EC-47D513A34FD4} [Orcale Database WPHP2 [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property 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.
Error at Copy DSS_SJV_Volume_History [DTS.Pipeline]: The "output column "COMP_UNIQUE_ID" (2007)" has a precision that is not valid. The precision must be between 1 and 38.
What do I do !!!!!!!!!!
Thanks
Shabnam
View 1 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
Jan 3, 2007
Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results.
My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info.
I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!
View 4 Replies
View Related
Apr 17, 2007
I have scheduled a job in Management Studio, but it doesn't work. However, when I run it maually in Visual Studio it works. I have connected an outside server by mapping it to mine. Maybe this is the problem?
I have also tried to configure a linked server, but I cannot find out how to connect my SSIS package to the linked server.
Can anybody help me?
Thank's!
View 8 Replies
View Related
Feb 6, 2004
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Help!!!
View 3 Replies
View Related
Sep 25, 2007
Hi
I have created a package that extracts data from an Oracle 10g database into a database on SQL Server 2005 SP2.
This package run fine when run inside BIDS and also if run from DTExecUI or DTExec
(This is done while logged in using the same domain account that runs both the SQL Service and the SQL Agent Service. This account is also a local admin on the box.)
When I try to schedule this with SQL Server Agent (running under the SQL Agent Service Account) it returns an MSDAORA error
"Oracle client and network components were not found"
The same error occurs if I try to use CmdExe step to try and execute the DTExec command.
The interesting part however is that I use a lower privaleged proxy account then
(a) Executing the package directly still fails but
(b) Running a DTExec command will succed
Can anyone provide an explanation for this behaviour. While I can work around this "feature" by using the DTExec and a proxy account it does make the packages harder to configure/debug and defeats many of the configuration advantages provided by SSIS
TIA
Nadreck
View 1 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
Jan 29, 2008
Hi,
I have been fighting this problem for several days now.
All I need to do is run a query against a table on mssql 2005 server and update a table (only update) in Oracle
Here are some of the things I have tried:
1) from a forum tip, I set up a dataflow: OLE DB Source (MS SQL) -> Derived Column -> OLE DB Command (Oracle)
- I get "Error at Data Flow Task [OLE DB Command [2850]]: Columns "NyNamn" and "id" cannot convert between unicode and non-unicode string data types."
2) I have tried a Source - OLE DB Source (MS SQL) -> Derived Column -> OLEDB Desination (Oracles OLEDB)
- Error when trying to preview:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Destination [2953]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
Error at Data Flow Task [OLE DB Destination [2953]]: Opening a rowset for ""APPINV2"."OWNERS"" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
3) I have tried a Source - OLE DB Source (MS SQL) -> Derived Column -> OLEDB Desination (MicrosoftsOLEDB for Oracle)
- Error when trying to preview:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Destination [2953]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Unspecified error".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Data type is not supported.".
Error at Data Flow Task [OLE DB Destination [2953]]: Opening a rowset for ""APPINV2"."OWNERS"" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
I have full rights on the oracle database with the account used, i can connect with toad with no problems.
To me, this should be a simple task. has anyone got any advice for me ?
Thanks
View 5 Replies
View Related
Feb 21, 2008
I want to schedule a job to run nightly where a remote mysql database is imported into a local sql server db. I ran through the wizard for scheduling a job, previewed my table data, and viewed the scheduled job in the DTS queue. However, the job never runs. I can't figure out why.
Anyone have any idea why? Is there a step-by-step process somewhere for doing this?
Thanks,
C
View 1 Replies
View Related
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
View Related
May 2, 2007
Hi,
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
Any caveats houghtscomments on doing this?
Thanks,
JGP
View 1 Replies
View Related
May 10, 2007
Hi,
I'm new to SSIS after spending a long time with DTS. I am having a difficult time getting a SSIS package to run as a scheduled job, even though the package works fine when run manually.
I read up on these problems and have completed all the neccesary instructions here:
http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp
but the package still refuses to execute through SQL Agent. The only error message I get in the History Log is:
"The package execution failed. The step failed."
I am running the job as the new Proxy user that I set up but it will not execute when scheduled. Can anyone offer any advice please?
Thanks.
View 4 Replies
View Related
Sep 14, 2006
Hello all,
I was very familiar in creating quite complex DTS packages in SQL2000. Now with 2007 that is all gone. I looked at the command line utility and it's UI counter part but these are just for executing. I keep getting references to the SSIS designer but I can't find where it is.
Can someone point me in the right direction?
Thanks,
Jesse
View 6 Replies
View Related
Mar 29, 2006
i am using sql server express edition.
i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.
what is the fastest option? some of these tables hold over 1m records.
so ideally i would like to schedule a refresh or update job outside busy hours.
I have heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.
many thanks
luzippu
View 3 Replies
View Related
Dec 7, 2007
Hi All,
I'm trying to import from Oracle Rdb database into SQL server 2005 via ODBC without much success.
I've installed the correct Oracle Rdb driver and verified it all works ok by importing in to Access.
Using the import wizard within SQL mgt studio and the following connection strings I get a password error
Dsn=XRS;Driver={Oracle RDB Driver};sid=system
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
------------------------------
BUTTONS:
OK
------------------------------
I know the username/password combination to be correct.
Is SSIS the way to go. I've had a quick look at this and setup the ODBC connection but the column
mappings box reports the following...
error at data flow task [datareader source[1]; cannot aquire a managed connection
from the run-time connection manager.
Anyone point me towards any examples of reading in data via ODBC and storing in a SQL server database.
Should point out that the source is not Oracle RDBMS (7,8 or 9) but Oracle Rdb which is a
completely seperate product.
Thanks in advance
Dave
View 10 Replies
View Related
May 2, 2007
I am using Import and Export wizard and seeing a very weird behaviour.
I am able to choose 'Microsoft OLE DB Provider for Oralce' and provide server name, username/ password. The test connection results in a success. However when I try to move to next window/page by clicking next I get invalid oracle error ora-01017 - invalid username/ password.
what is going on here?
View 1 Replies
View Related
Apr 4, 2008
Hi all,
I recently encountered what will appear to me as a very strange problem with my packages involving data import from an Oracle 10g database. The packages are simple data imports which select and insert data from Oracle to SQLServer 2005 tables.
Till recently, the packages ran without any problems but a day ago, these packages starts to fail when nothing was changed in them. They will run for about 8 minutes, at which approximately 400,000 records will be selected and processed. After that these packages will fail. The failure rate is inconsistent in the sense that some of these packages will fail while some will pass, depending on the 'mood' of SSIS.
These packages are using Oracle's supplied OLE DB drivers and have been running well since day 1. Should I be concerned with Oracle terminating connection after 'x' minutes something like that?
I'm absolutely puzzled with these behavior and will appreciate it if someone can point me to the right direction. This server is a 64 bit server and SQL Server is installed as 64 bit as well.
Thanks in advance!
Nestor
View 6 Replies
View Related
Mar 6, 2006
I have a column in an Oracle source system with data type NUMBER(38,2). The value "-0.01" is causing problems when trying to import into a SSIS data-flow.
The only way I can import this into my data-flow is by using a Datareader connection manager using the ODBC Data Provider. My DSN is using the Oracle ODBC driver.
If I try and use the "Native OLE DBMicrosoft OLE DB Provider for Oracle" I get an error: "The data value cannot be converted for reasons other than sign mismatch or data overflow"
Judging by this post: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html there aren't really any other combinations to try that will bring my data in as I want it.
I don't want to use ODBC though as:
Its an old technology
Its slow
I have to deploy an additional DSN.
Can anyone tell me why the other options don't work? Why does Microsoft OLE DB Provider for Oracle have a problem with "-0.01"?
-Jamie
View 2 Replies
View Related
May 5, 2006
Hi,
Can we allow SSIS to be scheduled via SQL Agent. Is there any security concerns.
Thanks & Regards
Shri.DBA
View 4 Replies
View Related
Jan 22, 2008
What are peoples opinions on using SSIS as a "central repository" and replacement for all scheduled tasks. Example, we have a bunch of servers which we have installed services which we have written. Currently we have scheduled tasks on each machine to stop and start the services. One of my collegues is using SSIS to run a system which runs tasks on multiple machines by remotely running programs on other machines via scheduled tasks and then collects the data and puts it into a database.
He's now pitching the idea that we remove the scheduled tasks on each machine and start and stop our services via SSIS so that it's centralized. In addition, we can also check for holidays in our database before starting services. Since it doesn't seem like SSIS was meant for this type of use, I'm weary of using the tool to do something it wasn't intended for.
Any opinions? I'm also worried that the learnnig curve for everyone is going to be too high.
View 11 Replies
View Related
Nov 21, 2006
We run 4 nightly scheduled jobs on our sql 2005. All use the same domain account and were running fine until a few days. Then the SSIS job started failing when run via the scheduler. (It does run when I execute it manually). The sql 2005 History simply says the package failed and was "Executed as <Domain-User>", the same user that successfully ran the other 3 scheduled jobs.
SQLAgent.out shows SQLServer Error:: 2812 Could not find stored procedure: msdb.dbo.xp_sqlagent_notify.
Relevant?
Any ideas wherewhat to look for next?
TIA,
Barkingdog
View 1 Replies
View Related
Jul 12, 2007
Quick question - What are the different ways of executing SSIS Package besides Scheduled Job?Does SSIS support command line execution (DTSRUN command supported in 2005)?
The reason I asked was that Scheduled Job triggered SSIS always error out. But when I call the same SSIS in Visual Studio, it executes beautifully. I wonder if there's some reason behind the error when calling from Scheduled Job.
Appreciate any feedback,
-Lawrence
View 16 Replies
View Related
May 15, 2007
I would like to loop through a SQL Server table that contains the paths to all the reports(SSRS) we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this? Anyone Please Explain how to do it . Or either Explain me how to run a Report(SSRS) from SSIS
View 3 Replies
View Related
Mar 3, 2000
Does anyone know how to import data from an Oracle view (on Unix machine) to the tables in NT/SQL server 7.0? At least point me to the right docoment resource, if available. Thanks a lot.
wendy
View 2 Replies
View Related
Mar 10, 2005
We will need to routinely import only changed data from an Oracle data base into a SQL database.
So we need an agent that will
1) Compare data in both databases (From disparate tables)
2) Import only that which is changes or new.
I am new to SQL server administration and am looking for a best practice method that we can be run on a weekly basis.
I am open to using third party software solutions, but would prefer a native MS SQL 2000 solution.
Can someone point me in the right direction?
Thanks.
View 1 Replies
View Related
Jan 10, 2006
Looking for a faster method of moving data from SQL to Oracle.
I'm attempting to push a sql table into an oracle table (sql server 2000 oracle 7, 8, and 9). I have no problem doing this with either 'Oracle Provider for OLE DB' or the 'Microsoft OLE DB Provider for Oracle'. None of my data is being transformed so its a straight import. With the hardware I'm using it takes nearly 3 seconds to import 1000 rows. While this isn't too bad, I need to import upwards of 4 million rows and this results in unacceptable time results.
I do have an oracle script that imports the csv files of the tables, but I'm looking for an all inclusive sql solution.
Does anyone know of another method in SQL that I can use to push the data faster?
View 7 Replies
View Related
Nov 29, 2006
Now this is the first time i have tried this with SQL 05 I am trying to Import data from an Oracle DB. I have the connection info:
Xtx20xxx = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =urlpart1.part2.part3.com)(PORT = 1521)))(CONNECT_DATA =(SID = Xtx20xxx ))
)
I used the Microsoft OLEDB provider for oracle from the dropdown,
The username and password I have also. I tried to enter in the Xtx20xxx as the server name, clicked "Properties"
put in the username and password I can given and clicked "Test Connection". No matter what I get the infamous ORA-12154: TNS:could not resolve service name error message. I am sure I am missing something obvious here but I have absolutely no clue and all the googling in the world has not given me any help. I do have the Oracle 10g client tools installed on this workstation (running Windows XP Prof SP2 and SQL 2005 STD).
View 5 Replies
View Related
Feb 11, 2007
Hello friends,
I am working for a project. At my college I used to work with oracle. Now as requirement changes I have to change my database to SQL Server.
Is there any simple way one can suggest me?
View 2 Replies
View Related
Jun 11, 2008
Hi all. We recently migrated to SQL Server '05 from 2000 and I'm trying to learn SSIS by crash course.
I just wrote an SSIS for one of our nightly processes in Studio 2005. It built with no errors, and the solution and resulting package are saved on the server housing SQL Server.
I ran the package in debug and it completed 100% with no errors. However, when I scheduled the package in SQL Server, it starts and fails.
Now, I'm very new to SQL '05 and I'm trying to figure out how to log the SSIS jobs, but is there any way to find out why the scheduled packages fail?
I'm desperately needing help on this, and any help would be greatly appreciated!
View 3 Replies
View Related
