SSIS FAQ

The blog list the basic questions on SSIS

Control Flow:

• Process Oriented
• Doesn’t manage or pass data between components.
• It functions as a task coordinator
• In control flow tasks requires completion (Success., failure or completion)
• Synchronous in nature, this means, task requires completion before moving to next task. If the tasks are not connected with each other but still they are synchronous in nature.
• Tasks can be executed both parallel and serially
• Three types of control flow elements in SSIS 2005
• Containers - Provides structures in the packages
• Tasks - Provides functionality in the packages
• Precedence Constraints - Connects containers, executable and tasks into an ordered control flow.
• We can control the sequence execution for tasks and also specify the conditions that tasks and containers run.
• It is possible to include nested containers as SSIS Architecture supports nesting of the containers. Control flow can include multiple levels of nested containers.

Data Flow

• Streaming in nature
• Information oriented
• Passes data between other components
• Transformations work together to manage and process data. This means first set of data from the source may be in the final destination step while at the same time other set of data is still flowing. All the transformations are doing work at the same time.
• Three types of Data Flow components
• Sources - Extracts data from the various sources (Database, Text Files etc)
• Transformations - Cleans, modify, merge and summarizes the data
• Destination - Loads data into destinations like database, files or in memory datasets

How do you do

• Error handling
• Logging
• Deployment & different strategies
• Schedule SSIS packages to run on the fly?
• How do you run stored procedure and get data in SSIS?
• Debugging (Break Point, Data Viewer, SQL Profiler)
• Configuration using XML, DB
• Handle Transactions

Give a scenario: Want to insert a text file into database table, but during the upload want to change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data, upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor. How would you accomplish this task in SSIS?