Importing Excel: Why Can’t I Get My SSIS Package to Work?

I used the TechRepublic tutorial at http://blogs.techrepublic.com.com/datacenter/?p=205 to learn how to import an Excel dataset. It’s a good tutorial with pictures, which is what I needed since my DB skills are totally lame.

It worked perfectly on my test run. I easily imported a .xls that was sitting on my desktop to my local instance of SQL Server. Congratulations me.

But when I tried to use the tutorial today at the office, the same procedure threw me an exception error. And an ugly one at that:

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

The problem is that the tutorial has you build a destination using the SQL Destination part. Well that works fine if you are importing local to local, but if you are sending something to a networked server it gives the error. You actually need to use the OLE DB Destination instead.

Thanks to the guys posting at the MS Forums who confirmed this.

Importing an Excel File: Error With XLSX

I'm learning how to make SSIS packages. My first three jobs are all data imports from Excel spreadsheets.

Two of the three files were created in Excel 2007. SQL Server Business Intelligence for SQL Server 2005 cannot recognize .xlsx files. In order to set up data sources and data connectiosn in BIDS, you will have to save the file as an older format .xls file.

Error - "Backup set holds a backup of a database other than the existing database."

I had trouble restoring a CONTACTS database onto a location on a different server than the one it was originally created on. The entire point was to get an instance of that database onto another box.

I copied the .BAK file for the CONTACTS database onto the new server and attempted to run the Restore wizard but got a 3154 error - "Backup set holds a backup of a database other than the existing database."

I tried making a empty, new CONTACTS database. Same error from the wizard.

The problems seems to be that the backup path for the original CONTACTS was on the C:\ drive on the orignial server, i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backups\contacts.bak

While the backup path for the new server is D:\Program Files\MSSQL.1\MSSQL\Backup\

The wizard couldn't find the path, I think.

The solution was to script a RESTORE with a REPLACE. Here's the script I used. Thanks to Pinal Dave on this one.

RESTORE DATABASE CONTACTS
FROM DISK = D:\Program Files\MSSQL.1\MSSQL\Backup\contact_20080801.bak'
WITH REPLACE

Worked perfectly.