In modern software development, not only does a software developer need to develop code and scripts efficiently but also rapidly. When it comes to serving as a SQL developer, building anything beyond simple queries is time consuming and hard to debug. However, rapid SQL development and painless debugging is possible with SQL Server Integration Services (SSIS) that comes with the Microsoft SQL Server program suite. With an easy-to-use graphical interface, SSIS lets SQL developers readily build SQL programs that fulfill a variety of needs. From data mining, to data warehouse development, to automation of basic tasks, SSIS handles it all in a fraction of the time it would take to actually write the SQL code yourself.
The great user interface within SSIS makes ease of use one of the first major advantages of SSIS for creating SQL Server packages. SQL Server Integration Services requires knowledge of SQL and coding practices, but a majority of SQL development is done through pop-up wizards and by creating flow charts – SSIS handles all of the rest. In fact, the most typing-intensive portion of the SSIS application is labeling the different components so your flow chart can be understood by other SQL developers.
SSIS is extremely helpful when it comes to creating complex software applications within the SSIS framework. All you need to do is open your toolbar, drag a few components onto the screen, edit them through wizards, and then connect them together with arrows. Even complex tasks such as data mining or fuzzy logic can be handled with just dropping a few components and setting their properties.
In the snapshot above you can see a very basic structure of a SQL Server package applying fuzzy logic to some data inside of a data warehouse. As you can see, what would take a conventional SQL development process significant programming and testing can be resolved by an SSIS developer in a fraction of time with minimal coding. The box at the very top of the chart pulls the data out of a table within the database, and the next box groups the data together for the fuzzy logic. The third component, Lookup Existing Duplicates, checks to see if that customer already exists in the database. Any rows that matched continue along the green arrow, but any that do not match go along the red arrow to the Fuzzy Lookup component. All the data is unioned together and it all continues down the track. No complex logic, just dropping a component, linking something to it, tell it what to match, and voila! More time was spent labeling the components to better understand what is going on when looking at the flow chart than actually putting the flow chart together. The entirety of the SQL Server package was completed by an SSIS developer who can now spend more time understanding the business logic.
The best part is that setting up your connections is clear. Creating a connection manager (seen at the bottom of the screenshot) just requires the name of the database and the table holding the information. The SQL Server Integration Services handles all of the rest, and even allows testing the connection before moving on. That’s how everything is in SSIS – Click, set things up in the wizard, and SSIS handles the rest.
Another remarkable aspect of SSIS development is its use to manage data warehousing development. The SSIS developer toolbox includes a component to extract information from any file type, regardless if your data is stored in Microsoft Excel, inside a database, inside XML files. SQL Server Integration Services even has a component that takes any raw file type and converts it into information that SSIS can handle. For each of these components, SSIS includes another one that converts the information into that file type as well. So you can easily convert information from one file type to another by just dropping two components into your flow chart.
This is a significant benefit when it comes to data warehouse development. An SSIS developer can upload data from anywhere and clean it before inserting it into any critical databases. You can have the SQL Server package clean out any corrupt data, collect data by looking up against other tables, and any other data manipulations you would like, and then dump all the data wherever you want. What would typically take several hours building with traditional SQL Server development can be developed more rapidly and consistenly with SSIS.
The SSIS application can even do basic and intermediate data mining for you. Just like fuzzy logic, mass bulk insertions, and executing tasks and procedures from other programs, all you need to do is drop the component into your flow chart. Then mark what items the component should perform data mining for and where the information should be stored and you are done. No messy SQL server development scripts to mine the data yourself, no needing to research and create complex algorithms. Just drag, drop, click and you are finished.
The greatest advantage that a SQL developer gets from using SSIS is clearly the enhanced ability to debug your code. We’ve all been there – you are working on an SQL script and you execute your file, only to have SQL Server tell you in general text “Something is wrong somewhere around this word.” Many SQL developers have gone bald pulling out their hair over the generic nature of these error messages. However, when performing SSIS development, the debugging process is improved in multiple ways.
When a SQL developer natively executes the SQL Server package within the SSIS application, he/she can watch what is happening in real time. While a component is working it is highlighted as yellow. When a component completes, it is highlighted green. Should a component fail, it highlights itself as red, letting the SQL developer know exactly where it crashed. It will even show along every arrow exactly how many rows went down that specific path, so the SQL developer can watch their data in real time traverse the flow chart and see what is going where.
Sometimes just knowing what component crashed does not tell a SQL developer exactly what went wrong. This is where the progress table comes in. While an SQL Server package is executing, the process tab updates itself with exactly what the SQL Server package is doing and when. If the application hits an error, the SQL developer can open up the progress tab and look for a red circle. Once the SQL developer finds the red circle, the SSIS application will tell the developer what the component did to make it crash, giving an SSIS developer much needed insight into what is ailing their software application.
But if that’s not enough, there are even more helpful tools that SQL Server Integration Services has in its weaponry. An SSIS developer can place data viewers on any path in their flow chart, which allows the developer to see the data that is traversing that path. So if some corrupt data that the SSIS developer is not aware of is crashing a component, he or she can see that data and know what needs repairing. SSIS even includes logging functionality that breaks everything down into specific details. If a component fails, it will tell the SSIS developer what data caused the error. SQL Server Integration Services takes out many of the mysteries of debugging so SSIS developers can know what they need to fix as fast as possible.
The SSIS event handler can also handle crashes, providing the ability to create more stable applications. The SQL Server package can even be designed to email the developer when something goes wrong and restart itself if it needs to. An SSIS developer can design separate event handlers for different parts of the package, catching and handling errors in any number of ways. And the event handlers use the same process as the rest of the SSIS application. Just open up an event handler, create a flow chart for what it should do when it catches that event, and it’s time to test!
When it comes to data mining development, cleansing and housing information in a data warehouse, applying fuzzy logic, or any other tasks that SQL can handle, SQL server integration services is indispensable to rapid and stable application development for novice and experienced SQL developers alike. After working through some tutorials, an experienced SQL developer can become an SSIS developer and begin applying SSIS for their ETL needs. In the field of database development, particularly in the realm of data warehouse development, few technologies are capable of rapid development and are as easily debugged as SSIS.