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. 

when i restore the sql database i am getting error

when i restore the sql database i am getting error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'MAIN'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The backup set holds a backup of a database other than the existing 'abc' database. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ 

Answer:
You're trying to overwrite existing database abc with a backup of a different database or a backup from another server.  The default action is not to allow that to happen.

If you're sure you're doing the right thing, add the WITH REPLACE option to the RESTORE DATABASE command and it will proceed.  You may also need WITH MOVE options if the backup came from a database whose files were in different locations that the abc database.

Copy All Tables from IBM DB2 schema to SQL Server

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?

ANS:
If you create a table that lists all the tables/files to be imported, and a script that takes a filename and imports the file (possibly with BULK INSERT), you could run multiple copies of that script to do parallel imports.  A flag bit in the table could be used to mark which tables have been chosen by one of the script copies.