user – The Data Queen

Creating a Proxy User to run an SSIS package in SQL Server Agent

There have been a number of times over the years when I have had to create a Proxy user in SQL Server in order to provide needed access to connections and locations being used in an SSIS package. Sometimes the SQL Server Agent login account simply doesn’t and shouldn’t have the required permissions.

Before using a Proxy account, do check to see if the permissions issue isn’t just that the SQL Server Agent login account has been set up as ‘Local System’. If that is the case, see if you can change it to a domain account specifically created for SQL Server Agent purposes. Check Administrative Tools -> Services on the server where the SSIS SQL Server resides to see what login account the SQL Server Agent is mapped to.

The user mapped to the SQL Server Agent Service Account will need read/write permissions. If you do need to create a new domain login for the SQL Server agent, in SSMS go to Server-> Security (not database security) -> Logins -> left click New Login -> Search -> Locations button -> Entire Directory -> select main domain ->OK -> Sql Agent username-> Check Names button-> OK-> Server Roles-> sysadmin-> OK..

If it turns out that you need to create an SSIS proxy user, edit this script to use the correct username and password and run it to create the proxy user.

USE master 
GO
-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\username', 'password';--SELECT  * FROM [master].[sys].[credentials]
-- Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON sys.xp_cmdshell TO [DOMAIN\username] 
GO

-- Create a credential containing the domain account PowerDomain\PowerUser and its password
CREATE CREDENTIAL MyCredential WITH IDENTITY = N'DOMAIN\username', SECRET = N'password'
GO
USE [msdb]
GO
-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential',@enabled=1
-- Grant SSISProxy access to the "SSIS package execution" subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
-- Grant the login testUser the permissions to use SSISProxy
EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'DOMAIN\username', @proxy_name=N'MyProxy'
GO

You will be able to see the proxy user in SSMS under SQL Server Agent. Is is in the SSIS PAckage Execution section because we added it to the SSIS subsystem in our code.

Now when you create your SQL Server Agent Job you can choose to run the SSIS package as your proxy user with the required permissions,rather than the SQL Server Agent account.

Importing data from Active Directory Using SSIS

This is a neat little trick. My client wanted to filter a report by employee manager, but didn’t have a good way to maintain the list of employees and their managers. There were a few possibilities, but we wanted to use a system that already maintained the relationship between employee and manager. We decided to pull the information out of Active Directory, using an SSIS package.

Addendum Sept. 12, 2012: The approach outlined below works fine for a simple data set where the query will not return more records than the AD paging file size will allow (typically 1000 records), and you are only querying single value attributes. However, I recommend you use this Script Task approach instead of using a Data Flow. It is more powerful and flexible yet still simple method of importing any size data set from Active Directory, regardless of paging file size. Read the step-by-step here: Get around Active Directory Paging on SSIS import

Find the Active Directory Server

The first thing I needed to know was which server Active Directory resided on. I didn’t want to have wait for I.T. to get back to me with that answer. There is a very easy way to find this out if you are logged into the same domain. Just run a command prompt and use the command

ping %USERDNSDOMAIN%

This will return the active directory server name and IP address.

Create the ADO.NET Connection in SSIS

Create your SSIS solution and your package. Now create the connection to the Active Directory server. There is a little trick to this. In order to use the connection in a data flow you need to create an ADO.NET connection. During the create process, in the Provider drop down, change the Provider to OLE DB Provider for Microsoft Directory Services. Then enter your server name and test as normal. If you were to create an OLE DB connection instead of ADO.NET and change the provider, it would not work for Data Flow connections. It must be an ADO.NET connection type.

Use ADO NET Source in your Data Flow

On the data flow tab, drag an ADO.NET Source onto the design surface. Open up the ADO.NET Source Editor and choose the connection you created in the previous step. Choose “SQL Command” for Data Access mode. Now you will write a query to pull the desired information from Active Directory. Your query will look something like this:

SELECT displayName, Mail, Title, physicalDeliveryOfficeName, telephoneNumber, Manager, name, sAMAccountName
FROM ‘LDAP://yourservername’
WHERE objectClass=’user’

or this, depending on how you like to structure your LDAP queries.

<LDAP://yourservername>; (objectClass=user); displayName, Mail, Title, physicalDeliveryOfficeName, telephoneNumber, Manager, name, sAMAccountName

You must put single quotes around the server. For more information on what attributes are available to be imported from Active Directory I found this to be helpful: http://www.kouti.com/tables/userattributes.htm.

You can test your query in SSMS like this:

SELECT displayName, Mail, Title, physicalDeliveryOfficeName, telephoneNumber, Manager, name, sAMAccountName

FROM Openquery (adsi, ‘SELECT displayName, Mail, Title, physicalDeliveryOfficeName, telephoneNumber, Manager, name, sAMAccountName

FROM “LDAP://yourservername” WHERE objectClass = ”user” ‘)

For this SQL Query to work you will need to add your AD server as a linked servier on your SQL server and name it ADSI. In SSMS open up Server Objects and right click on Linked Servers. Select New Linked Server. On the General tab select “OLE DB Provider for Microsoft Directory Services” as the Provider. Type in your AD server name in the Product Name and the Data source. Leave the other fields blank and click OK.

When running your query, if you get an error like this: “Cannot fetch a row from OLE DB provider “ADsDSOObject” for linked server “adsi”, there is a good chance your query is returning more rows than the AD paging file size allows. In that case you can do one of two things: 1) filter your query further to return fewer rows (try filtering on a specific sAMAccountName to be sure that the query syntax isn’t the problem) or 2) use a C# script which is not affected by paging file size as outlined here Get around Active Directory Paging on SSIS import .

