If you want to work in the database management industry, you’ll need to know SSIS. SQL Server Integration Services (SSIS) involves schedule, maintaining and transferring data on your Microsoft SQL database servers. SSIS integration replaces the old data transformation services (DTS) that would take data from one location and import it into your database.
SSIS can do much more than the old DTS application. SSIS is also faster and less buggy. DTS used older technology while SSIS is the latest in Microsoft’s data scheduling software. At a basic level, SSIS is a way for database administrators to import data or run data maintenance tasks against a database without performing these tasks manually. You don’t have to be an expert in SSIS to work with Microsoft SQL Server, but there is a very good chance that your interviewer will ask you some questions on the system for any database related job.
1) What is SSIS?
SSIS was first introduced with SQL Server 2005, which was the next generation of SQL Server software after SQL Server 2000. SSIS is a form of ETL (extraction, transformation and load), which is a database term that integrates data scheduling and tasks. The SSIS engine automation many data maintenance tasks, so you can update data without manually firing procedures and imports.
2) How is SSIS different from DTS?
Older versions of SQL Server used DTS, which was similar to SSIS. DTS let you create steps that you would then assign a priority order. With SSIS, you can separate data from work flow, and SSIS has significantly better performance than older DTS packages. While performance isn’t always an issue when running jobs during off-peak hours, it’s a problem when you must run jobs during normal business hours. You can run SSIS during business hours without too much performance degradation.
3) What is SSIS’ control flow integration?
When you create a package, you usually need some tasks to complete before you can move on to the next task. SSIS lets you control the order in which each data task is performed. This is important, because the wrong data flow can cause major issues with your data and sometimes cause severe data corruption.
4) What is data transformation?
Data transformation is a vague term, because you can pull data from any format and transform it to any other format. That is the goal of data transformation. In many database jobs, you’ll have data listed in a simple file such as a CSV or Excel file. Your job is to automatically pull data from this file and import it into your database tables. You can sometimes perform data updates and do “scrubbing” to the data to clean it up, because these flat files can contain raw data that needs to be better formatted. All of this can be accomplished using an SSIS package.
5) What can you do in an SSIS task?
A task is one step in your SSIS job. A task can be almost database transformation step. It can be connection to another database, importing data from a file or database table or running a stored procedure against your database tables. You can also customize tasks with the Microsoft .NET language, which makes SSIS a very powerful tool with your database.
6) What are the result statuses for your tasks?
There are three statuses after a task attempts to complete. The first status is “Success,” which allows the next task to perform. You set what happens after a task is successfully run. The next status is “Failure.” This status seems self explanatory, but it’s very vague. A failure can be a number of problems, and you must view your server logs and investigate the issues. A failure will stop the rest of the SSIS job from running, so you must fix the failed task to allow the rest of your transformation steps to run. It’s not always easy figuring out the problem, but you can run each task one by one to help identify the issue.
The final status is “Complete.” This status tells you if the task is completed, which means all steps within that task were fully completed.
7) What kind of containers can you use with SSIS packages?
There are three types of containers: sequence, for loops and for each loops. A sequence container is a simple way to group similar tasks together. Think of a sequence container as an organization container for more complex SSIS packages.
A for loop contain is what you can use to repeat steps a certain number of times. Perhaps you need to update records ten times. You can use this for loop container to complete a number of tasks 10 times before moving on to another step.
A for each loop can only be used in a collection. A collection is an enumerated object. For instance, you can use the for each loop to a specific number of records in a record set or a certain number of files in a directory. The for each loop is better than the standard for loop, because you don’t accidentally make a mistake of looping through numerical values too many times and causing an error in your SSIS package.
8) What kind of variables can you create?
You can create global variables and task level variables in SSIS. For programmers, these variables are the same as global and function level variables. A global variable is available to all tasks across the entire job. Variables created in tasks are only available within that task.
These are just a few questions you might be asked when you go into an SSIS job interview. Make sure you study and understand SSIS before you answer any
No comments:
Post a Comment