Archive for November, 2012

Product Evaluation – SSIS Attunity Adaptor

November 17, 2012

Description – A plugin for Integration Services which allows us to move data between SQL and Oracle

Currently we use  an OLEDB Provider by Oracle.

Prerequisties

The Oracle client software must be installed on the computer running SQL Server Integration Services (SSIS)

Installation

Attunity can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=29284

The SSIS service should be restarted once Attunity is installed

The following Data Flow Items should be added to Visual Studio

  • Oracle Source
  • Oracle Destination

Data Source Type when connecting to Oracle is MSORA

The connection manager configuration when connecting to database is

TNS Service Name:   ip:port/servicename

An installation guide can be found here: http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

Performance

I have our largest 900k row table to test the product

Using our current method of data import I can import the table in 2 and a half minutes.

Using Attunity with different batch sizes yields different results

Tool Batch Size test 1 (secs) test 2 (secs) test 3 (secs) avg (secs)
Attunity 1000 93 80 82 85
Attunity 10000 62 68 66 65
Attunity 50000 76 62 62 66
Attunity 100000 65 80 68 71
OLE DB Driver NA 147 148 147 148

Test Conclusion: The fastest attunity batch size appears to be around the 10000 mark and is more than twice as fast as the currently employed method.

The following blog post tells us that we can achieve even faster results if we convert data to string when we extract data and then convert it back to native type when importing however this was not evident in testing where average speed was around 120 seconds.

http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspx

Price: Free

Conclusion: We should start using this tool immediately on our larger tables where the time savings will be noticeable. Smaller tables will see only negligible saving and so should stay in the current import package. Utilising SSIS means that we lose flexibility provided by the current TSQL solution (for example adding new tables would mean opening up the package making changes and then uploading the new file as opposed to simply adding a new record to the import table) however the time saving and the fact that we can run tables imports in parallel means that this is a price worth paying.

Possible other Benefits: This tool allows us to upload data to Oracle databases, we could use it to transfer data from text files, excel files, SQL/Postgres/Access database directly into Oracle.

View size of all databases on the server

November 17, 2012

Problem:

View size of all databases on the server

Solution:

SELECT d.name, ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs

FROM sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

GROUP BY d.name

ORDER BY d.name

IMEX Explained

November 17, 2012

http://support.microsoft.com/kb/194124?wa=wsignin1.0

Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.

The possible settings of IMEX are:

        0 is Export mode
        1 is Import mode
        2 is Linked mode (full update capabilities)

The registry key where the settings described above are located is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

SSIS Importing dates from Excel in the wrong format

November 17, 2012

Problem: SSIS is importing dates in American (mm/dd/yy) format from Excel.

Solution: Right click the data flow task and change the locale setting to English (United Kingdom)

Reporting Services asking for a Username and password

November 17, 2012

Problem: Reporting Services is asking for a Username and password when accessing the url
Solution:

1. Add site to trusted zone
2. Check automatic logon with currentuser name and password within custom settings