Search results matching tag 'ssis'

ETL Programmer Analyst Team Lead

andy — Wed, 30 Sep 2009 21:39:21 GMT

ETL Programmer Analyst Team Lead

Candidates will contribute as an ETL team lead in the Data Management Group (DMG) responsible for design, development, and implementation of interfaces and conversions for the Medicaid Management Information System in Idaho.

ETL team duties include requirements validation, code development, source control, unit testing, and version deployment scripting. SQL and stored procedure expertise in a RDBMS environment required, Microsoft SQL Server 2005 T-SQL desired. ETL development expertise with Microsoft SQL Server Integration Services desired.

Team lead duties include working with a project manager to prioritize task assignments, progress tracking, and ensuring timely, yet accurate delivery, Mentorship acumen required. Full life cycle development experience required. Configuration and release management experience highly desired. Health information management experience highly desired. Excellent written and oral communication required. Some travel may be required (25% maximum).

SSIS Developer

andy — Wed, 30 Sep 2009 21:28:54 GMT

SSIS Developer

-SSIS and SQL development as well as Maintenance and Support
-Maintains information policies and procedures to support current and future data information needs.
-Ensures proper data definitions, ownership, use and integrity of data.
-Facilitates the identification of potential gaps in available data and collaborates with business areas to determine best means for acquiring the data needed.
-Establishes and maintains excellent knowledge of data warehouse data structure design, definitions, capabilities, programming languages, and data integrity issues.
-Collaborates with IT resources and data owners to define business objectives and definitions for databases including business rules, sources, purge archive criteria, and reload schedule.

Skills Required:
-STRONG SKILLS IN SSIS AND SQL are the most important!
-Knows ins and outs and techniques of SSIS and SQL
-Knows stored procedures, functions, and data modeling
-Someone who is an expert at process and will ask questions if design flaws exist
**Strong UNIT TESTING to unit test their work before it goes to Change management for system testing
-Extensive experience with SQL Server 2005 T-SQL and stored procedures required. (6 years)
-Experience with Microsoft SSIS required. (2 - 3 years)

-Requires BA/BS in related field and 6-8 years experience in data analysis, data modeling, and working with automated data warehouse systems, or any combination of education and experience, which would provide an equivalent background.
-Experience with programming languages and various statistical programs in addition to general knowledge of major components of managed care operations required.
-Excellent analytical, problem-solving and PC skills required.
-Excellent oral and written communication skills required.

Preferred Skills:
-Experience with large Data Warehouses and Data Marts preferred.

Thursday August 14th 2008 6:30PM to 8:00PM

fibrock — Tue, 05 Aug 2008 11:59:00 GMT

August's Presentation: SSIS FrameWorks!

Registration - Please register!

This Months Sponsor-McClain Group II

Abstract:

SSIS Frameworks focuses on Object Oriented programming techniques applied to SSIS Development. We will explore the concepts of encapsulation and modularity from an Object Oriented Programming perspective and how this can be applied to developing SSIS packages. SSIS Frameworks will look into building both a metadata structure and the SSIS package design, to support breaking units of work into small reusable SSIS packages and how to structure those packages for re-use. Real world examples from a Data Warehouse to Master Data Management Systems will be referenced to show how this type of SSIS development can cut development time and allow for coding work to be spread across a team. We will also get into the nitty gritty of some SSIS configurations/event handlers/variable passing.

About Steve:

Steve Fibich currently works in Richmond VA at PFGC as the Systems Architect on a Master Data Management project utilizing SQL Server 2005 and SSIS 2005 pulling data from iSeries, Unix, VAX, and SAP for a massive system integration effort. He has been working with SQL Server since 6.5 building Data Warehouses, building integration systems for third party product integration, creating Master Data Management systems , and doing general DBA work for the past 10 years.

Re: 7/17/2008's Presentation - Lessons learned from the Colonial Williamsburg Data Warehouse Project

rptodd — Mon, 21 Jul 2008 14:29:48 GMT

Sorry it took me so long to reply. I spent about an hour putting together a reply, only to receive an error when I clicked post, and not being able to go "back" to it in my browser history.

Here's the recreation of the post:

Thanks for the kind words. I definitely enjoyed doing the presentation, and am glad it was timely. You should make sure you attend next month's meeting, as I believe Steve is going to be talking about package frameworks exclusively.

