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)
