Getting information from Oracle into SQL Server using SSIS

I needed to populate a SQL Server database with information from an Oracle database, but found when I created a linked server and ran the INSERT statement manually, I was getting errors regarding truncation and datatypes.  I had already run this successfully using the IMPORT data wizard (and had my application owner confirm the data) so was a bit confused.  SSMS wasn’t reporting which row or column was problematic.  Overall, my goal was to run a nightly job to run the INSERT statement, but this wasn’t working.

Ultimately, I found that during the IMPORT data wizard, I could choose to save the process as an SSIS package, in the MSDB database.  I chose this option, created a job to call the package and was off and running.

This was great, but what if there were problems during the data move?  Doing some research, I had come across this outlining logging in SSIS packages.  I exported my existing package to the file system, opened it up in BIDS and gave it some logging options.  I renamed it, imported it back into MSDB and altered the job to call the new package.

How to export a package from the MSDB database:

Open SSMS, choose “Integration Services” for “Server Type:”.  Enter your SSIS credentials.

In the “Object Explorer” pane under “Integration Services”, expand “Stored Packages” -> MSDB.

Right click the package to export, choose a location on the file system and name for the package.

To edit this package in BIDS:

Open BIDS, choose “File” -> “Open” -> “File.  Locate and choose the freshly exported package.

To add logging to a file for this package:

In BIDS, choose “SSIS” -> “Logging”.

Choose the “Provider Type” -> “SSIS log provider for Text Files”.  This will populate the grid below.

Click on the “Configuration” field, a drop down will appear.  Choose “New Connection”.

A new window will open, you can choose to log to a new file, existing file, new folder or existing folder.  I chose a new file each time and put it in my SQL Server log directory (…\MSSQL.1\MSSQL\Log)

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *