Call SSIS Package from the Stored Procedure

Enable the xp_cmdshell option, just run the following script lines in your selected instance in SQL Server

USE master

GO

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'show advanced options', 0
GO

Now, we are ready to use "xp_cmdshell" stored procedure to call our SSIS package.

Here, I have used two special commands, one is "xp_cmdshell" and the second is "dtexec".
Now what is "dtexec" command.

dtexec: The dtexec command prompt utility is used to configure and execute SQL Server 2005 Integration Services (SSIS) packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.
(Reference from: http://msdn.microsoft.com/en-us/library/ms162810.aspx)

Below is the script to execute SSIS packages, we only have to pass the necessary variables.

declare @ssisstr varchar(8000), @packagename varchar(200),@servername varchar(100)

declare @params varchar(8000)

----my package name

set @packagename = 'ImportItemFile'

----my server name

set @servername = 'myserver\sql2k5'

---- please make this line in single line, I have made this line in multiline

----due to article format.

----package variables, which we are passing in SSIS Package.

set @params = '/Set \Package.Variables[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"'

----now making "dtexec" SQL from dynamic values

set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
--- to execute package from file path use below query
---EXEC xp_cmdshell 'dtexec /f "E:\PackageLocation\WellZoneExtraction.dtsx"'

set @ssisstr = @ssisstr + @params

-----print line for verification

--print @ssisstr

----

----now execute dynamic SQL by using EXEC.

DECLARE @returncode int

EXEC @returncode = xp_cmdshell @ssisstr

select @returncode

variable passing structure of the "dtexec" command:

/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

Now the @returncode variable will be returned by the "dtexec" command and it will be two record sets, the first will return the code from the following possible value which will indicate the SSIS package status, and the second table will describe all the processes that happened during execution of the SSIS package.

Value	Description
0	The package executed successfully.
1	The package failed.
3	The package was cancelled by the user.
4	The utility was unable to locate the requested package. The package could not be found.
5	The utility was unable to load the requested package. The package could not be loaded.
6	The utility encountered an internal error of syntactic or semantic errors in the command line.

So, in this way, we can call the SSIS package from the Stored Procedure by using "xp_cmdsjell" and "dtexec" command from the SQL Server.

Error 3154: The backup set holds a backup of a database other than the existing 'xx' database

If you are getting this error than restore database using script

Use Master
RESTORE DATABASE ihkdb01
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ihkdb01.bak'
WITH MOVE 'ihkdb01' TO 'D:\SQLServerRepository\ihkdb01.mdf',
MOVE 'ihkdb01_Log' TO 'D:\SQLServerRepository\ihkdb01_log.ldf',
REPLACE

Method to create Share folder in web server C#

public void CreateSharedFolder(string PhysicalPath, bool bFolderCreated)
{
try
{
int index = PhysicalPath.LastIndexOf("\\");
string AliasFolder = PhysicalPath.Substring(index + 1);
ManagementClass managementClass = new ManagementClass("Win32_Share");
// Create ManagementBaseObjects for in and out parameters
ManagementBaseObject inParams = managementClass.GetMethodParameters("Create");
ManagementBaseObject outParams;
// Set the input parameters
inParams["Name"] = AliasFolder;
inParams["Path"] = PhysicalPath;
inParams["Type"] = 0x0; // Disk Drive
// Invoke the method on the ManagementClass object
outParams = managementClass.InvokeMethod("Create", inParams, null);
// Check to see if the method invocation was successful
if ((uint)(outParams.Properties["ReturnValue"].Value) != 0)
{
if (bFolderCreated)
{
Directory.Delete(PhysicalPath, true);
}
throw new Exception("Unable to share directory as it is already in use.");
}
}
catch (Exception ex)
{
throw new Exception("Error :", ex);
}
}

Method to create Create IIS Virtual Directory in C#

private void CreateIISVirtualDirectory(string PhysicalPath)
{
try
{
string strVDPath = ConfigurationManager.AppSettings["VDPath"];

//Get the folder Name and assign it to alias for creating Virtual Directory

DirectoryInfo di = System.IO.Directory.GetParent(PhysicalPath);

PhysicalPath = di.Parent.FullName;

int index = PhysicalPath.LastIndexOf("\\");
string AliasFolder = PhysicalPath.Substring(index + 1);

DirectoryEntry de = new DirectoryEntry(GetWebSitePath() + "/ROOT/" + strVDPath);
DirectoryEntry newVDir = de.Children.Add(AliasFolder, "IIsWebVirtualDir");
newVDir.Properties["Path"][0] = PhysicalPath;
newVDir.Properties["AccessScript"][0] = true;
// These properties are necessary for an application to be created.
newVDir.Properties["AppFriendlyName"][0] = AliasFolder;
newVDir.CommitChanges();
}
catch (Exception ex)
{
Error.LogError(ex);
throw new Exception("Error :", ex);
}

}

private string GetWebSitePath()
{
string strPath = "";
string strWebSiteName = ConfigurationManager.AppSettings["HostedWebSite"];

DirectoryEntry w3svc = new DirectoryEntry("IIS://" + Environment.MachineName + "/w3svc");

foreach (DirectoryEntry de in w3svc.Children)
{
if (de.SchemaClassName == "IIsWebServer")
{
if (de.Properties["ServerComment"][0].ToString().ToUpper() == strWebSiteName.ToUpper())
{
strPath = de.Path;
break;
}
}
}
return strPath;
}

ABC and Endpoint of WCF

Address: WCF services must have an address. The address specifies the location of the service which will be exposed for clients that will use it to communicate with the service. The address's protocol that WCF can provided: HTTP , TCP ,NamedPipe , Peer2Peer ,MSMQ.
Binding: Specifies how a service is accessible. In other words: how the two parties will communicate in terms of transport (HTTP , TCP ,NamedPipe , Peer2Peer ,MSMQ) ,encoding (text, binary etc.) and protocols (like transactional support or reliable messaging).
Contract: Used to specify what your service can do. For example: give you the square when providing 2 numbers.

Endpoints:

All communications with the WCF service will happen via the endpoints. The endpoints specify a Contract that defines which methods of the Service class will be accessible via the endpoint; each endpoint may expose a different set of methods. The endpoints also define a binding that specifies how a client will communicate with the service and the address where the endpoint is hosted. WCF provides Windows Activation Services which can be used to host the WCF service. Otherwise the WCF service can also be hosted in IIS or in any process by using the Service Host class, which is provided by WCF. Services can also be self-hosted.