Oct 4, 2011

Problem Migrating DTS Packages to SSIS

The problem was that I had a fresh installation of SQL2005 on a fresh box. I was migrating the existing DTS packages from a SQL 2000 box into SQL 2005 using the Legacy - Data Transformation Packages - migrate option. The packages appeared to migrate successfully, but I could not open them to view them! I had installed the SQL 2000 DTS Designer Components but was still receiving the following errors:





Which would result in the DTS Package Viewer loading a sketchy view of the package:


Sometimes, a similar problem can occur if you have dashes or underscores in DTS package names. It turns out this issue still occurs if you have spaces in you package names as well! So after simply removing the spaces in the package names, everything just worked!

Problem with Embedded SSIS Package

After migrating SQL DTS packages to SSIS, in which SQL very nicely created SSIS packages for me to work with, I found I was getting System.IO.FileNotFoundException errors once I imported into SQL. The package worked fine in BI. I looked into what SQL had done when it imported these packages and noticed that the storage location for the Packages was "Embedded in Task"



It had embedded the packages within the admin users Temp directory - a directory not accessible by the SQL user that ran the packages.


To resolve this issue, Edit the package, Click Save As to save as a Structured Storage file in a central location that the SQL agent user has access to.
Change the Storage Location to be Structured Storage File and use the File attribute to point to the centrally located file. Browse to set the package name and load the required package.

Rebuild & deploy the package and all is now good! :) Make sure that you select the checkbox to
"Rely on Server Storage for Encryption" so that the package password is stored.
Alternatively - create & load the package as a seperate package in SQL and load from SQL directly in the package properties form above.

Database is in Suspect Mode

If your project's database is in suspect mode, then no transaction will take place until and unless you repair your database. That causes a show stopper for your up and running application. Here, you will find a way to get out of this.

MessageError: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xaaaaaaa9). It occurred during a read of page (1:9608) in database ID 6 at offset 0x00000004b10000 in file 'databasename.MDF'...
If you find your database in Suspect mode, then please keep your nerve strong. Just proceed step by step what I am written below. I think you will get out of this trouble. SQL Server 2005 introduced a new DB Status called Emergency. This mode can change the DB from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode. The steps are... After executing the script given below, you will get back your database in operational mode. Actually I have tried with two of my existing live systems and found no data loss.
Note: Obviously there are two more options available. Run REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transitionally consistent state. Here are a few things to bear in mind about emergency mode repair: it's a one-way operation. Anything it does cannot be rolled back or undone. If this worries you (if someone ever got into that state, then surely don't have the healthy concern about data that they should have in the first place) then make a copy of the damaged database before you run emergency mode repair.
As it's a one-way operation, you cannot wrap it in an explicit user-transaction.
It's the only repair option available in emergency mode - if you try to use REPAIR_REBUILD, then it won't work.

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"

This error can occur for many reasons and it is often suggested that you change the timeout of your dataflow task to resolve the issues. I never like to do this as it can end up causing more issues that you hadn't anticipated! It can also occur if you are trying to access a SQL server that is not on the server that you are running the SSIS package on.

For me, I have found that it tends to occur whenever I use the SQL Server Destination with the OLE DB source. If you use the OLE DB source - remember that you have to use the OLE DB destination as well - otherwise SSIS will not like it at all - letting you know with the above error.

Changing the destination type to be OLE DB destination has resolved this issue for me once again today.