Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

An Introduction to SQL Server Integration Services: Part 1 (Control FLow and Data FLow Overview)

  • 27 March 2012
  • Author: RCharCox
  • Number of views: 6428

This is the first installment of a series that I, a beginning user of SSIS and general novice to the tech industry, will use to both dive deeper into understanding this essential service from Microsoft as well as to educate those who, like me, need a higher level of explanation when it comes to this IT stuff.


SSIS performs data migration tasks, which, in layman's terms, is the transferring of data between storage types, formats, or computer systems.  For example, it takes a report that you generate using TSQL and saves it as a file that can be accessed by users around the company. 


When working with SSIS, the solution is the main directory for what you're working on.  In each solution, there is a project.  Projects can contain several objects, like SSIS packages or reports in SSRS.  As with all BIDS projects, when using SSIS you will have several windows to use:  The properties window, the toolbox, and the solution explorer are but a few.  Many people refer to SSIS packages as being similar to building with legos:  There are a lot of different pieces you can use to manipulate the data, and they all fit together somehow.  An SSIS Package starts out with a Control Flow. 


These are made up of three basic elements:  a container, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an "ordered control flow."  A control flow can have several tasks, although they are usually kept to as few tasks as possible for simplicity's sake.  Some examples of the basic tasks that come with MS BIDS are the "Execute SQL" task, "File System" task, and "Data Flow" Task. 


The data flow is used to automate the process of backing up files, pushing data to the users, or generating the reports needed by the users.   Each data flow consists of, logically, a source, some sort of transformation, and a destination.  The source points to the original file, database, or spreadsheet, which is then taken and transformed (by several means, like filtering out data, altering it through a derived column to standardize the results, joining two or more data sources, or even something as simple as doing a row count or aggregation function), and then deposited into a destination, which could be a new table in a database, an excel spreadsheet, or a flat file like a .txt, to name a few.

Rate this article:
No rating


Other posts by RCharCox

Please login or register to post comments.