SSIS variable interview question

What are variables and what is variable scope?

variables are extremely important and are widely used in an SSIS package.A variable is used to store values. A variable is a named object that stores one or more values and can be referenced by various SSIS components throughout the package’s execution.here are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.

Realtime scenario for ssis variable:

We have created a SSIS Package that takes the files from Source Folder and move them to Archive Folder. While it move them, It also add Time Stamp to file. Here is the blog post for the Package  .In this post we want to watch the values of the variables at run time. This can be helpful when we are getting some errors and we are not sure if the value of variables are changed while executing SSIS Package Or our expressions are evaluated correctly that we have written on one or more variables. By looking at the value of variables, It will help us to debug our package.

Solution:

We will be using Break Point in SSIS Package to view variable values at run time. Here are the variable I have used in package. I have changed the values for VarArchiveFullPath and VarSourceFullPath variables.

Expressions are written on these variable those should evaluate at run time and change the values of these variable.

Step 1: 

Right Click on File System Task and go to Edit Breakpoints

Step 2: 

Choose the Break Condition.

Step 3:

Execute your SSIS Package

Step 4:

To view the values of your variable Go to Debug--> Windows--> Locals

Let's see the values of variables win Locals window

We can see that the values are changed according to the expression and we can debug if these values are correct according to our expectations, if not then we can go back to expressions and change them. 

Real time scenario in ssis

One of the real time scenario: 

I am trying to come up with the best and most efficient way to copy more than 700+ tables from an IBM DB2 iSeries schema to SQL Server. I have tried the followings:

1. I have exported all the 700+ tables into csv files but I am not able to upload them all in one batch to SQL. I can upload one table at time but it too time consuming

2. Created SSIS package to directly copy table form IBM to SQL but that's gain working for only one table at a time

Any recommendations as what tool(s) or methods to use for copying/uploading multiple tables from IBM to SQL?

SSIS Real Time Question

How do I access Microsoft Excel 2007 from SSIS 2005 version. I am unable to access the files from SSIS

ANS:
Use ACE connector for Excel.
ACE OLEDB 12.0:

TYPE OLE DB ProviderUSAGE Provider=Microsoft.ACE.OLEDB.12.0MANUFACTURER Microsoft 

Xlsx files

This one is for connecting to Excel 2007 files with the Xlsx file extension. That is the Office Open XML format with macros disabled.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.