SSIS and Oracle

Working with Oracle and SSIS/DTS on a 64-bit windows server has been a painful experience.

Here are the steps to connect to Oracle if you’re setting up a new SSIS package.

  1. Create a connection using the .net Providers\OracleClient Data Provider
  2. Use the DataReader source type.  Select the Oracle connection you created in step one.
  3. Use whatever destination you need to – it doesn’t matter at this point.  I typically use the OLE DB for SQL Server destinations.
  4. Schedule as a job with the integration services type step.  No 32 bit DTEXEC modification needed as was the case with DTS steps that hit Oracle. 

You may hit some quirky errors with overflows or numeric data types.  It’s good to make sure you have the latest oracle patchset applied before proceeding with workarounds.


I’ve always been a stickler for backups, even before I was a sysadmin.  I create music on my home computer, creating massive volumes of data just waiting to disappear from my life.

I have been using SuperDuper and TimeMachine on OS X.  I don’t backup my windows machine, since it is a media center we mostly just stream stuff off the web.

When I leave town I throw the backup drives in an unlocked fireproof save with a note begging thieves not to steal it.  This data is truly unique and irreplaceable.  

But what if there was a fire when I didn’t have my drives in the safe?  I’d be very sad, that’s what.

There are a few online backup services which are starting to look very attractive.  Most offer 2GB for free.  I’ll be needing at least 50GB for audio mixes.  Actual song files are another story….maybe 300GB or so.  Then there are various video, picture and music files I’d hate to lose.  So it’s looking like I’ll be needing around 500GB to lock it all up.

Enter Mozy – $4.95 a month for unlimited storage space.  I assume this is for one computer.  What a deal.  That puts a year at $60.  40 years at $2400.


Helping out the auditors

If you’d like to give an auditor their very own copy of your data on their machine and your DB is less than 4 GB, then read on.  As a responsible IT person, you won’t be putting sensitive information on this auditor machine, unless it is properly secured, electronically and physically, right?

1. Set up the auditor machine

Install SQL Express on the auditor machine. Enable remote connections via TCP/IP with the config tools. Grant permissions for your agent account on the source server to be an admin on the auditor’s machine. You’ll also need to grant access for the agent account to write to a backup directory on the auditor machine.

2. Create a script that will restore the database and do some other steps. This script is referenced from sqlcmd in step 3. My script does these things:

MASTER.dbo.usp_killusers ‘MyDB’ –something similar to this script ->>

RESTORE DATABASE MyDB FROM DISK = ‘\\workstation\backup\MyDB_Auditor.bak’
MOVE ‘MyDBtrainingsys’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf’,
MOVE ‘MyDBtrainingdata’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_1.ndf’,
MOVE ‘MyDBtraininglog’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_2.ldf’

–Drop our auditing ddl trigger from the database
IF EXISTS (SELECT * FROM sys.triggers WHERE name = N’DBA_Audit’ AND parent_class=0)

EXEC sp_changedbowner ‘sa’

–Add rights for whomever needs access
CREATE USER [Domain\AuditorLogin] FOR LOGIN [Domain\AuditorLogin]
EXEC sp_addrolemember N’db_datareader’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_datawriter’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_ddladmin’, N’Domain\AuditorLogin’

3. Create the job with these steps on your source server.

(T-SQL type) – Create backup on workstation
BACKUP DATABASE MyDB TO DISK = ‘\\workstation\Backup\MyDB_Auditor.bak’

(OS Command) – Restore
sqlcmd -i \\Share\auditorRestore\MyDBAuditorRestore.sql -Sworkstation\sqlexpress -o \\share\sf.log

(OS Command) – Cleanup
del \\workstation\backup\MyDB_Auditor.bak