Data Formatting

Each field will be of NTEXT type. You can leave them as NTEXT if this is an acceptable format for your destination, or you can add Derived Columns which convert the values in an expression, like this:

(DT_WSTR,50)sAMAccountName

Add your Destination

Add your data flow destination and map the fields as you wish. In this case I created a table in the data warehouse to receive the Active Directory data.

You can now finish your package and deploy it. This is a simple but very powerful method. I found it very useful and I know I’m going to be using it again.

Addendum Sept. 12, 2012: This approach works fine for a simple data set where the query will not return more records than the AD paging file size will allow (typically 1000 records), and you are only querying single value attributes. However, I recommend you use this Script Task approach instead of using a Data Flow. It is more powerful and flexible yet still simple method of importing any size data set from Active Directory, regardless of paging file size. Read the step-by-step here: Get around Active Directory Paging on SSIS import

Credits

In preparing this post, I found the following articles to be useful:

http://social.technet.microsoft.com/wiki/contents/articles/processing-active-directory-information-in-ssis.aspx

http://www.kouti.com/tables/userattributes.htm

Trigger an SSIS Package from SharePoint using Nintex Workflow

There are many times when my clients want to be able to refresh data on demand, without having to wait for a nightly data warehouse refresh. Quite often this occurs when they are updating data in SharePoint lists which get fed into the data warehouse and from there into cubes and reports. There is a very simple way to allow a user to trigger the data refresh of just their specific piece of data, using Nintex Workflow. If you don’t know about Nintex, it is a third party product which adds a drag-and-drop workflow designer and advanced workflow features to SharePoint. It is inexpensive, and a great way to empower users to manage their business processes.

In this example a user has updated a series of SharePoint lists with current vendor scorecard data and would like to see it reflected in the cube and reports.

SSIS PACKAGES BEST PRACTICES

You will need to create an SSIS package which triggers the data refresh desired by the user. When building my SSIS packages which populate a data warehouse I like to keep it modular, with related actions in individual packages, and then use a master package to trigger the packages in the correct order. In this way I can trigger smaller jobs simply by creating additional master packages to run the relevant packages which already exist. This is best practice since I only have to maintain the logic in one package, which can then be used by multiple master packages. I normally create one package for each of my dimension (attribute) tables and one for each of my fact (transactional) tables wherever possible, unless they are interdependent. I try to keep each package as modular as possible so I can trigger them in different sequences where necessary.

Using a master package to trigger a sequence of packages has many advantages over doing multiple steps in SQL Server Agent. I only trigger one master package using a SQL Server Agent Job. It allows me to keep the order of running a sequence of packages in SSIS where I can add annotations to remind myself why certain packages need to be run before others. This helps me remember the interdependencies when I am making changes. It also allows me to control the checkpoints, which gives me the ability to rollback multiple steps where necessary. And it helps me when reviewing the SSIS logs. I can see how long the master package takes to run from beginning to end, and compare that over time.

CREATE A NEW SSIS MASTER PACKAGE

For this example I have a master package which populates the entire data warehouse and refreshes the data cube at the end. This is run nightly. I will add an additional master package, to be triggered by the user, which only refreshed the vendor scorecard data from the SharePoint lists into the data warehouse and refreshes the data cube. We’ll call it MASTER Vendor Scorecard Refresh.

CREATE A SQL SERVER AGENT JOB

Now create a SQL Server Agent Job with one step which triggers the SSIS package you just created. Do not schedule this job, since it will be triggered manually by the user.

You will want to add a notification to email you when the job completes while you are doing your testing. You could change the notification to when the job fails once you know it’s working correctly.

CREATE A SHAREPOINT LIST AND WORKFLOW

Create a SharePoint list, called Vendor Scorecard Data Refresh. Add one item to the list. You could name the item Trigger Vendor Scorecard Data Refresh Workflow. In your List tool bar select Workflow Settings, Create a Workflow in Nintex Workflow.

Leave the template as Blank and click the Create button.

In the bottom left of your screen choose the workflow action type of Integration.

From the resulting actions which appear in the Workflow Actions pane, drag the Execute SQL action onto your workflow.

Double click in the center of the Execute SQL icon to configure the action. Select OLEDB, key in your connection string.

Data Source=yourserver;Initial Catalog=yourdatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;

check the box for Use Windows authentication. Key in the username and password that has permissions to to trigger the SQL Server Agent Job. Enter the query which will trigger the job.

EXEC msdb.dbo.sp_start_job N’Vendor Scorecard Refresh’

Click on Save to save the action. Publish the workflow by clicking on the Publish button.

TRIGGER THE WORKFLOW MANUALLY

Navigate to your Vendor Scorecard Data Refresh SharePoint list. Click on the dropdown beside your one item and select Workflows.

Select the workflow you want to start. You can store more than one workflow on a SharePoint list, but this can get confusing to users so I recommend you keep it to one workflow per SharePoint list unless there is a very good reason to do otherwise.

Click the start button to trigger the workflow.

If you set up notifications on the SQL Server Agent job you will receive an email when the job completes. The email will tell you if the job was successful.

If you did not set up notifications you can check the history of the job. The history will not appear on the job until the job completes, so you will need to wait a few minutes for the steps in your SSIS package to complete before you can check if it ran successfully. In SSMS right click on the SQL Server Agent Job and select View History. You will see a green checkmark beside the date and time if the job ran successfully. If it fails you will see a red X.

That’s it, your done. Users can now trigger their own data updates as required.