Oracle 8: SQLPLUS login.sql

set pagesize 5000
set linesize 120
set timing on

define_editor="C:\Program Files\Notepad++\Notepad++ -multiInst"

set termout off
column a_prompt new_value the_prompt
select lower(user) || '.' || substr(userenv ('sessionid'), 5) a_prompt from dual;
set sqlprompt &the_prompt.>
column a_prompt clear
clear buffer
undefine the_prompt
set term out on

OLE DB VS ADO.NET

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1a9e3670-9685-4943-913b-123ecf248a9c/

1. support across SSIS components:

a. SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject: http://ssis.wik.is/Connectivity_Libraries

2. performance

a. Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data.

b. That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed façade, and providing more abstraction with a little more performance overhead.

IBM suggests their ADO.NET Provider is better, because the OLE DB is “bridging” from managed code to unmanaged code.

3. 64bit considerations

a. Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here: http://ssis.wik.is/64-bit_Story

b. Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracle’s own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries.

c. For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

4. target data source & data type supportability

a. Connectors, whether they are ADO.NET or OLE DB providers, don’t support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etc…For a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki : http://ssis.wik.is/Data_Sources

b. Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsoft’s Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +…However, Microsoft’s ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm.

ISO Date Format Samples

SSIS Expression

(DT_WSTR, 4)YEAR(GETDATE())
+
RIGHT(“0″ + (DT_WSTR, 2)MONTH(GETDATE()), 2)
+
RIGHT(“0″ + (DT_WSTR, 2)DAY(GETDATE()), 2)

SSRS Expression

=CSTR(YEAR(Now)) + RIGHT(“0″ + CSTR(MONTH(NOW)), 2) + RIGHT(“0″ + CSTR(DAY(NOW)), 2)

DB2

SELECT REPLACE(CHAR(CURDATE(),ISO), ''-'', '''') 
FROM SYSIBM.SYSDUMMY1

SELECT CURDATE() - 3 DAYS
FROM SYSIBM.SYSDUMMY1

SELECT RTRIM(CHAR(YEAR(CURRENT DATE))) 
    || RIGHT('0' || RTRIM(CHAR(MONTH(CURRENT DATE))), 2) 
    || RIGHT('0' || RTRIM(CHAR(DAY(CURRENT DATE))), 2)
FROM SYSIBM.SYSDUMMY1

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') 
FROM SYSIBM.SYSDUMMY1

References:
http://en.wikipedia.org/wiki/ISO_8601

SSIS connections to Oracle on a 64-bit machine

In a simplified way, the SSIS connects to the databases via two parts, the data provider models and the database drivers.  The data provider models are .Net Provider, OLE DB Provider, ODBC, etc.  The database drivers are Jet engine, Oracle driver, etc.

Since we mostly use OLE DB, I will only discuss one type of provider, OLE DB.  Both Microsoft and Oracle offer data provider for OLE DB.  Microsoft has 32 bit “Microsoft OLE DB for Oracle”.  Oracle has both 32 bit and 64 bit “Oracle provider for OLE DB”.  Both data providers use the same Oracle driver on the machine.

For database driver, there are only Oracle drivers.  The only different is driver versions and OS versions.  For example, our laptops are usually has 32 bit Oracle driver.  The server usually has 64 bit Oracle driver.

Since Microsoft does not offer data provider for Oracle in 64 bit, the “Oracle provider for OLE DB” automatically become the only simple way to go.

For importing data into the Oracle, the most efficient way is still the Oracle SQL*Loader.  But it requires writing data to a text file first.  Another way is use Script destination to consume data in Oracle transitions.

Executing the SSIS package in 32 bit mode is another way to solve the problem.

http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html#!/2009/10/quick-reference-ssis-in-32-and-64-bits.html

http://msdn.microsoft.com/en-us/library/ms141766(v=sql.90).aspx

http://www.robkerr.com/2010/03/oracle-driver-configuration-for-ssis.html

http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/01/26/connectivity-issue-with-oracle-in-x64-environment-ora-12154-tns-could-not-resolve-the-connect-identifier-specified.aspx

There is an issue in using the “Oracle provider for OLE DB”.  Oracle data type Number is a variable-length data type.  It is similar to the Numeric data type in SQL Server but it saves space at the table.  The SSIS failed to retrieve the correct precision and scale when the precision and scale of the Number fields are not specified in the Oracle database.  (This is only a 2005 problem.)

In this case, it is not a good idea to use “Oracle provider for OLE DB” as data source in the SSIS “Data Flow”.  So it is better to use the .Net Provider,“OracleClient Data Provider”, instead of OLE DB providers.

Refere to the “OLE DB VS ADO.NET” for more information.

Oracle data type Number
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209

SSIS Oracle Number issues
http://connect.microsoft.com/SQLServer/feedback/details/282497/ssis-unable-to-determine-precision-for-oracle-number-datatype

Just a reminder!  The “Oracle provider for OLE DB” does not process the comment characters “–” correctly.