Monthly Archives: February 2013

Enable / Disable Users (not Logins) in SQL Server

A DBA friend of mine came across a strange problem the other day. He discovered that one of the users in a SQL server database was disabled, thats a database user not a server login. At least we thought it was disabled at first, it behaved as if it was disabled and it had the small red down arrow icon that disabled server logins have, but as we all know there is no way to enable or disable database users in the Management Studio GUI.

I had a quick Google and was quite surprised to discover that lots of people had the same ‘issue’ but most peoples workaround was to delete the user and re-create it.

After some more research, it turns out that the user didnt have CONNECT permissions to the database (which I guess amounts to the same as being disabled). You can grant CONNECT permissions using the code:

USE YourDatabase
GRANT CONNECT TO User1

Or to remove the permissions:

USE YourDatabase
REVOKE CONNECT FROM User1

You can check which users in a database have the CONNECT permission by executing this T-SQL:

USE YourDatabase
SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'User1'
Advertisements
Tagged , , , ,

Remotely running a project-deployed SQL 2012 SSIS Package

Introduction

I’m working on a project at the moment that requires me to run a SSIS package remotely from an existing .NET application. The application also needs to be able to pass parameters to the SSIS package before executing it. A result which states whether the package executed successfully or not should then be returned to the application.

While I’ve found some half decent instructions for doing this using a web service: Loading and Running a Remote Package Programmatically. This doesn’t apply if you happen to be using the new project deployment method in SSIS 2012 – and it just so happens that I am. Also, ASMX web services are in my opinion a bit old school (Visual Studio doesn’t even have a template for ASMX web Services if you are using .NET 4) – so I decided to come up with my own version of this solution using WCF.

Prerequisites

I’m assuming at this point that you have already created the SSIS package that you want to run remotely. In my case my SSIS package has some required parameters.

I’m also making the assumption that you have an existing .NET application from which you will invoke your new WCF web service.

Full source code can be downloaded at the end of this post.

Now let’s get started.

Creating the WCF web service

First create a new project in Visual Studio (I’m using Visual Studio 2010) and select the template for a WCF Service application.  Here I am using C# and .NET Framework 4 – I’ve called my web service “PackageLauncher”.

ssis_wcf_1

Traditionally we can use the Microsoft.SqlServer.Dts.Runtime assembly to interact with SSIS but this does not support packages that have been deployed with the new 2012 project deployment model. However, after some digging I found that there is a new assembly specifically designed for SQL 2012 project deployed SSIS packages: Microsoft.SqlServer.Management.IntegrationServices.

The Microsoft.SqlServer.Management.IntegrationServices assembly is intalled in the GAC when you install SQL Server 2012 with SQL Data Tools, but does not show up in the .NET tab of the ‘Add Reference’ window in Visual Studio. In order to add a reference to it in your solutions you need to browse to it, its located in:

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91

We also need to add the following assembly references that can be found on the .NET tab of the ‘Add Reference’ window:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo

Creating the PackageLauncher Interface

Once you have added the references mentioned above the first thing to do is configure the interface for our web service.  Open the code for the interface, it will currently be called IService1.cs and will contain some sample code.  As the comments at the top of the page say we can rename our interface by right-clicking where it says IService1 and choosing ‘Rename’ from the ‘Refactor’ menu – lets rename it to IPackageLauncher.  This is also a good time to rename the file itself to IPackageLauncher.cs.

We can remove the sample code in this file so delete all code inside:

public interface IPackageLauncher {

}

Also remove the sample [DataContract] class.  Your IPackageLauncher.cs file should now look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

namespace PackageLauncher
{
	// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.
	[ServiceContract]
	public interface IPackageLauncher
	{

	}
}

At this point we need to create a couple of Data Contracts of our own – the first will hold input parameters (some of which will be used to execute the package and some will be passed to the package itself). Note that these DataContract classes are placed just outside of the IPackageLauncher interface:

[DataContract]
public class PackageParameters
{
	string _SSISCatalog;
	string _SSISFolder;
	string _SSISProject;
	string _SSISPackage;
	string _SourceFile;
	string _UploadedBy;

	[DataMember]
	public string SSISCatalog
	{
		get { return _SSISCatalog; }
		set { _SSISCatalog = value; }
	}

	[DataMember]
	public string SSISFolder
	{
		get { return _SSISFolder; }
		set { _SSISFolder = value; }
	}

	[DataMember]
	public string SSISProject
	{
		get { return _SSISProject; }
		set { _SSISProject = value; }
	}

	[DataMember]
	public string SSISPackage
	{
		get { return _SSISPackage; }
		set { _SSISPackage = value; }
	}

	[DataMember]
	public string SourceFile
	{
		get { return _SourceFile; }
		set { _SourceFile = value; }
	}

	[DataMember]
	public string UploadedBy
	{
		get { return _UploadedBy; }
		set { _UploadedBy = value; }
	}
}

The second will contain parameters that the Web service will return to our host application. These paramenters will tell us whether the package executed sucessfully, and if not; the error messages that were received:

