Driver Sources for Oracle

Here is my preliminary list of what works against Oracle....

OLE DB Source - use the Oracle Driver, but be cautious of Numerics and use a data viewer to make sure that numbers to the right of the decimal come through...

Lookup - Use the Microsoft Driver...using the Oracle driver will cause mismatches of all records when comparing numbers, like when looking up keys.

SCD - Oracle OLE DB Driver...no other choice, because the Microsoft driver can't handle parameters

OLE DB command - Same as above, as you need parameters

OLE DB Destination - use the Microsoft driver, as numbers ending in 0 (zero) will be turned into zeroes!!!

Cheers!
Scott

Oracle Driver and problems with Numbers...

Sorry for the long time not blogging, all....Holidays, vacations and family...you know the drill...

Here's something from the "this ain't gonna work file"...

Do NOT use the ORACLE OLEDB driver for inserting data in an OLE DB destination! I have found that when you insert a number value that ends with a 0(zero) or a 5(five), that this value may be truncated or turned to zero. Definitely don't use it in a Lookup transformation either! I have seen it miss matches in keys that end in o(zero).

Double check your data and see if you are having this problem. Mine is against Oracle 10g on AIX 5.3.

80 degrees F here in Florida today!
Scott

Slowly Changing Dimension Transformation against Oracle

Now here is something I was completely amazed by...

The SCD transformation will work against Oracle with a little tweaking. First, and this is the only time I would use this, use the Oracle for OLE DB Driver. The Microsoft Driver has a problem with the parameters that I haven't figured out yet. The SCD Wizard works as normal, but on the update side, it does not map the OLE DB Command to do the updates correctly. You will have to manually create the parameters under the Input side and then map them. Works like butter after that.

Another thought about these updates. If you are doing a lot of Type I dimensional changes, I would right these to a temp table and then use a one update to match everything by key. Much faster than the row by row action of the OLE DB command.

Scott

Parameterized queries against Oracle...Again!

So here is round 2...

You can also do a parameterized query against Oracle Execute SQL Task. Here is the scoop:

1. Make sure you are using the Microsoft Driver for OLE DB....you should be using this all the time except for one place...we'll talk about that later.
2. Create your query, and use ?'s for where your parameters go.
   
3. Map the parameters on the Parameters option tab. Make sure that you get the datatypes correct and that Oracle will often expect DB_Decimal for most numbers.

And off you go!

Scott

Parameterized queries against Oracle...

So I needed to get some data out of Oracle the other day and need it to be based off some parameters. Turns out the OLE DB source will not recognize parameters when used against Oracle. Neither the Microsoft or Oracle driver will do it. So I thought I might still be able to use the DataReader source against Oracle, it won't support parameters at all. I ended up creating a workaround that went like this....

First, create a variable called MyStmt and make it a String. Create another variable, MyID as an Integer. Now go to the properties of the MyStmt variable. Set the "EvaluateAsExpression" property to True. Now, read this well...place the SQL statement that you want to create in the "Expression" property, not the Value of the Variable. You will have to do some data conversion to concatanate the string and integer, so email me if you need help with that. Now, go back to your OLE DB source and set the source of the SQL statement to a Variable, and choose the correct variable name. Voila! A parameterized query! Don't forget to use the Microsoft OLE DB for Oracle driver and to check the datatypes on the Numerics...see my earlier blog posts...

Hope this helps....

Upcoming post...how to get SSIS to work against Oracle on a 64-bit server...

Scott
scott(at)barrettzone.com

The final story...how to get data out of Oracle....

So after waaaayyyy too much testing, if you're going to get Oracle, here are your best options...

1. ADO.Net DataReader with a ADO.Net for ODBC driver. Create a system DSN, and you're good to go. No problems with the NUMBER datatypes I mentioned above, as they are all converted to floats.

2. ADO.Net DataReader with the ADO.Net Oracle Client - Not as fast, and all numbers with decimal places will be cut off.

3. OLE DB connection - Use the Microsoft Driver first. It at least sets the precision and scale for you. It is wrong if you have decimal places, but you can adjust that in the advanced editor.

