Andrew Fraser DBA

8 Transferring Data with SSIS

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

SSIS = SQL Server Integration Services

8.1 Copy Database Wizard

You can copy an entire database with the “Copy Database Wizard”. In SQL Server Management Studio, right click on database name, choose “Tasks” > “Copy Database”.

The wizard has an option to save and schedule the operation as a job for e.g. nightly data transfer.

Restoring from a backup, choosing the new database name as you do so, is another useful method of copying an entire database.

8.2 Export/Import Wizard

You can perform export/imports with the “Export/Import Wizard”. In SQL Server Management Studio, right click on database name, choose “Tasks” > “Import Data” or “Export Data”.

The wizard has an option to save and schedule the operation as a job for e.g. nightly data transfer.

Note that this works with Oracle data as well as SQL Server. Oracle client, normally including tnsnames.ora, is needed on the server that runs the job.

8.3 SSIS

What in Oracle would be done by cron shell scripts and export/import or sql*loader would be done in SQL Server with the above two methods.

Complex data manipulation – the kind of thing in Oracle that would need PL/SQL scripts rather than just shell scripts – is handled in SQL Server with SQL Server Integration Services.

[SQL 2012] SSIS is managed through SQL Server Data Tools (formerly pre v2012 called SQL Server Business Intelligence Development Studio) rather than SQL Server Management Studio. This is a cut down version of Microsoft Visual Studio.

[SQL 2014:] SQL Server Data Tools is no longer shipped with the SQL Server install, instead is a separate (free) download from microsoft.com.  Either download “Visual Studio 2013 Express” which includes this, or download the stand alone “SQL Server Data Tools” which installs as a cut down version of Visual Studio 2012.  Once installed, it is run through Start > Programmes > Microsoft Visual Studio <version>.  Summary at http://sqlmag.com/sql-server-2014/sql-server-data-tools-sql-server-2014.

Typical DBA tasks for SSIS include controlling releases of new versions SSIS packages, scheduling SSIS packages as jobs or job steps, investigating errors with SSIS package execution.

A simple SSIS package can be created in SQL Server Data Tools with:

• File > New > Project > Integration Services Project
• Add new “Data Flow Task”
• Add “ADO Net Source”
• Add “SQL Server Destination”
• Drag green flow diagram line to connect the source and destination.

Also useful for DBAs is the SSIS “Transfer Logins Task” to automate copying logins and privileges to e.g. DR servers.

8.4 Linked Servers

SQL Server calls its database links “linked servers”. These are often to other SQL Servers, but can equally well access Oracle databases.

It is also possible, and easy, to create Oracle database links into SQL Server databases – but Oracle charge a substantial amount for that functionality, which they call “Heterogeneous Services”. In spite of the grand sounding name it is just a little driver file to download and place under $ORACLE_HOME/network. Microsoft provide linked servers into Oracle databases for free, so it is more cost effective to let SQL Server drive the transfer and communication between the two database types.

Linked servers are seen under the “Server Objects” tree.

When creating a linked server to a different SQL Server, it is best to choose “SQL Server” data type, in preference to the default option of “Microsoft OLE DB Provider for SQL Server”.

And in the Security tab, specify the username/password to be used to connect to the remote server – most often this would be “using the logins current security context”, so same username/login is being used in both local and remote servers:

You can then manipulate the remote data:

select * from [MYREMOTESQLSERVER.MYDOMAIN.COM].[database].[dbo].[mytable]

Synonyms can be created to simplify that syntax.

8.5 Oracle Linked Servers

You can similarly link to/from oracle databases.  This requires installation of Oracle Data Access Components (which includes Oracle Simple Client) onto the SQL Server Windows database server.  Alternatively you could install full Oracle Client onto the SQL Server Windows database server (requires over 1gb disk space).  Either is available as a free download from oracle.com, without any license costs.

It is possible to do the reverse option – have a tnsnames alias and/or database link from an oracle database into a SQL Server database, by installing “Oracle Database Gateway” (formerly called “Oracle Heterogeneous Services”) onto the oracle database server.  Details  of that are in Oracle Support Note 1331065.1.  Note that the ODBC gateway is free, no license costs (that is options 1 and 3 in the support note).

Summary of steps for setting up Oracle linked servers:

1. Download 64-bit ODAC 12.2c Release 1 (12.2.0.1.0) Xcopy for Windows x64 from oracle.com/database/technologies/odac-downloads.html file ODAC122010Xcopy_x64.zip (or use a later version if available – but best to go for the Xcopy variant instead of the managed variant)
2. Unzip
3. In DOS command window as administrator, cd into the unzipped folder and run “setup.exe”
4. In control panel system properties, add two entries to the end of the PATH environment variable: “c:\oracle; c:\oracle\bin”
5. In SQL Server Management Studio, open \Server Objects\Linked Servers\Providers, right click on the OraOLEDB.Oracle provider, select properties and check the “Allow inprocess” option. (h/t David Brabant)
6. Still in SQL Server Management Studio, add a new linked server with the following options:

• Linked Server: “MYORACLELINK” (or any other reasonable name)
• Provider: “Oracle Provider for OLE DB”
• Data Source: “//myOracleServer.mydomain.com:myPort/myDatabase” (or use a client TNS alias)
• Security: choose “Be made using this security context”, and then specify the oracle username and password to connect to oracle with.
• Server Options: set “Collation Compatible” to true.

You can then access the Oracle tables and views with SQL like:

select top 100 * from [MYORACLELINK]..[owner].[mytable]

8.6 Summary of data transfer options

Lab 8 – SSIS Export/Import and Designer

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)