Recently we had a great opportunity to work with one of our clients who was implementing a new student information system (Infinite Campus if you’re wondering which one). If you’ve done this before, congratulations, you know what an undertaking it can be!
Our role was to develop an integration architecture that would meet their immediate needs of replacing about 20 integration points between their other systems while setting the foundation that will meet their long term needs for data warehousing, point-to-point systems integration, and data import/export. Oh, I forgot to mention our client was a school district on a tight budget!!
We’re familiar with quite a few integration platforms such as Informatica, Oracle, and IBM… each having great integration functionality; But with a hefty price tag. Microsoft also offers an enterprise class integration platform…. included with SQL Server at < $2,000 per core!! SQL Server 2012 Integration Services was our choice for our client.
At first, I think our client was a bit reluctant at the architecture because it was a bit out of their comfort zone. Most school districts simply script their integration points using what’s available to them (Perl, SQL, VB Script, Excel, Access, etc.) and don’t invest in good integration software. Moving away from the “tried and true” is always a bit difficult, but the benefits using software designed to do integration will always beat out any home-grown solution. But in the end, with some guidance and good examples, they found their feet and are now starting to see this type of architecture will pay off for them in the long run.
The main reason for our success in gaining our client’s trust (and ultimately adoption for the system) was that we were able to line up all the components and fit it into an easy to understand process. Here is what we defined for them for developing exports (data extracts) using SQL Server Integration Services.
Step 1 – Determine the Requirements
It goes without saying, don’t start a project unless you know what you need to do. The complexity any the export package will depend on how complex your transformation requirements are. Some packages can take minutes to develop, whereas others could take days. The requirements should define both the “sources” and “targets” (destination) for your export. and if you need to filter or convert data, they must define the logic (business rules) required to transform the data into the correct target format.
Step 2 – Create/Open a SSIS Project
Developing an export requires that you install SQL Data Tools (from the SQL Server installation disk) first. Once installed, you can create your project and create one or more packages. We recommended that you create a separate project for each “target” system.
Step 3 – Create a new Package and Develop the Export
The outcome of the development is going to be one or more export packages. We ended up creating a few templates for our client to get them started so they could see how to build exports in a variety of formats and destinations.
Step 4 – Test your Export
SQL Data tools includes a debugger which lets you run and debug your packages by running the package locally.
Step 5 – Deploy your SSIS Project
Once you have a working package you can use the “Deploy” option in SQL Data Tools to deploy your package to your Integration Services Catalog. If you’re not using the integration services catalog then you’re probably going to run into configuration issues down the road… so it’s highly recommended. With it, you will be able to audit all your package executions, automatically version your projects, and schedule them with confidence that they will run just like you tested
Step 6 – Schedule your Package to Run Automatically
Finally, we want to automate our exports. This is done by using the SQL Agent to schedule SSIS packages to run on a schedule you set. When you schedule these you can have SQL notify you if something goes wrong too!
Our vision is to be a leader of best practice business intelligence and information management solutions that that improve performance and communication between decision makers, administrators, employees, and the public. We deliver sustainable solutions for our clients by creating scalable designs that meet both short term (immediate) and long term requirements.
Principal consultants at QC are information experts specializing in business intelligence and enterprise information management. We are committed to providing the best service possible for our clients by offering only qualified and experienced consultants that meet their needs.
If you are interested in learning more about SQL Server Integration Services or any of our other services please contact us today!
Shane Quigley is an expert in data warehousing, business intelligence, systems analysis, and solution architecture.