4. OLE DB Connection with Oracle OLE DB Driver - this is pure luck if it works, and most number fields will bomb out! You can manually creaet every column from scratch in the advanced editor, but by the time you get to the third column, you'll switch to one of the other methods....oh yeah...this is the slowest read times as well.....

Scott Barrett

SSRS Reporting Models for Oracle????

Here's a nice little shortcut that will get your users off your back!

I wanted to create a Report Model so my users could write their own reports. The problem is that the only two data sources you can create a model against are SQL Server and Analysis Services. No Oracle in that list (obvious reasons, but it would have been nice)!!!!!

Here is the work around for Analysis Services savvy folks....

You can create a DSV in Analysis Services the points to Oracle. Use the ADO.Net provider for best results. Build your cube and dimensions, and deploy the cube. Now create a data source in the SQL Management Studio to SSAS and choose the cube you just created. Right click and choose "Generate Model". It's so simple is scary! And because it's Analysis Services, it is fast. Now if you are creating "green-bar" reports, there are better ways to do this. If you want a ton of flexibility, this is the way to go.

If you have no experience in Analysis Services, don't try this at home. The learning curve for AS 2005 is quite steep.

If the idea of a Microsoft BI stack on top of anything hasn't hit you yet, go ask Barnes & Noble, CompUSA, Wal-Mart and Home Depot. The UDM is what brings all of this together........

Scott

MySQL connections...

Hey...data seems to be everywhere, and today I ran across it in a new place. Easy fix, used the Data Reader with the ADO.Net for ODBC connection. Voila! You have data!

Turns out that there is an OLE DB provider for MySQL. I'll try it out tomorrow and see if it works.

Try this link to get the OLE DB driver...

OLEDB for MySQL


Scott

Excel data not coming into SSIS right?

So sometimes you have data that does not come up right in Excel because either it's not formatted, or it's got mixed types. The end of it all is you just need it to come in as text and you can convert it later. My good friend, Robert Skoglund gave me this hint and I dug up some info on it....

Basically, add the option IMEX=1 to the ConnectionString for Excel. This forces the columns to be treated all as text.

This from ConnectionStrings.com....

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

"HDR=Yes;" indicates that the first row contains columnnames, not data

"IMEX=1;" tells the driver to always read "intermixed" data columns as text

TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

From Microsoft.....
http://msdn2.microsoft.com/en-us/library/ms254500.aspx

Credit, where credit is due....
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Loading dimensional data from Excel

So I've got all this Excel data and I need to load a dimension table. What I really need is the distinct list of dimensional keys and values and then do the lookup, but the Excel data source only lets me pick the entire worksheet, so I would have to stage it first....WRONG!!!!!

You can write queries as sources against Excel! Set up your Select Distinct statement and you are ready to go. If you want the easy way to see it done, use the Import/Export wizard in the BI studio and when they ask you about the source, choose the query option.

Just makin' life easier...

Scott Barrett

Theory behind how SSIS gets so fast...

I owe someone credit for this, and when I find that article again, I'll give them full credit...

Unlike most other ETL tools that deal with data on a row by row basis, SSIS uses memory buffers to hold the data in place. So think of it like this.....

Instead of data passing through the transformations to different places in memory, think of it as the data being stationary, and the transformations passing over the data. When you're done transforming, it comes out of memory into your destination. Moral of the story....the more memory, the better.....

To put it in simpler terms, sometimes the chicken crosses the road, sometimes the road passes beneath the chicken. In SSIS, it is the latter.

Wow...that was deep......

Scott Barrett

SSIS and Oracle Sequences

So I need to load a table, and I need an auto-incrementing field filled in. Easy enough I think....oops...I'm using Oracle and their infernal sequences. Now compared to the identity field in SQL Server or the Auto-Number in Access, these things are just abominations. Two ways to get around this, so here we go.....

1. Generate your own sequence numbers....pretty easy with the Script component and a variable. Search the web, email me or get Donald Farmer's book on scripting and this is done in a minute and works like a champ. Why? This now becomes an in-memory operation on the set of data, and it is quick....
2. Use the OLE DB command to do an insert and reference the MySequence.NextVal in your insert statement. Don't try this at home if you have a lot of rows to generate numbers for. After about 50,000 records, your going to want to get some coffee.....

There you go, so of now and create some numbers on the fly....

Scott Barrett