Are you preparing for your first interview for an SSIS job? This can be a very stressful time so use this article to help you get ready with possible questions likely in an interview. I will also give you tips on what to be prepared for when interviewing with people of different expertise. For example, it is important to have different expectations and to respond differently when you are interviewing with someone non-technical than with a person that has years of experience with SSIS.
Non-Technical Interview
Surprisingly the non-technical interview may be the more difficult interview for a lot of job hunters in the IT industry. Sounds like it should be easier right? You don't have to expect detail questions about development but you do have to build a relationship with the person interviewing you.
Make sure in an interview like this that you know your audience, meaning speak their language. You don't want to lose the person that's interviewing you with overly complicated technical terms. This can cause the other person to feel like they cannot relate to you. Of course this is just with a non-technical interview when you are in a technical interview be ready with all your technical terminology.
Here are some general tips that will help regardless of who is interviewing you:
You can find many websites that will have many more great tips but these are some that I’ve always found useful.
Technical Interview
Interview Questions
1. Tell me about how you use configuration files. Would you consider it a best practice to create a configuration file for each connection manager or one for all your SSIS packages?
2. What techniques do you use for debugging an SSIS package that fails?
3. Why would you use checkpoints and how are they useful?
4. Why would you use transactions and how are they useful?
5. If you have a package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from a SQL Agent Job what would be your first guess on what the problem is?
6. How do you run SSIS packages from the command line?
7. Why would you use breakpoints and how are they useful?
Possible Question Answers
1. There should be a single configuration file for each connection manager in your packages that stores their connection string information. So if you have 6 connection managers then you have 6 config files. You can use the same config file across all your packages that use the same connections.
If you have a single config file that stores all your connection managers then all your packages must have contain the connection managers that are stored in that config file. This means you may have to put connection managers in your package that you don’t even need.
2. Depending on how the package is failing you may use Data Viewers, Breakpoints, or redirect error rows to another output.
3. When checkpoints are enabled on a package if the package fails it will save the point at which the package fails to a XML file. This way you can correct the problem then rerun from the point that it failed instead of rerunning the entire package. The obvious benefit to this is if you load a million record file just before the package fails you don’t have to load it again.
4. If transactions are enabled on your package and tasks then when the package fails it will rollback everything that occurred during the package. First make sure MSDTC (Microsoft Distributed Transaction Coordinator) is enabled in the Control Panel -> Administrative Tools -> Component Services. Transactions must be enabled not only on the package level but also on each task you want included as part of the transaction. To have the entire package in a transaction set TransactionOption at the package level to Required and each task to Supported.
5. The account that runs SQL Agent Jobs likely doesn’t have the needed permissions for one of the connections in your package. Either elevate the account permissions or create a proxy account.
To create a proxy account you need to first create new credentials with the appropriate permissions. Next assign those credentials to a proxy account. When you run the job now you will select Run As the newly created proxy account.
6. SSIS uses the command line prompt dtexec. The syntax looks like this (dtexec /f C:\SSIS\Packages\Example.dtsx /set \package.variables[variablename].Value;"mynewvalue"). http://msdn.microsoft.com/en-us/library/ms162810.aspx
7. Breakpoints put pauses in your package. It’s a great tool for debugging a package because you can place a breakpoint on a task and it will pause the package based on execution events.
A reason in which I have used breakpoints is when I have a looping container and I want to see how my variables are changed by the loop. I would place a watch window on the package and type the variable name in. Set a break point on the container the stop after each iteration of the loop.
Use a Whiteboard
Be prepared to demonstrate how you would build SSIS packages by drawing on a whiteboard. You may be given a problem and the person interviewing you will expect you to show how you would solve it. If you can visually show how to solve that problem by drawing out a solution it can show your brainstorming skills better.
If you have made it to the point where you are answering questions on a whiteboard then you are in a good stage of the interview. If the interview doesn't naturally evolve to using a whiteboard then work it into how you answer the questions by saying, "Would you mind if I showed you how I would do that on your whiteboard?"