August 24, 2011 at 1:02 pm
I have been using SSIS for a few years and I have recently been hired as a Senior ETL Architect. We are going to replace our SQL Server 2008 R2 data warehouse with Vertica, but we are keeping SSIS. I have already completed the source-to-target mapping for the current (SQL Server) system and I will start the mappings for the new system soon. I am supposed to design the SSIS package, but we have an offshore team that will actually build and implement them. This is a big challenge for me and I have some questions for the wise people out there.
1) The previous ETL system was created with stored procedures, views, and C# code. The offshore team are programmers and are quite skeptical of GUI-based ETL tools like SSIS (in which they have limited experience). How can I improve their comfort level with SSIS?
2) Since the offshore team has limited SSIS experience, how can I architect the ETL process so they can do the "heavy lifting" of building the solution in SSIS?
3) Should I just build a sample SSIS package that processes one table or client and then let them use it as a template?
4) What ETL naming conventions would you recommend?
5) What documentation is good for the operational team (also offshore) to use?
NOTE: there is another person, a Senior Data Warehouse Architect, who is designing the data warehouse tables. I only have to design the ETL.
Thanks in advance for the help!
August 25, 2011 at 7:19 am
imani_technology (8/24/2011)
I have been using SSIS for a few years and I have recently been hired as a Senior ETL Architect. We are going to replace our SQL Server 2008 R2 data warehouse with Vertica, but we are keeping SSIS. I have already completed the source-to-target mapping for the current (SQL Server) system and I will start the mappings for the new system soon. I am supposed to design the SSIS package, but we have an offshore team that will actually build and implement them. This is a big challenge for me and I have some questions for the wise people out there.1) The previous ETL system was created with stored procedures, views, and C# code. The offshore team are programmers and are quite skeptical of GUI-based ETL tools like SSIS (in which they have limited experience). How can I improve their comfort level with SSIS?
2) Since the offshore team has limited SSIS experience, how can I architect the ETL process so they can do the "heavy lifting" of building the solution in SSIS?
3) Should I just build a sample SSIS package that processes one table or client and then let them use it as a template?
4) What ETL naming conventions would you recommend?
5) What documentation is good for the operational team (also offshore) to use?
NOTE: there is another person, a Senior Data Warehouse Architect, who is designing the data warehouse tables. I only have to design the ETL.
Thanks in advance for the help!
I'm probably not a wise person, but I do have a couple of opinions.
1) I would argue that it's about productivity. A developer is going to be more productive using SSIS than writing everything by hand. Also, it gives these developers a chance to learn a new tool. Ultimately, they'll just need time to get familiar with SSIS.
2) I think you have to create some template SSIS packages. Make it so that the configuration and logging are taken care of in the template.
3) Take your template(s) and then implement a small example for each.
4) I've followed and implemented the naming standards from a Jamie Thomson blog post (http://consultingblogs.emc.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx). They've worked well for teams I've been on.
HTH,
Rob
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply