SSIS : Oracle As Datasource --&&> Issues With Numeric Datatype
Oct 17, 2007
Hello,
I have to develop a package to get some data from a 10g Oracle database and load it to a SQL Server 2005 database.
In my design I used Oracle Provider for OledDB as my source provider following Microsoft recommendations found here : http://msdn2.microsoft.com/en-us/library/bb332055.aspx
As I have to use a parametrized query against my Oracle database I create a variable with my query that I override at runtime with a script task to update the parameters values.
One of the return column is of numeric datatype.
To handle this known issue with Oracle sources I edited through the advanced editor my Oledb source component to manually correct the precision.
Everything works fine with one of my package.
So I repeat the whole process while designing an other one with the same purposes.
As for the previous one I first unit test every components and everything works. But when running the full package it raised an error code 0x80004005 Error while validating post executing phase of the component (sorry for the poor translation from french to english ...)
I open my dataflow where the component is and found no warnings or errors. I edited through the advanced editor my Oledb source component and found that there's an error on the precision scale of the numeric return column. The message warn me that the precision must be between 1 and 38.
I checked this column and in the "OLE DB Source output / External Columns" the precision is 0 though I previously manually corrected it.
I upadted again for a 18 precision as it is for this column in "OLE DB Source output / Output Columns".
But it never keeps my changes ......
I delete the component and recreate it to force the metadata to be updated but always the same thing, the precision scale for this column in the "OLE DB Source output / External Columns" never keeps my 18 value.
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
I am working on a project to write SSIS packages to update a SQL Server 2005 database with data from an Oracle 10g database. Unfortunately, our development ETL box is running 32 bit Windows Server 2003 while the production ETL box is running 64 bit Itanium Windows Server 2003. We have created SQL Server Agent jobs to execute all of our packages. All of our jobs run without error on the 32 bit development box, but in our preproduction tests they are failing in the 64 bit box.
In my testing, I've found that I can successfully pull character data from Oracle. For example, I can write a package that retrieves the data for the following query:
SELECT 'test' FROM dual
However, the following fails:
SELECT 1 FROM dual
I have logging turned on in my package. When the package fails, I typically see informational messages in the log up to the point of failure, but no actual error event logs are generated. The only error information I have is in the SQL Server Agent job history log, which contains the following:
Executed as user: SERVERNAMESYSTEM. The return value was unknown. The process exit code was -2147483646. The step failed.
I have also tried using the 64 bit version of the DTS Wizard to create a sample package, and observe the same behavior. When I run the DTS Wizard, queries that return numeric data cause DTSWizard.exe to exit and produce the following message in the application event log:
Event Type: Error Event Source: .NET Runtime 2.0 Error Reporting Event Category: None Event ID: 1000 Date: 2/1/2007 Time: 12:36:13 PM User: N/A Computer: SERVERNAME Description: Faulting application dtswizard.exe, version 9.0.2047.0, stamp 443f5e50, faulting module oracore10.dll, version 10.2.0.1, stamp 435841b0, debug? 0, fault address 0x00000000001e4910.Has anyone else experienced similar behavior, and know of a solution aside of wrapping all retrieval of numeric data in to_chars in our Oracle queries?
I'm designing a database that will not be that large (I would be surprised if it ever surpassed 50,000 rows across all tables), but will be accessed quite often, so I am doing my best to optimize its structure, such as doing 3NF, selecting appropriate data types, etc.
I have a few columns that will contain numeric data (such as an invoice number (from an external source) or location ID). However, one of my classes in college was about database design, and we were taught that if you won't be doing mathmatic computation on a field (such as the invoice or location fields I mentioned earlier), then you should use a string literal type (char, varchar, etc.)
Unfortunatly, the professor did not explain much as to why this should be done. From the standpoint of semantic analysis, these types of fields are probably more labels than they are numbers. However, I don't find that very convincing (or even helpful).
In short, my question is that given a column holding numeric data that isn't worked on in a mathematical sense, is it really better to mark it as a string literal than a number? Any articles or studies I can read relating to this?
I would think that comparisons would be faster with int, as well as data storage (though, as mentioned, that's not as big of a concern). Sadly, Google doesn't have many helpful resources. (Lots of stuff on char vs varchar, though.)
I read the topic from JROdden and this case is similiar but...
I got several varchar fields with values like 1.2 1.3 ... these I can covert with select CONVERT(dec(5,2), fieldname) as fieldname
In fact I also solved undefined- and NULL-values with. CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,
But now there are values like 1,4 and these ones neither CONVERT nor CAST will handle.
I tried the SELECT DISTINCT KMPAUSCHALE FROM extr_INTFIRMA WHERE (isnumeric(KMPAUSCHALE) = 1)
and get 0,40 0.25 0.30 and so on...
The error is: [Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))
I think the easiest way would be to insist on higher data quality but I also would like to solve this interesting challenge.
Thanks for any hints
By the way, I followed rudys link to http://rudy.ca/afdb.html and now I know how I could protect myself !!!!
There must be a voice in my head saying: Try the db-forum, try it and stay happy... ;-)
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
I am new to SQL Reporting services and learning from website tutorials. I could easily connect to Adventure Works database and could create some reports. Now I want to create one report from Oracle database in my network on one of my server (Network computer). I could not connect to database as do not know what to write (is path like Server name or IP address 152.168.1.25 Oracle database name) is required in Server name field? Server Type should be OLE DB or Oracle and OLEDB Provider should be Microsoft OLE DB Provider for Oracle? Then Server of filename? Is there any tutorial or material available, which can help me in establishing this connectivity? Any help is greatly appreciated.Do I have to install any thing on my local computer for oracle ( as read in one of forum for Oracle Client or something like that)
Everything I have read says that this is possible in Oracle 9 and greater versions, but I can't get it to work...
Here is my query:
select * from employee where employee_id in ('111','222')
This works fine in the data tab until I replace the values with a parameter. I get the pop-up to enter the parameter values but no matter how I enter them, I don't get any rows returned. I've tried entering them as '111','222' and as 111,222 and changing the parameter type from a string to an int, but nothing seems to work.
I am running this on SQL Server RS 2005 and my Oracle driver (Oracle in OraHome92) is version 9.02.
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).
I have been reading some topics on "slow Oracle connections", but I have not found an answer for my question yet.
The problem is that I am extracting data from a Oracle database to Raw files (to import into our DWH later on). For one table this takes more that 1 hour! I find this to be very slow, but maybe I am expecting too much speed?
Can anyone tell me how long it should take to download 10 million rows from Oracle to Raw file (no transformations)?
Some extra information: - The table has 10 million rows and a little over 200 columns - The datatypes in the Oracle database are varchar, number and date - I tried to tune the buffer size in the data flow and found that I could fit about 1900 rows in a DefaultBuffersize of 100 MB - I use the "Native OLE DBMicrosoft OLE OB Provider for Oracle" (Provider=MSDAORA.1)
Can I do anything to get a better performance or is 1 hour for a table like this the best performance we can get?
(Changing the Oracle database is unfortunately not an option...)
created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection) System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)
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?
I make a report to consume a SSIS package, actually I use SSIS to load a datasheet on my Sql Server database, then i expose the aggregate values in a datareader destination. Every work fine in Visual Studio. But when i deploy my report and my Shared Data Source, and I try to run the report i get the next message:
An error has occurred during report processing.
No se puede crear una conexión al origen de datos 'ETLSource'.
The package failed to validate. I dont Understand why, because when i work in BIDS the work perfect, Actually i'm using sql server authentication in my package connection to avoid the mistakes about report credentials.
That is the code of the ETLSource. <?xml version="1.0" encoding="utf-8"?> <RptDataSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Name>ETLSource</Name> <DataSourceID>c238d65d-a0f3-48da-ae23-323d6ba13bb0</DataSourceID> <ConnectionProperties> <Extension>SSIS</Extension> <ConnectString>-f "C:TempValidaCambioEfec.dtsx"</ConnectString> </ConnectionProperties> </RptDataSource>
Also I have SSIS and SSRS in the same machine. And I'm using the same domain Account to run SSIS, SSRS and Reports site.
I am currently trying to use a SSIS datareader destination as a datasource in Reporting Services (RS). I have sucessfully developed the report and I am able to execute and see the results from it in the RS preview pane. I am also able to deploy it (same server as RS development and report is on) without errors. However, once deployed, I am unable to get the report to work in Report Manager. I get the following error message:
"An error has occurred during report processing. Query execution failed for data set Dynamic_POS_BO_xRef'. The package failed to execute."
I have read everything in BOL regarding configuration of the RS Execution Account and have configured it with a domain member that has admin rights and tryed it with one that has limited rights (best practice recommendation).
I have developed an SSIS Package which is the Data Source for a Reporting Services report (using the OLE DB Destination DataReader component - very cool and powerfull!!!).
I have one Connection Manager defined in the package to access a SQL Server 2000 Database. The authentication is set to SQL Server Authentication (per the documentation in BOL) where I provide the UserID and Password.
Here is what works and what doesn't work:
In BIDS on my local machine the report executes beautifully.
When deploying the SSIS Package to my local machine's Integration Services File System and the report to my local machine's Report Server database the report executes beautifully (Yes, I'm using the developer edition).
When deploying the package to a W2003 Server Integration Services installation as well as deploying the Report and Data Source to the same W2003 Server's Reporting Services installation - the report fails.
I think it is because the passsword to the SQL Account is not "set". I am able to run the package through BIDS via dtexecui on this W2003 server successfully. However, when I run the report on the server it fails.
I am guessing it is because the password for the SQL Server Authenitcation is not "there". I am encrypting via "Save SensitiveDataWithPasssword".
What am I doing wrong? Any suggestions would be welcome.
I've created a SSIS package with a DataReaderDestination and a SSRS report that points to it.
I referenced these links during development and I have everything working as expected locally. **I changed the config files as stated on both my local machine and our server. My config matches the example exactly.
When I attempt to deploy my report project to the server I get this error message: "An attempt has been made to use a data extension 'SSIS' that is not registered for this report server."
In the report manager data source properties page it reads: "The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly."
In SQL Server Management Studio data source properties page SSIS is not a data source type option.
I've tried restarting both the Reporting Services and Integration Services on the server to no avail.
Any suggests? My problem is I can't get the SSIS to be recognized source on the server.
I'm getting some data from a flat file with a SSIS Package, it comes a integer but I would like to converted to a decimal with a 3 scale. Example: Flat File: 2070015000950011800 In the data conversion I had it with a 3 scale, but what I got was this:20700.00015000.0009500.00011800.000But what I want is something like this:20.70015.0009.50011.800 I dont know if you guys get the idea. But I will apreciate if anyone can help me. Thanks, Erick
the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.
the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.
i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero
i hope someone help me coz i need to handle it soon.
I have a Sql agent job which will exeute a SSIS package. While, one of my data sources is an excel file. I scriptted the sql agent job and recreate on a test server, it fails with The command line parameters are invalid.. The command line has the following for the excel data source, and that is the place fails the job. /CONNECTION UserDataExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\myserverUserDataUserData.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";" I guess it has something to do with the double-quote inside, Please advise.
I have created database and OLAP cube in Analysis services using SSAS.In SSAS I have used a datasource which is using SQL tables to populate OLAP cube.Now when I added some more data to my SQL tables and trying to deploy cube,the newly added is not getting populated in the cube.So i want run SSIS package which will import data from SQL tables to this OLAP cube.
Can you please help me how to write this SSIS package to import data from SQL tables to OLAP cube.(Very urgent issue)
I have a problem with decimal places. My source flat file has value of 22.20 but after inserting to SQL table SSIS is inserting as 22.2000. I am using DT_Numeric datatype in SSIS. I tried changing scale to 2 but it does not work.
We have an SSIS package that reads in a tab delimited file and throughs the data into a SQL Server 2005 table. The package worked fine on the test files but when we recieved the production files we are getting the error message:
Invalid data for type "numeric".
Needless to say this is not very discriptive. I ran a trace on the DB durning the call and its doing a bulk insert
BULK INSERT [dbo].[FNIAllAppData] FROM 'GlobalDTSQLIMPORT 00000000000015b800000000009fbd88' WITH (DATAFILETYPE = 'DTS_Buffers', CODEPAGE = 'RAW', CHECK_CONSTRAINTS, TABLOCK)
When I changed the Data Flow Destination off to OLE DB Destination the error does not occure and all records are loaded successfully.
Any suggestions or help would be appreciated. Thanks.
I have a problem with datatype mismatches. In my Data Flow I am reading a Flat File that includes a DateOfBirth which SSIS correctly assigns a datatype of [DT_DATE]. I need to check to see if the record has previously been submitted, so I do a Lookup that uses a SQL statement that combines fields (LastName, Zip & BirthDate) from three different tables. My problem is that SSIS assigns a datatype of [DT_DBTIMESTAMP] to BirthDate, which I can't use because of the mismatch. I have tried every form of re-formating the field in SQL that I can think of, but it still gets assigned that DT. And I'm unaware of a way of changing the DT once SSIS assigns it in the Lookup task.
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
Hi i am working on sending data from a dat file to table in sql server Database and i am using the Data conversion transformation in ssis to convert string of fixed length into numeric (11,5) which is the datatype for the price field in the table and its returning an error saying that status vale 6 and error text as Conersion Failed sue to overflow of specific type ... Can anyone let me know how to overcome this error.