As for how we're grouping our configurations, we have the one DB: SSISConfig, and then inside we roughly group the configurations into tables, with 1 table per package. Then within the packages/tables we're breaking things down into logical groupings using the ConfigurationFilter column in the table (I'll show examples in a second), which corresponds to the Configuration String which you can see here, from the Package Configuration dialog in the package:

This is a screen capture from the package I talked about in the presenatation: the MasterPackage, which controls the execution of all the packages we execute in our ETL process.

Here's some data from that table, which is SSISConfig.dbo.MasterPackage. It's got the column headers in the first row, followed by comma separated values in the subsequent rows:

ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType, DefaultConfiguredValue
ConnectionStrings, <connection string stuff>, \Package.Connections[Ticketing DataMart].Properties[ConnectionString], String, <connection string stuff>
ConnectionStrings,<connection string stuff>, \Package.Connections[Foundation Datawarehouse ADO.net].Properties[ConnectionString], String, <connection string stuff>
DisableFlags, False, \Package.Variables[User::DisableTicketing].Properties[Value], Boolean, False
DisableFlags, False, \Package.Variables[User::DisableMergeProcess].Properties[Value], Boolean, False
DisableFlags, False \Package.Variables[User::DisableHouseholding].Properties[Value], Boolean, False

It's not the best example of how we utilize the configurations, but it's still useful. The entries I've chosen to show can be used to change the settings of the various connection strings the package uses, and to enable and disable entire steps of the ETL quite easily. Trust me, this has been very useful on a regular basis. You may also notice that we don't have the TruncateConfiguredValue column in this table. That's because we don't have any special settings in this table for when we want to Truncate the values in our data warehouse.

Here's another example using one of the individual dimensions in our data warehouse, which is where we do a lot more utilization of the Default and Truncate columns. This is from SSISConfig.dbo.DimCustomer, which controls the settings for the conformed Customer package/dimension that I talked so much about in the presentation:

ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType, DefaultConfiguredValue, TruncateConfiguredValue
ConnectionStrings, E:\SSIS\ETL\FDW\Test\Customer\, \Package.Variables[User::OutputFilePath].Properties[Value], String, E:\SSIS\ETL\FDW\Test\Customer\, E:\SSIS\ETL\FDW\Test\Customer\
ConnectionStrings,<connection string stuff> \Package.Connections[Ticketing Transaction Source System - OLEDB].Properties[ConnectionString], String,<connection string stuff>,<connection string stuff>
RawFile, E:\SSIS\ETL\FDW\Test\Customer\Checksum.raw, \Package.Variables[User::RawFileLocation].Properties[Value], String, E:\SSIS\ETL\FDW\Test\Customer\Checksum.raw, E:\SSIS\ETL\FDW\Test\Customer\Checksum.raw
Truncate, True, \Package\Truncate DimCustomer.Properties[Disable], Boolean, True, False

Here you can see a couple more ways that we leverage the SSIS package configurations. Not only are we still using the Connection Strings like we were in the MasterPackage: to control which DBs and servers the package was connecting to. We're also using it to control where the package is physically located on the server itself. This way we can have multiple versions of packages residing on the same server. This is great for running both Test and Production on the same server, for example. The last line also shows how we can control the execution of an individual package using the Disable property. I have to make a quick caveat here. Some people feel this is not a best practice, and have run into problems with it. It's worked well for us, but if you wanted to do things differently, such as putting a variable into your package, then checking that variable in your step, most commonly in an Execute SQL Task or an OLE DB Source, go for it. That last entry enables or disables an Execute SQL Task in the package which contains TRUNCATE dbo.DimCustomer. If it's enabled, it truncates, if it's disabled it doesn't get executed. Simple. Handy. We then use the Default and Truncate columns from the stored procedures we've built for controlling the configurations.

Last thing for this post. This is not a concept we came up with ourselves. We took an existing concept, the 2 pass configuration using SQL Server Tables for storage of the configurations, and added a simple idea, which is storing the typical configurations with the current configurations. This means that the majority of information on how to set this all up is contained both in BOL and on the web. Just take those tutorials and add a couple of columns, and your'e off and running. Here's a good article, for example: http://www.mssqltips.com/tip.asp?tip=1405

Hope this helps, and post any other questions you have,

Rick

7/17/2008's Presentation - Lessons learned from the Colonial Williamsburg Data Warehouse Project

rptodd — Fri, 18 Jul 2008 17:21:31 GMT

Hey guys, I really enjoyed presenting last night, and look forward to doing it again at some point in the future!

I wanted to get everyone the presentations, but I couldn't figure out a way to upload a file here, so I put them here instead: ssis configurations and The Conformed Customer Dimension.

If you have any questions or comments about the presentation please post them here, and I'll do my best to answer you.

Cheers,

Rick

P.S. If you come to Colonial Williamsburg, drop me a line, and I'll try to spare a little time to show you around!