How to transfer the variables from execute sql task to script task in ssis.

In this example I am fetching records from persons table using execute sql task and the same record I want to add in customer table, to do this I created one stored procedure called “insertcustoner” , this stored procedure will insert records in customer table and we are  calling it in script task(c # code).

Step1: Declare variables in package.

Step2: Drag and drop one execute sql task from toolbar. Click on that make result set property to “full set”, maintain all other property as show in picture…

 

Step3: Add result set variable(this variable will contain all records fetched from persons table ).

 

Step4: Drag and drop one ForEach loop container, click on that go to collections select “ForEach ADO Enumerator” then select ADO Object Source variable, here you have to select the result set variable of previous step.

 

Step5: Do the mapping of variable which you already fetched from persons table(here one variable means the one record fetched from one column and the variable numbers are limited to number of columns you fetched)

 

Step6: Drag and drop one script task from toolbar, click on that, in script option add read only variables those you already declare in mapping of ForEach loop. Or the number of columns you fetched in second step.

Step7: Then add following code.This code will take  three parameters as input pid, name, address. These parameters contains the  one single row from result set variable. In this code the “insertcustoner” is a stored procedure name.

Note: We can call package variables using this code.

 Dts.Variables["MyCaseSensitiveVariableName"].Value;

string cs=@"server=NWS1026\SQLEXPRESS;database=practice;trusted_connection=yes";

SqlConnection conn = new SqlConnection(cs);

SqlCommand command = new SqlCommand("insertcustoner", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@name", SqlDbType.VarChar).Value = Dts.Variables["name"].Value;

command.Parameters.Add("@pid", SqlDbType.VarChar).Value = Dts.Variables["pid"].Value;

command.Parameters.Add("@address", SqlDbType.VarChar).Value = Dts.Variables["address"].Value;

//command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;

conn.Open(); int rows = command.ExecuteNonQuery();

conn.Close();

How to execute stored procedure in sql server using SSIS Execute Sql Task.

For this we have to create one stored procedure in sql server. The below query will create one stored procedure to insert into customer table and it takes three parameters as inputs to insert into customer table. The customer table has three columns pid , name and address.

create procedure insertcustoner(@pid int,

@name varchar(50),@address varchar(50))

as

begin

insert into custoner values(@pid,@name,@address)

end

Now we can  execute this stored procedure using Execute Sql Task in SSIS.

Step1: Declare four variables id, name, address and outres (Full Result will store in this variable of type Object).

Step2: drag and drop  Execute Sql Task from control flow. click on Execute sql task and do the following steps. 

• Configure Result set as ‘Full result set’.
• Give connection type as OLEDB
• In connection give database name
• We configured the SQLSourceType property to direct input.
• In SQL Statement write this query: - exec insertcustoner  ?,?,?

In this query we are transferring the parameter values fetched from first execute task and to stored procedure input parameters. The EXEC keyword used to execute stored procedure.

Step3 : Click the result set option on left side of the editor and add ‘outres’ variable here. Mention result name as 0.

Step 4: Then drag and drop one foreach container and give the enumerator type as “ForEach ADO Enumerator”. Select “outres” variable in ADO Object Source Variable.

Step 4: Then in variable mapping add what all variable(records) you retrieved in first execute sql task. Here the number of variable we are mapping is exactly match with the number of columns we are fetching in first execute sql task.

Step5: click on Execute Sql Task in ForEach Loop and configure the editor as shown in below figure

Step 6: configure parameter mapping property as shown in below figure.

Step 7: Finally our package look like this.

Taking Backup and Restore of database using SSIS.

"This operation we can do using Analysis Service Execute DDL Task and Script Task".

Analysis Service Execute DDL task: The Analysis Services Execute DDL Task is a very useful one, allowing you to do just about anything with a SQL Server Analysis Services instance.  For example you could backup and restore a database, process a cube, create a partition, merge partitions, etc.  You specify commands to be executed using XML for Analysis (XMLA) which is the native XML protocol for all interaction between a client application and a Microsoft SQL Server Analysis Services instance. 

Script Task: The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. You use the Script task for work that must be done once in a package (or once per enumerated object), instead than once per data row.

-Procedures to take backup of a database

Step 1: Add a string variable to the package; we will use this variable to contain the XMLA script to perform the backup.  Right click on the Control Flow, select Variables from the context menu, then enter the variable as follows

Step 2: Drag and drop the Script Task from the Toolbox onto the Control Flow of the SSIS package.  Edit the Script Task and add the package variable created in Step 2 above to the ReadWriteVariables property.  We will assign the XMLA script to this variable in the next step.   

Step 3: Click the Edit Script button in the Script Task Editor and enter the following XMLA script to take the backup of database

 Note: Here "cubes" is the database name.

 The backup file name is modified to include the current date.  The resulting XMLA is stored in the package variable named ‘x’.  The use of ${BACKUPFILENAME} for the text to replace is purely arbitrary, but hopefully intuitive.

Step 4: Open the Analysis Services Execute DDL Task editor, click on DDL in the list box on the left, and set the properties as follows:

The XMLA to execute is defined in the package variable that we setup in the previous step.
At this point you can execute the SSIS package and you will see the backup file created; the default location is specified in the BackupDir property for the Analysis Server; e.g. C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup.

 Finaly our package look like this.

** To restore add this code in script task all other steps are similar **