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.
A SQL script that automates the creation of the database snapshot.
DECLARE @vDBName AS VARCHAR(255)
DECLARE @vFiles AS VARCHAR(MAX)
DECLARE @vSQLCmd AS VARCHAR(MAX)
SELECT @vDBName = DB_NAME(), @vFiles = ''
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
SET @vSQLCmd = 'CREATE DATABASE ' + @vDBName +'_PrePublish ON ' + LEFT(@vFiles,len(@vFiles)-2) + ' AS SNAPSHOT OF ' + @vDBName
EXEC(@vSQLCmd);