I have been putting together a DTC package using Business Intelligence Studio for Visual Studio 2005. For those in the know, this is the IDE for working with Integration Services for SQL Server 2005.
One of the tasks I had was to pull data from an Oracle data store based on a parameter that was generated from a query to a SQL 2005 db. I would then need to move the data into the SQL 2005 database, along with a few derived columns.
Whilst I was easily able to set up connectivity to the Oracle DB using ODBC within a Data Flow scope, I needed to use parameters and the Data Reader object does not support them. OleDB does, if you use a variable with an expression you can evaluate. I'll post how to do this later.
The problem I had was that the Microsoft OleDB provider for Oracle does not seem to work very well. By that, I mean I could not get the thing to function at all within a Data Flow section, but I could get it to work within a Control Flow.
The answer to all my problems was to download and install the relevant Oracle OleDB Provider. I made the relevant adjustments to my "OLE DB Source" component and relevant connections and ran the project. Everything worked. Having now deployed the package to the test environment and created and scheduled a job, there have yet to be any further problems.
So the lesson I learnt from this was that if you are going to connect to a database from the Microsoft platform always grab the database manufacturer's providers. They are generally free and supported so hopefully fairly simple to justify the addition to deployment.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment