[SSIS 2008] - Global transformation method for GUID fields
-
Hi, I'm trying to setup a SSIS Package to migrate data between two databases. There are many tables to migrate, and there are some tables that I cannot/do not want to migrate (i.e. I create the records by hand in the destination database - there are not a lot of records in these particular tables). An example of the tables I do not want to migrate is the 'Users' table. The primary key of this table is of type GUID. It is used in almost every other table, that I have to migrate, as foreign key. So I have to setup a translation of this GUID. Of course, for one particular table/data flow I can do the translation with a script component ; but then I have to rewrite the same long code for every other table/data flow. So I wondered if there could be a way of defining a global transformation method that I could call in each of my script component ; something that would look as below :
public static Guid GetUserGuid(Guid sourceGuid)
{
if (sourceGuid == new Guid("589CD136-4AED-4E3E-A391-DDAE8A790D1B"))
return new Guid("4D0947C1-5746-4DD6-AAFE-5ECB5BACBB9F");
else if (sourceGuid == new Guid("286BCF1A-8CDD-DE11-9EA4-000C29FC4D89"))
return new Guid("05F0CAD9-4095-E011-8ED0-00155D785E00");
// and so on...
}But I did not find the way to achieve this. I've been searching for three hours without finding some relevant help. I also thought of setting up a specific database that could provide the matching ; for example :
CREATE TABLE [Users](
[SourceId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[DestinationId] [uniqueidentifier] NOT NULL
)But then again I did not find a way to use this matching-table in my transformation process. Does anyone have some clues about my problem and where I should search for some hints about it ?