20 Jan 2006

SSIS XML Task / Source Adapter

I have been working with SSIS using the XML Task and XML Source Adapter. I was getting an error say that an element is was not defined. When I check the XSD, which was using import / include schemas, I found the element was difined but not in the main XSD schema the adapter was pointing to but another schema which was being imported. The probelm was with the schema were the element was defined not being invaild for the XML Source Adapter, that was the real probelm, as soon as I corrected the imported schema the error was resolved.

The probelm with the imported XSD schema there was some DTD define at the being of the file and XML Source Adapter does not support a mixture of DTD and XSD.

SSIS Execute SQL Task With VARCHAR(MAX)

While I was using the SSIS Execute SQL Task to return a single row result I getting the following error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "RestoreCmd": "The type of the value being assigned to variable "User::RestoreCmd" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object!."
This was happening becuase I was trying to assgin a varchar(max) result column to SSIS string variable.
There are two ways to solve this problem; one is to change the SSIS variable to object and convert each time it used to a string; the second way to solve this is to cast result column to max size limit.

SSIS 2005 and Database Snapshots

I have been looking into the feasibility of using database snapshots as method of rolling back an SSIS package that fails and this is what I found:
Creating a database snapshot was not a real problem, there is one issue that you need to be aware of. For each data file within the database that the snapshot is being based on there will have to be a sparse file created for it. I have created a little SQL script that automates the creation of the database snapshot.

Rolling back a database to its initial state after a package has errorred is a problem. The method to revert a database back to its initial state with database snapshots is a restore operation. The restore is a full restore, which of course means there can't be any connections while the restore is happening and this can't be done on-line because it might have to take out the primary data file. This is a problem because SSIS connection manager will create connections to the database when the package starts and doesn't release the connections until the package has completed its run.

A SQL script that automates the creation of the database snapshot.

   1:  DECLARE @vDBName AS VARCHAR(255) DECLARE @vFiles AS VARCHAR(MAX)DECLARE @vSQLCmd AS VARCHAR(MAX) SELECT @vDBName = DB_NAME(), @vFiles = ''
   2:  SELECT @vFiles = @vFiles + '(Name = ' + Name +', filename='''+ LEFT(physical_name,LEN(physical_name) - CHARINDEX('.',REVERSE(physical_name))) + '_PrePublish.ss''), ' FROM sys.database_files WHERE TYPE = 0
   3:  SET @vSQLCmd = 'CREATE DATABASE ' + @vDBName +'_PrePublish ON ' + LEFT(@vFiles,len(@vFiles)-1) + ' AS SNAPSHOT OF ' + @vDBName
   4:  EXEC(@vSQLCmd);