Building a Truly Platform Agnostic SSIS Package (SQL Server and Oracle Interop)

As painful as it is proving out to be, I am trying to create a single package that is vendor neutral between SQL Server and Oracle.

At first, I thought that as long as I was going OLEDB in and out, and I ensured that my SQL syntax was generic enough, I'd be OK.

Well, not exactly. I am finding there is more to this. Specifically, metadata gets generated differently based on the source/targets.

For example, on an OLE DB Source task, datatypes will vary (i.e. NUMBER in Oracle vs Int in SQL). The job here is to pick the common denominator for the SSIS specific type. Time consuming, but doable, I think.

Another issue is on an OLE DB Desitnation. If you choose Table for Data Access Mode, this value gets written to the dtsx XML file. So, even when both RDBMS have the same schema (which is an obvious prereq) if choosing SQL Server "dbo.DimTable" will get written to the file and for Oracle "SCHEMA.DIMTABLE" will get written.

So, I am am wondering, what is the best way to address this?

My inital thought was using a dtsConfig file (which I am already using extensively) and set the target table names accordingly. This approach would have the added benefit of allowing for post-deployment configuration changes should a table name change, for example.

This section of the dtsx file shows the values of interest:

Code Snippet

<component id="138" name="OLE_DST_DimTable" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property'>http://www.microsoft.com/sql/support;4">http://www.microsoft.com/sql/support;4">
<properties>
<property id="139" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="140" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">"ORASCHEMA"."DIMTABLE"</property>

<!-- More Properies -->
</component>

Ideally, I'd like configuration time control over the name attribute of the component element (highlighted) so that I can set the value of the property element with the OpenRowset attribute type (also highlighted). This way, presumambly as long as datatypes were generic enough, the mapping would just work.

But, in walking through the dtsConfig tree, I don't see these elements or attributes exposed.

I would sincerely appreciate any suggestions on how to accomplish this.

Rick

The OpenRowset property is not exposed through Configurations. You can try using the Table From Variable access mode. This goes through OpenRowset with the table name stored in a variable. You can then use Configurations to update the variable.

|||Great idea, I will try that thanks.|||

Hi Ted,

How do you recommend I go about setting the variable? I have used the Script Task in the Control Flow, but Script Component Task seemed somewhat unintuitive given its directional contexts (source, destination, transform). I don't want to put the script in the Control Flow aspect because I want to scope the variable iniatialization to the specific Data Flow Task that is in context at the time.

It seems that I want to just put a script in the data flow task that checks the provider value and if SQL, sets the variable to "dbo.TableName" and if Oracle sets the variable to "SCHEMA.TABLENAME".

You mention "You can then use Configurations to update the variable." Can you please elaborate?

Thanks,

Rick

|||

RickGaribay.NET wrote:

Hi Ted,

How do you recommend I go about setting the variable? I have used the Script Task in the Control Flow, but Script Component Task seemed somewhat unintuitive given its directional contexts (source, destination, transform). I don't want to put the script in the Control Flow aspect because I want to scope the variable iniatialization to the specific Data Flow Task that is in context at the time.

It seems that I want to just put a script in the data flow task that checks the provider value and if SQL, sets the variable to "dbo.TableName" and if Oracle sets the variable to "SCHEMA.TABLENAME".

You mention "You can then use Configurations to update the variable." Can you please elaborate?

Thanks,

Rick

Package configurations are a mechanism to set various component and variable properties through external means, such as XML configuration files. Take a look in SQL Server Books Online: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

A few additional links:

http://sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx http://www.sqlis.com/26.aspx|||

Thanks Matthew.

We came up with a potential solution simply involving the initialization and assignment of named variables with the specific fully qualified table name based on the provider. To your point, the added benefit of this is that if we wanted to ever override these values, we could simply expose them via package configurations.

Thanks again,

Rick