May 8, 2008 at 6:54 pm
I have what I believe is the perfect application for SSIS, but I can't seem to find the solution. I don't know if I'm searching for the wrong terminology, or if it just isn't out there.
I have an Access db that I am migrating to SQL Server. There are several situations where I want to break out data that is in one table in Access into multiple tables in SQL Server. For example, in Access a field called AccountRep is included in the Clients table. The same account rep may have multiple accounts. So what I'd like to do is have an AccountRep table, with ids, joined to the clients table. I'd like to go from this:
ClientName,Address,Phone,AccountRep
Adventure Works,123 Main St., (123) 456-7890, Bob Jones
Northwind,456 1st St.,(987) 654-3210, Jane Doe
Microsoft, 789 A St., (456) 789-0123, Bob Jones
To:
ClientName,Address,Phone,AccountRepID
Adventure Works,123 Main St., (123) 456-7890, 1
Northwind,456 1st St.,(987) 654-3210, 2
Microsoft, 789 A St., (456) 789-0123, 1
And:
AccountRepID,AccountRepName
1, Bob Jones
2, Jane Doe
I thought that this was considered normalization, but when I search for it, I find instructions for going from AccountRep,Client1,Client2 to Client,AccountRep; which is where I already am. Is there some way I can use the Unpivot transformation or something else to accomplish what I'm trying to do? Or am I totally off-track? Thanks, and please let me know if this is not clear or if I am just not searching right.
May 8, 2008 at 10:45 pm
You definitely can use SSIS to achieve what you are after. The simplest approach is to use 2 data flows.
The first imports a unique (distinct) list of account reps and loads them into the account rep table. To do this I would use and OLE DB source (probably you are already doing this) with command that looks like SELECT DISTINCT AccountRep from Clients. I would then send this to a LOOKUP task. This should be configured to ignore errors. The error output from the lookup task should be sent to either an OLE DB Destination provider or a SQL Server Destination provider.
The second dataflow starts with the select you already have. The output from this should go to a lookup task that looks up on the AccountRep table and returns the ID. You should now have enough info to insert/update your destination Client table - again using either an OLE DB Destination provider or a SQL Server Destination provider.
May 10, 2008 at 12:38 pm
Beautiful, it worked like a charm. Thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply