SSIS Transfer Database task

Just a few notes about the use of the transfer database control task in SSIS 2005.

We had several problems with the logins being corrupted when the database was placed in the new instances. It appears that the task attempts to match the users to the instances users as one of its' steps. If it cannot find the user names in the new instance it corrupts the dbo users and schema.

The simple solution is to make sure that all users exist in the new instance. The transfer database task will then match the DBID of the instance users. This saves you from having to run sp_change_users_login to match the DBID from the new database to the instance

Thanks you Sam for this one.

Raising errors in the history

/*
I have been looking into raising customer errors in a large collection of file copy routines I am creating to provide better logging for a collection of PDFs we use for various websites. This is a start of what I am looking for and will be added to as I find new features.

The error will be posted in the SSIS history so it will be easier to find when doing the morning audit.

*/

Dim filename As String = Dts.Variables("PDF_filename").Value.ToString
Dim sourceFilePath As String = Dts.Variables("SourceFilePath").Value.ToString
Dim destFilePath As String = Dts.Variables("DestinationFilePath").Value.ToString
Dim destFilePathArchive As String = Dts.Variables("DestinationFilePathArchive").Value.ToString
Dim purgeSourceFile As String = CBool(Dts.Variables("PurgeSourceFile").Value.ToString)
Dim ArchiveFilePath As String = Dts.Variables("archivePathExtension").Value.ToString

Dim sourceFileName As String = sourceFilePath & "\" & filename
Dim destFileName As String = destFilePath & "\" & filename
Dim destFileNameArchive As String = destFilePathArchive & "\" & filename

Dim CopyDest As String = destFilePath & ArchiveFilePath & "\" & filename

Try
File.SetAttributes(destFileName, FileAttributes.Normal)
'Make sure that if the file exists at the destination it is not read only
If File.Exists(CopyDest) Then
File.SetAttributes(CopyDest, FileAttributes.Normal)
End If
File.Copy(destFileName, CopyDest, True)
File.SetLastWriteTime(CopyDest, Now())
File.Copy(sourceFileName, destFileName, True)
Catch ex As Exception
'An error occurred.
Dts.Events.FireError(0, "Error in file copy", _
ex.Message & ControlChars.CrLf & ex.StackTrace, _
String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

Joining 2 files in SSIS with an outer join

This post is to demonstrate the step necessary to perform an outer join on 2 record sets. the goal of this join was to find all expiring transactions from an authorization table. Outer join them to any settlements against the transaction. Then finally create an in memory record set that can be used to create an email to notify the users.


This leads me to complain about the stupid syntax for if then in the derived column expression editor as shown below:

[Expression the create a true false result] ? [Then data value] : [Else data value]

I have no idea where this came from and it took me forever to find this. This is the link to MSDN.

This can all be accomplish with a single transact SQL statement using a temp table variable and this makes it look really complicated.