[DataContract]
public class PackageReturnValues
{

	bool _ExecutionSuccessful;
	List _ErrorMessages;

	[DataMember]
	public bool ExecutionSuccessful
	{
		get { return _ExecutionSuccessful; }
		set { _ExecutionSuccessful = value; }
	}

	[DataMember]
	public List ErrorMessages
	{
		get { return _ErrorMessages; }
		set { _ErrorMessages = value; }
	}

}

We can now create an OperationContract in the IPackageLauncher interface that takes in our PackageParameters and returns our PackageReturnValues:

[OperationContract]
PackageReturnValues ExecuteSSISPackage(PackageParameters PackageParameters);

Creating the Service Class

The next step is to create the code for our service class, this will contain the method that executes our SSIS package. To do this open the Service1.svc.cs file, again I am going to rename the class to “PackageLauncher” and the “Service1.svc” file to “PackageLauncher.svc”. As before, the sample code in this file can be removed, so delete all code within the “PackageLauncher” class.

Note that this class inherits our IPackageLauncher interface, if we right-click IPackageLauncher and select “Implement Interface > Implement Interface Explicitly” the ExecuteSSISPackage method will be created for us automatically.

We can now start to write the code that executes our SSIS package. The first thing we need to do is add some references to a couple of assemblies that we are going to need:

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;

Now we delete the line of code that throws the Not Implemented exception and then create some objects to hold our return values and any errors that might occur:

PackageReturnValues returnValues = new PackageReturnValues();
List errors = new List();

Now we need to create a try/catch block to catch any errors that might occur. If an errror does occur we set the ExecutionSuccessful property of our returnValues to False and add an error to our errors collection. The rest of our package execution code will be placed inside this try/catch:

try
{
	[The rest of our code will go here...]
}
catch (Exception ex)
{
	returnValues.ExecutionSuccessful = false;
	errors.Add(ex.Message);
}

No we need to make a connection to our SQL server (here I’m logging in with windows authentication but obviously in a real-world situation you should use an account created specifically for executing SSIS packages) and create an instance of the IntegrationServices class using our SQL connection:

SqlConnection connection = new SqlConnection(@"Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;");
IntegrationServices integrationServices = new IntegrationServices(connection);

Project deployed SSIS packages are stored in the following structure: Catalog > Folder > Project > Package. We can use this line of code to get a handle on our package:

PackageInfo package = integrationServices.Catalogs[PackageParameters.SSISCatalog]
	.Folders[PackageParameters.SSISFolder].Projects[PackageParameters.SSISProject]
	.Packages[PackageParameters.SSISPackage];

By default the SSIS package will be executed asynchronously. The idea being that when you execute a package an Execution ID is returned and you can poll the server to check on the status of the package, however in this example we want to simply run the package synchronously and wait for it to finish. This can be achieved by adding the SYNCHRONIZED execution parameter before executing the package (Note: add this code inside the innermost of our if statements (in the location highlighted above)):

var executionValueParameters = new Collection();
executionValueParameters.Add (
	new PackageInfo.ExecutionValueParameterSet {
	ObjectType = 50,
	ParameterName = "SYNCHRONIZED",
	ParameterValue = 1
	}
);

My package has a couple of parameters that I need to provide so I set those now:

package.Parameters["SourceFile"].Set(
	ParameterInfo.ParameterValueType.Literal,
	PackageParameters.SourceFile
);

package.Parameters["UploadedBy"].Set(
	ParameterInfo.ParameterValueType.Literal,
	PackageParameters.UploadedBy
);

package.Alter();

We can now execute our package and get an execution status (which we use to set the ExecutionSuccessful property of our returnValues object):

long executionIdentifier = package.Execute(false, null, executionValueParameters);

ExecutionOperation execution = integrationServices.Catalogs[PackageParameters.SSISCatalog].Executions[executionIdentifier];
switch (execution.Status)
{
	case Operation.ServerOperationStatus.Canceled:
		returnValues.ExecutionSuccessful = false;
		break;

	case Operation.ServerOperationStatus.Failed:
		returnValues.ExecutionSuccessful = false;
		break;

	case Operation.ServerOperationStatus.Success:
	  returnValues.ExecutionSuccessful = true;
		break;

	default:
		returnValues.ExecutionSuccessful = false;
		break;
}

We now get any error messages from the execution and store them in our errors List. Errors have the message type of 120 – so we use a LINQ query to filter the execution messages and then convert them to a List of strings:

errors = execution.Messages.Where(m => m.MessageType == 120)
	.Select(m => m.Message)
	.ToList();

Finally, at the very end of the method after the catch block we copy our errors List into our returnValues object and the return it:

returnValues.ErrorMessages = errors;
return returnValues;

And thats it! You can now hit F5 to load up the WCF Test Client and Invoke the ExecuteSSISPackage method with some custom parameters.

Source Code

You can download the source code here (Note: This code is just a sample and you should implement proper error handling and fully test before you use this on a production system):

Package Launcher.zip

Tagged , , , , ,