SSIS Questions
1. Demonstrate or whiteboard how you would suggest using configuration files in packages. Would you consider it a best practice to create a configuration file for each connection manager or one for the entire package? 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. Demonstrate or whiteboard how checkpoints work in a package. When checkpoints are enabled on a package if the package fails it will save the point at which the package fails. 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.
3. Demonstrate or whiteboard using a loop in a package so each file in a directory with the .txt extension is loaded into a table. Before demonstrating this tell which task/container accomplishes this and which enumerator will be used. (Big hint on which task/container to use is that it requires and enumerator) This would require a Foreach Loop using the Foreach File Enumerator. Inside the Foreach Loop Editor you need to set a variable to store the directory of the files that will be looped through. Next select the connection manager used to load the files and add an expression to the connection string property that uses the variable created in the Foreach Loop.
4. Demonstrate or whiteboard how transactions work in a package. 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. 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? 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. What techniques would you consider to add auditing to your packages? You’re required to log when a package fails and how many rows were extracted and loaded in your sources and destinations. I like to create a database that is designated for package auditing. Track row counts coming from a source and which actually make it to a destination. Row counts and package execution should be all in one location and then optionally report off that database. There are also third party tools that can accomplish this for you (Pragmatic Works BI xPress).
7. What techniques would you consider to add notification to your packages? You’re required to send emails to essential staff members immediately after a package fails. This could either be set in the SQL Agent when the package runs or actually inside the package you could add a Send Mail Task in the Event Handlers to notify when a package fails. There are also third party tools that can accomplish this for you (Pragmatic Works BI xPress).
8. Demonstrate or whiteboard techniques you would use to for CDC (Change Data Capture)? Tell how you would write a package that loads data but first detects if the data already exists, exists but has changes, or is brand new data for a destination. If for some reason you’ve avoided using a whiteboard to show your ideas to this point then make sure you start on this question! For small amounts of data I may use the Slowly Changing Dimension. More often than not the data is too large to use in such a slow transform. I prefer to do a lookup on the key of the target table and rows that don’t match are obviously new rows that can be inserted. If they do match it’s possible they are updates or duplicates. Determine this by using a conditional split comparing rows from the target to incoming rows. Send updates to a staging table that can then be updated in an Execute SQL Task. Explain that putting updates in a staging table instead of updating using the OLE DB Command is much better for performance because the Execute SQL Task performs a bulk operation.
9. Explain what breakpoints are and how you would use them. 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.
We may be required to log such errors once upon the failure of the complete package or we may want it for each & every task failed during the package execution. So depending upon these requirements we have two types of event handlers in SSIS: 1. OnError 2. OnTaskFailed
OnError: This event is raised when an executable gets some errors due to any reason. This may be due to failure of any of the tasks included in the package.
OnTaskFailed: This event is raised when a task is failed irrespective of the complete package failure.
The important difference between these two is explained below:
Let’s suppose we have a package (Package 1) which has many tasks; one of them is ExecutePackage task. Now this ExecutePackage task executes another package (Package 2) which in turn contains some tasks.
Pacakge 1: Data Flow Task -> Script Task -> ExecutePacakge Task (Package 2) Package 2: Script Task
Now suppose Script task of the package 2 gets failed due to some errors (like with some wrong code inside). Then OnError Event of the package 1 will be raised only once. But OnTaskFailed event of the package 1 will be raised twice; once due to the failure of Script Task of the package 2 and second time due to the failure of the ExecutePackage task of the package 1 (which gets failed due to failure of package 2).
Observations: 1. OnError event is raised when ever there are some errors occurring in any task of the package. These errors may be more than one for a single executable/component like in case of DFT components. And thus OnError event will be raised multiple times, but as explained in the example we took Script Task which is raising only one error. 2. OnTaskFailed event is raised when a task is completely failed. And this will be raised for each & every task involved in that package/parent task.This can be checked by putting a Script Task in the OnTaskFailed event handler of “package 2” with displaying the Source of the failure by using a System variable “SourceName”.