I've spend the better part of a day looking around for anything to help me, but SSIS is such a huge tool that nothing is of any use so far, or maybe I just don't understand it.
I need to take an old Access db that has some problems and move it into SQL server. I already have the schema set up in my SQL db. The old data needs to be fixed up, things like normalization and removing duplicates. My boss insists on using SSIS for this job, because we need someone here who knows how to use it, and currently almost no one does (one manager used it a long time ago).
So, I have a project set up in BDIS, or visual studio, or whatever this app actually is. I created a connection manager to my Access db, and I managed to drag an OLE DB Source onto the designer and set it up to connect to that db. I also figured out that I can select data from there.
Now what? I'd really prefer to have a way to just write a giant SQL script to grab all the data I need from the Access db, transform it how I want, and shove it into the SQL server database. But it looks like I'll need to use all these funky transformation tools in the designer. I also can't figure out how I'll get the resulting data into SQL server. I have the connection set up in the Server Explorer pane, but everyone online says never use the SQL Server Destination. So I'm lost there as well.
My boss said the Books Online help files would be very useful. So far, it's been like finding a needle in a haystack submerged under a swamp, with three castles sitting on top of it. There's just too much information, and none of it seems useful to me.
Edit
Hopefully some more information will be useful. I think the wizards that come with SSIS aren't powerful enough for what I want, so if they are, you'll have to please explain it. Here's an example of what I have to do, except my reality is a lot more tables with more transformations.
Say I have a source table that looks like this:
Companies
====================================================
| Name | Address | WidgetOne | WidgetTwo |
|--------------------------------------------------|
| ACME | 123 etc. | Trampoline | Cannon |
====================================================
I need to normalize this into two tables. And it will obviously have to track the IDs so that the widgets are associated with the correct company. If you could help with an example of how this case would be handled with SSIS, from Access to SQL server, then I can probably take it from there. Thanks!