Martina White – Page 4 – The Data Queen

How to Query Multi Value fields in Active Directory using SSIS

Apparently what’s even more difficult than importing data from AD is figuring out how to import multi-value objects from Active Directory. “Description” is an example of a standard AD multi-value field. My client had many custom multi-value fields added to AD and needed to import the data from these fields into tables in a database. You can accomplish this easily this by adding a bit of code to the C# code importing the single value attributes as outlined in my previous post Getting Around AD Paging on SSIS Import.

This C# code is much simpler than trying to import each multi-value field using a Data Flow task. Using Data Flow tasks can be done but it has some tricky problems like importing only those records with values in the multi-value field, working around paging, and how to deal with apparently empty objects that your query returns even though you specified that it only return those objects with values. It’s also quite a bit slower as you need to populate variables and pass those variables to loops to iterate thru the multi-values for one account at a time.

Here is the code for importing one multi-value attribute into a table. This code should be placed at an appropriate spot within the “foreach (SearchResults” loop outlined in the Getting Around AD Paging on SSIS Import post.

string propertyName = “Description”; //or whichever multi-value field you are importing

ResultPropertyValueCollection valueCollection = results.Properties[propertyName];

//Iterate thru the collection for the user and insert each value from the multi-value field into a table

foreach (String sMultiValueField in valueCollection)

{

string sValue = sMultiValueField.Replace(“‘”, “””); //Replace any single quotes with double quotes

sqlConnection1.Open();

cmd.CommandText =

“INSERT INTO User_Descriptions (sAMAccountName,Description) VALUES (‘” + sAMAccountName + “‘,'” + sValue + “‘)”;

reader = cmd.ExecuteReader();

sqlConnection1.Close();

}

The nice thing about this code is that you can iterate through any records, even if the multi-value field is empty. It won’t fail, it just won’t return a record. This means you can add this same chunk of code multiple times edited for several different multi-value fields within the same script task, and have all your tables updated using the same script. The package is very easy to maintain, with no package variables, no complex package logic, just a simple script. Very elegant!

Get around Active Directory Paging on SSIS import

I have a client who is importing certain users from Active Directory. The paging on their AD is set to 20,000 records. When trying to pull data using a SQL statement, the query fails because it hits the maximum number of records and is unable to return more. You could work around a problem like this by editing your query filter to ensure that you always retrieve fewer than 20,000 records at a time, for example using the whenCreated field. However, there is no guarantee that whatever filter you use will always limit your return value to a maximum of 20,000 records. And you now need to build a loop construct to retrieve all the records since you want more than 20,000 records.

This is much easier to solve than you might think, judging from the number of forum questions out there on the subject (and how long it took me to piece it together). Here are the steps.

Create an SSIS package.

Add a string variable, scoped to the package, called SqlConn. Populate it with the connection string to the database you want to populate with the AD records.

Add a script task to your package. Open the script task, making sure that the ScriptLanguage is C# and not VB.

Click on the Edit Script button. On the right hand side you should see the Project Explorer window. Right click on the name of the Project File at the top of the tree and select Add Reference.

On the .NET tab scroll down and find System.DirectoryServices. Select it and click OK.

Make sure you see the reference appear in the References folder in the Project Explorer window.

Add these statements at the beginning of your script.

using System.DirectoryServices;

using System.Data.SqlClient;

Paste this script to replace the public void Main(). Edit the ds.Filter and Insert string values to meet your table requirements. Be sure to only select single value attributes of the object. If you try to use this method to import multi-value attributes such as “Description” from AD it won’t work. I’ll be writing about that next.

public void Main()

{

//Set up the AD connection;

using (DirectorySearcher ds = new DirectorySearcher())

{

//Edit the filter for your purposes;

ds.Filter = “(&(objectClass=user)(|(sAMAccountName=A*)(sAMAccountName=D0*)))”;

ds.SearchScope = SearchScope.Subtree;

ds.PageSize = 1000;

//This will page through the records 1000 at a time;

//Set up SQL Connection

string sSqlConn = Dts.Variables[“SqlConn”].Value.ToString();

SqlConnection sqlConnection1 = new SqlConnection(sSqlConn);

SqlCommand cmd = new SqlCommand();

SqlDataReader reader;

cmd.CommandType = CommandType.Text;

cmd.Connection = sqlConnection1;

//Read all records in AD that meet the search criteria into a Collection

using (SearchResultCollection src = ds.FindAll())

{

//For each record object in the Collection, insert a record into the SQL table

foreach (SearchResult results in src)

{

string sAMAccountName = results.Properties[“sAMAccountName”][0].ToString();

string objectCategory = results.Properties[“objectCategory”][0].ToString();

//Replace any single quotes in the string with two single quotes for sql INSERT statement

objectCategory = objectCategory.Replace(“‘”, “””);

sqlConnection1.Open();

cmd.CommandText = “INSERT INTO Users (sAMAccountName, objectCategory) VALUES (‘” + sAMAccountName + “‘,'” + objectCategory + “‘)”;

reader = cmd.ExecuteReader();

sqlConnection1.Close();

} } } }

That’s it. This will iterate through all of the objects in Active Directory, regardless of paging size set on Active Directory.

To learn how to import multi-value fields from AD, read this post:

How to Query Multi-Value Fields from Active Directory using SSIS

Log not truncating on Checkpoint

I have a client who was running out of disk space due to a database log which was growing exponentially. The database was set to Simple recovery, and the log was set to Truncate on Checkpoint. The log was growing hugely and had to be manually shrunk every few days.

To find out the reason for the log not truncating I ran this query.

SELECT name,log_reuse_wait , log_reuse_wait_desc FROM sys.databases

I found out that the database was not truncating due to an Active Transaction. This can be caused by a long running transaction or by a deferred transaction. http://technet.microsoft.com/en-us/library/ms345414(v=sql.105)

In the meantime the database went into Recovery mode and a number of things happened of which I am not aware. Somehow the active transaction got cleared and the client manually shrunk the log file.

We changed a few settings to minimize the damage should this happen again. The Autogrowth was set to 20% with no maximum. We change the Autogrow settings to something more reasonable, with the initial size of 5GB, and a maximum of 10GB to at least protect the other files from getting stalled due to no space on the drive.

Then I looked at capturing the Active Transaction information. Unfortunately, unless the Active Transaction was running, I was unable to find out what the transaction was. I built a very simple SSIS package which I ran every 5 minutes to discover if an Active Transaction was holding up the log file, and to capture some information about what transaction was running and who was running it.

This package populated a new table in the data warehouse with the attributes of a transaction. To avoid filling the table with information I didn’t need, I wrote the query so it would only populate the table if there were any transactions holding the log open. Here is the source query for the Data Flow. Should the IF EXISTS be negative, the Data Flow task would fail causing the Send Mail Task not to be triggered.

IF EXISTS

–IF log is held up due to Active Transaction
(SELECT name, database_ID, log_reuse_wait , log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait_desc = ‘ACTIVE_TRANSACTION’ AND database_id = <your database id>)

–Find out the user and which query is holding it open
SELECT s.SPID,S.OPEN_TRAN,TEXT,s.Hostname,s.nt_domain,nt_username,net_address,s.loginame,
s.login_time, s.last_batch, s.status, s.sid, s.hostname as [Host_Name],
s.program_name, s.cmd, s.net_library, GETDATE() as InputDate
FROM SYS.SYSPROCESSES S CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE)AS A
WHERE OPEN_TRAN<>0

The really nice thing about this query is that it returns the Text fo the actual query which is holding the log open, as well as program_name which is running it and the Host_Name.

I set up a SQL Server Agent job to run the package every 5 minutes, since I only wanted to capture items which were long running.

Using this package I was able to very quickly find out that there was an automated process on SharePoint which was running every hour but for whatever reason was never able to complete (that’s another story). Because the transaction didn’t successfully complete it remained flagged as Active, the Checkpoint for the log was not removed and the log just kept growing and was never truncated. Once the issue with the SharePoint job was resolved the log file was able to resume normal behaviour and Truncate on Checkpoint as expected.

Populating a table with ISO Weeks using SSIS

I normally use dimDate tables in my projects, but for this client it made sense to have this at the week level. Rather than sticking with my usual dimDate stored procedure to build out the table and attributes, I thought I’d take the opportunity to migrate the logic over to an SSIS package. Here is a quick overview of how it works.

In my SSIS package I created 4 variables. Three of these variables will be populated by the package. Only the StartDate needs to be populated by the user. In this case the first date to be populated in the table is the first day of 2010.

The tasks are fairly straightforward and the finished package looks like this.

I wanted to blog certain parts of this, largely so I can remember them the next time I do this.

I will lay out the important SQL Statements used in these steps. For specifics on how to set up variables and tasks using variables you can check out this post How to Set and Use Variables in SSIS Execute SQL Task

Set @StartDate to first Sunday from @StartDate

This task is in place to ensure that the StartDate is set to the correct day of the week, as required by the business. This client has the start of their business week on Sunday. If the user has entered a date into the package variable which is not a Sunday, this task will set the @StartDate to the nearest Sunday after the date in the variable. The fact that the SQL Server DATEPART ISOWW starts on a Monday will be addressed later in this post. The CAST statement is added to allow the results to be inserted into the @StartDate string variable.

SELECT
CAST(
CASE WHEN DATENAME(dw,?) = ‘Sunday’ THEN ? –@StartDate
ELSE DATEADD(d,8-DATEPART(dw,?),?) END
as nvarchar) as NewStartDate

Set @EndDate to to last ISO Week in current year

The client wants the table to contain weeks up to the last week of the current ISO year. When we move into next year, the new year will automatically be added. You can edit this statement to meet your business requirements. The CAST statement is added to allow the results to be inserted into the @EndDate string variable.

SELECT
CAST(
–Check to see if current year is 52 or 53 weeks
CASE WHEN DATEPART(ISOWW,DATEADD(WEEK,53 – DATEPART(ISOWW,GETDATE()),GETDATE())) = 1
THEN DATEADD(WEEK,52 – DATEPART(ISOWW,GETDATE()),GETDATE())
ELSE DATEADD(WEEK,53 – DATEPART(ISOWW,GETDATE()),GETDATE())
END
as nvarchar)
as EndDate

Set @LoopNumberOfWeeks

This will find the number of weeks between the StartDate and the EndDate for use in the For Loop Container. I added 1, because I chose to start my Loop at 1 rather than 0.

SELECT DATEDIFF(wk,?,?) + 1 as NumberOfWeeks

For Loop Container

The container is set to look through the number of weeks calculated in the Set @LoopNumberOfWeeks task. Note that the InitExpression is 1 instead of 0. I am using the @LoopWeek variable to populate one of the fields in my table, which is why I wanted to start with 1.

Insert record into dimAccWeek_New

INSERT INTO [dimAccWeek_new]
([AccWeekSK],
[AccWeekStartDate])

SELECT
? as AccWeekSK,
? as AccWeekStartDate

Add 7 days to @StartDate

Instead of using the loop to move the date forward one week, I am updating the variable with an Execute SQL Task inside the loop. The CAST statement is added to allow the results to be inserted into the @StartDate string variable.

SELECT
CAST(
DATEADD(dd,7,?)
as nvarchar) as LoopWeek

Update attributes

Here is the meat of it. When using the DATEPART ISOWW on their SQL Server, the ISO Week starts on a Monday. Since business week starts on a Sunday I am adding 1 day when calculating where it falls within a Week, Quarter, Year

UPDATE dimAccWeek_new
   SET AccWeekSK = CASE
    –When date is > the first week and date between Jan 1 and 3 then PrevYr + ISOWeek
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) > 1
    AND DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0101’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0103’
    THEN CAST(DATEPART(Year,AccWeekStartDate)-1 AS varchar) + RIGHT((’00’ + CAST(DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) as varchar)),2)
    –When date = the first week and date between Dec 29 and Dec 31 then NextYr + ISOWeek
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) = 1
    AND AccWeekStartDate
        BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1229’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1231’
    THEN CAST(DATEPART(Year,AccWeekStartDate)+1 AS varchar) + RIGHT((’00’ + CAST(DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) AS varchar)),2)
    –ELSE CurrentYr + ISOWeek
    ELSE CAST(DATEPART(Year,AccWeekStartDate) AS varchar) + RIGHT((’00’ + CAST(DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) AS varchar)),2)END

      ,AccYear = CASE
    –When date is > the first week and date between Jan 1 and 3 then PrevYr
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) > 1
    AND DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0101’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0103’
    THEN CAST(DATEPART(Year,AccWeekStartDate)-1 AS varchar)
    –When date = the first week and date between Dec 29 and Dec 31 then NextYr
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) = 1
    AND AccWeekStartDate
        BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1229’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1231’
    THEN CAST(DATEPART(Year,AccWeekStartDate)+1 AS varchar)
    –ELSE CurrentYr
    ELSE CAST(DATEPART(Year,AccWeekStartDate) AS varchar) END

      ,AccQuarter = CASE
    WHEN DATEPART(mm,AccWeekStartDate) BETWEEN 1 AND 3 THEN 1
    WHEN DATEPART(mm,AccWeekStartDate) BETWEEN 4 AND 6 THEN 2
    WHEN DATEPART(mm,AccWeekStartDate) BETWEEN 7 AND 9 THEN 3
    ELSE 4 END

      ,AccMonth = DATEPART(mm,AccWeekStartDate)
      ,AccWeek = DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate))

,AccWeekEndDate = DATEADD(dd,6,AccWeekStartDate)

You can add as many attributes as you require. This should get you started.

How To Set and Use Variables in SSIS Execute SQL Task

I couldn’t find any step-by-step resources on this so I thought I’d blog it. This is done in SQL Server 2008 R2. In this scenario I will use a SQL Statement to populate an SSIS variable, and then use that variable in another SQL Statement.

Create your variables

If the variables window isn’t visible already, from the menu select View –> Other Windows –> Variables.

This will bring up your variables window on the left hand side of your screen. You should be able to tab between it and the Toolbox. You may have to pin it to your screen first.

I have already created some variables in the screenshot above. To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task. I normally scope all of my variables to the package, since you can’t see the variables in the window unless you have selected the scoped object. I find it easier to manage variables having them all in one place. I have not had any compelling reason not to do it this way, although I’m sure there are cases where you may want them scoped differently.

Before creating the variable you must select the item for it’s scope. In this case select the canvas of the Control Flow which will permit you to scope the variable to the entire package. Then create the variable by clicking on the Add Variable button at the top of the Variables window.

Give your variable a Name, a Data Type and a Value. In this case we are working with EndDate, and although you would want to set it as Date, there are some compatibility issues when using the Date data type for a variable and then pulling into a SQL parameter. I have found it is best to set the date as a string and then manipulate it from there. Set your EndDate attributes as shown, with the Scope being the name of your package.

Populate an SSIS variable using a SQL statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

Set the ResultSet to Single row
Select your Connection to the database where you will run your SQL Statement
Enter your SQL Statement

On the Result Set tab:

In the Result Name key in the name of the field being return in your SQL Statement. In this case I named the field EndDate
In the Variable Name select the user variable you created.

Use the Variable in a SQL Statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

Select your Connection to the database where you will run your SQL Statement
Enter your SQL Statement, placing a ? where each variable is to be used.

In this case I need to add two Parameters to the Parameter Mapping tab, since I have used two question marks in my SQL Statement. Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement. This is the only way to identify which parameter is being used for which question mark. Parameters are made much easier in 2012.

Here’s the tricky bit which isn’t at all self explanatory.

On the Parameter Mapping tab:

Select the user variable in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement.
Set the Data Type to NVARCHAR, since we are using a string variable. (This is the spot where the data type compatibility didn’t mesh, which is why we opted for string).
Set the ParameterName to 0. This indicates the order in which the parameter is appearing in the SQL statement. You can enter them in the pane in any order you want, but the ParameterName must contain numbers starting from 0 to however many parameters you have minus one. So if you have two parameters you must have one ParameterName = 0 and one ParameterName = 1. You can reverse the order, but they must both exist or the task will fail.

Configure SSIS Logs

As a continuation of Creating an SSIS Package Template with Predefined Package Configurations I wanted to share with you another good habit for your package template. I like to add logging to my packages for the purposes of troubleshooting, especially when there are complex processes with multiple packages running at once. It helps me track how long packages take to run and gives me the ability to see if there are changes over time, as well as the obvious error logging.

It is very simple to turn on the logging. Simply right click on your Control Flow canvas and select Logging.

This is another place where having a sequence containers within your package are a good idea. I generally have the entire package within one master sequence container, and then divide up the tasks within other sequence containers as necessary. You can set the logging for the main Sequence Container within your package and you will notice that the other items within that container will inherit the logging configuration. You can deselect certain tasks or containers if required.

I like to log errors in SQL Server. To do this, change the Provider Type to SSIS log provider for SQL Server.

Click the Add button. In the Configuration column use the dropdown to select the connection to the SQL database where you would like to store the logs.

Check the box beside the Package_Template container and then check the box beside the log you have just created.

Select the Details tab. Specify which events you would like to capture.

Keep in mind that each event will take up space in your database. Depending on how many packages you are logging and how often, you may with to only track important events, or you may wish to delete some or all of the events on a scheduled basis.

Here are a few views I like to create for use when checking the logs.

CURRENT ERRORS

CREATE VIEW [dbo].[ssislog_ErrorsCurrent]
AS
SELECT     TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime AS StartTime, LG.endtime AS EndTime,
                      LG.message
FROM         dbo.sysssislog AS LG INNER JOIN
                          (SELECT     LG1.executionid, LG1.source AS PackageName, LG1.starttime
                            FROM          dbo.sysssislog AS LG1 INNER JOIN
                                                       (SELECT     source, MAX(starttime) AS starttime
                                                         FROM          dbo.sysssislog
                                                         WHERE      (event = ‘PackageStart’)
                                                         GROUP BY source
                                                         HAVING      (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
                            WHERE      (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
WHERE     (LG.event IN (‘OnError’))
ORDER BY PackageStartTime DESC, StartTime

CURRENT LOG ENTRIES

CREATE VIEW [dbo].[ssislog_LogEntriesCurrent]
AS
SELECT     TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime, LG.endtime, LG.message
FROM         dbo.sysssislog AS LG INNER JOIN
                          (SELECT     LG1.executionid, LG1.source AS PackageName, LG1.starttime
                            FROM          dbo.sysssislog AS LG1 INNER JOIN
                                                       (SELECT     source, MAX(starttime) AS starttime
                                                         FROM          dbo.sysssislog
                                                         WHERE      (event = ‘PackageStart’)
                                                         GROUP BY source
                                                         HAVING      (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
                            WHERE      (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
ORDER BY LG.endtime DESC

PACKAGE DURATION

CREATE VIEW [dbo].[ssislog_PackageDurationCurrent]
AS
SELECT     TOP (100) PERCENT source AS PackageName, MIN(starttime) AS StartTime, MAX(starttime) AS EndTime, DATEDIFF(MI, MIN(starttime), MAX(starttime))
                      AS DurationInRoundedMinutes, DATEDIFF(ss, MIN(starttime), MAX(starttime)) AS DurationInTotalSeconds
FROM         dbo.sysssislog
WHERE     (event IN (‘PackageEnd’, ‘PackageStart’)) AND (starttime > DATEADD(dd, – 1, GETDATE()))
GROUP BY executionid, source
ORDER BY starttime DESC

I hope you find this useful.

Manually Deploy SSRS Reports to SharePoint

I have a situation where there is an alternate authentication method in place on SharePoint and deploying reports using the Visual Studio deployment options won’t work. To get around this while they sort it out I have manually loaded the reports, data sources and shared datasets to SharePoint. There were a few tricks which I would like to remember so I’ll post them here.

1. Create 3 document libraries:

The first thing I did was create three libraries, one for Reports, one for Shared Datasets and one for Shared Data Sources. You don’t have to have separate libraries, but I find it more user friendly to keep these items separate. I don’t want users weeding through data sets and data sources to get to their reports. Here is how to create these libraries. The one surprise is to use a content type of Report Builder Report for the Shared Datasets. I imagine this is to allow you to configure your Dataset to connect to a Data Source.

2. Create (don’t upload) the Data Source.

Navigate to the Data Source library you created. From the Documents tab select New Document. Do not try to upload a data source you have already created for your report, since, for whatever reason, SharePoint won’t recognize it as a Report Data Source. You need to recreate it.

Configure the data source appropriately. Choose “Stored Credentials” to allow for proxy authentication, and select “Use as Windows credentials”. Click on the Test Connection button to be sure it is working. Click OK.

3. Upload the Shared Datasets:

Navigate to your Shared Datasets library and from the Documents tab you can “Upload Document” or “Upload Multiple Documents” depending on how many shared datasets you have.

4. Connect the Shared Datasets to the Data Source:

Connect the shared datasets to the appropriate data source. Click the drop down beside the dataset and select “Manage Data Sources”.

Click on the “DataSetDataSource”, which will have the yellow caution triangle to let you know it has not been configured.

Click on the ellipsis and navigate to wherever you created the data source in SharePoint.

Click OK and click Close. Do this for all the Shared Datasets you uploaded.

5. Upload the Report:

Navigate to the Report library you created. From the Documents tab select Upload Document and upload your Report Services report.

6. Connect the Report to the Data Source:

From the drop down beside the report select “Manage Data Sources”.

Same as step 4, click on the name of the data source that needs to be connected. Click on the ellipsis and navigate to where the data source is stored in SharePoint. Click OK. Click Close.

7. Connect the Report to the Shared Datasets:

From the drop down beside the report select “Manage Shared Datasets”

From the list of dataset names which need to connected, click on the first one which has a yellow caution triangle beside it. This lets you know that the dataset has not yet been connected.

Click on the ellipsis and navigate to where you have stored your shared datasets. Select the dataset. Click OK. Repeat this for any shared datasets which have not been connected. Click Close.

You are ready to view your report. If you get any data source errors, check that the Shared Datasets are all connected correctly to the data source, as well as the report.

Create a SharePoint SSRS Report Library

For whatever reason this type of library is not out-of-the-box. I have to set it up manually every time. Here are the steps for this particular client. They will be similar for other SharePoint/SSRS set ups. You can follow these instructions to set up an SSRS Data Source library and an SSRS Share DataSet library.

Document type Content Type

Report Services Report Report Builder Report

Data Source Report Data Source

Shared DataSet Report Builder Report

1. Create a Document Library in SharePoint.

Go to Libraries. Click Create. Call your new library “Reports”.

2. Allow Management of Content Types.

Click on your library. Go to Library Settings. Click on Advanced Settings. Change the radio button for “Allow management of content types” to Yes. Click OK.

3. Add Report Content type.

In the Library Setting under Content Types click on “Add from existing site content types”. In this case the client is using Report Builder content types for reporting, which will work fine for Report Services reports.

From the “Select site content types from” drop down, select “Report Server Content Types”. Add any content types you would like to maintain in your Reports library. I prefer to keep data sources and data sets in separate libraries, but some people like to keep them on one library. Take note that whichever content type floats to the top of your Content Type list will be the Default content type for your library. This will matter when creating and adding new documents. Add the default Content Type first, and then any others. Click OK.

If you don’t see the Report Server Content Types listed in the drop down, you may need to activate them on your Site Collection. Read this post to find out how to do that – http://thedataqueenblog.azurewebsites.net/2013/05/activating-ssrs-report-content-types-for-sharepoint/

4. Delete the Document Content Type.

Under Content Types select the “Document” content type.

Select “Delete this content type”.

This will make the Report Builder Report content type the default content type.

You are ready to deploy SSRS reports to your SharePoint library.

If you find that you are unable to deploy your reports using the deploy feature in Visual Studio – for example Visual Studio keeps asking you for credentials when you try to deploy – you may want to manually upload the reports and data sources to SharePoint. Read this post to find out how http://thedataqueenblog.azurewebsites.net/2012/07/manually-deploy-ssrs-reports-to-sharepoint/

SSIS: Connect to PostgreSQL

There is a great blog post on Connecting to a PostgreSQL Instance Using SQL Server Integration Services which you’ll want to read. I will recap the steps directly from this post:

Installation of both drivers (32-bit & 64-bit) is identical. Here are the basic steps to get the driver working:

Download the latest .msi file for Windows from this location: http://www.postgresql.org/ftp/odbc/versions/msi/

Run the msi file on your SQL Server.

Launch the ODBC Administrator Utility and choose the type of data source you need; File, System or User and click Add.

The ODBC Administrator will present a list of drivers. Scroll to the bottom and you will see two options for PostreSQL; ANSI and Unicode. Select the version you need and click Finish.

The Administrator will present a screen on which you must supply a database name, server name, user name and password.

After you have supplied values for these fields, click the Datasource button and make sure the Use Declare/Fetch box is checked. The driver will fail to retrieve larger datasets if you do not check this box. I have not yet found a satisfactory answer for why this is so.

Now you are ready to build a new connection manager in SSIS and hook it to the PostGRES data source you just created. Use the following settings when building out the connection manager:

Select the .Net ProvidersODBC Data Provider.

Select the “Use connection string” radio button. Using the values you configured in the ODBC Administrator, build a connection string as follows: Dsn=PostgreSQL35W;uid=User1

Enter the User name and Password in the fields provided.

Test the connection and you should be ready to go.

Here are a few bits of information I’d like to add to this excellent blog post, mainly for my own purposes should I run into this again:

I noticed on the PostgreSQL msi page that there were several versions of the drivers. I took the most recent of the 32bit, psqlodbc_09_01_0100-1.zip. The 64 bit drivers have “64 bit” in the name.

The 32-bit ODBC drivers for PostgreSQL get installed here C:Program Files (x86)psqlODBC

After installing the 32 bit driver per the instructions, in your Visual Studio solution be sure to change your Project Properties – Debugging – Run64BitRuntime property to False. This will force it to run in 32-bit mode.

If you are triggering your package from SQL Server Agent, set the SQL Server Agent job to run in 32-bit mode

For SQL2012, be sure to put the password in a Project Parameter so it is retained in the package on deployment to Integration Services, and doesn’t get stripped out due to package protection.

Automate Import of SSIS Packages to File System

A client with SQL Server 2005 asked if there was a good way to import and deploy packages automatically, rather than having to do each package manually every time. This particular client has packages deployed to SQL Server Integration Services. I have written a separate blog post on the Automating Deployment of SSIS Packages in SQL 2005. Here is the solution for importing packages.

BIDS Solution:

Open (or create) the BIDS solution for the related packages which you want to import and deploy. The related packages should all reside in the same folder on Integration Services, otherwise the deployment piece will not work as it can’t deploy to multiple folders. You will need a separate BIDS solution for each SSIS folder, and you will need to create a separate _ImportPackages.dtsx package for each solution.

In order to make this somewhat scalable, the solution folder structures will need to incorporate the exact same folder name as the folder on SSIS, since the SSIS folder name will be used in the import query. For example, if your group of packages is deployed to a folder called “Maintenance” on SSIS, you will want your solution to be called something like “Maintenance ETL”. In this way, once you create your _ImportPackages.dtsx you will be able to copy it to the next solution and simply edit the folder name in the query and the Batch File connection manager. You’ll see what I mean.

Create SSIS Package to Import Deployed Packages:

Create a new SSIS package called _ImportPackages.dtsx. I put the underscore in the name so it will float to the top of the SSIS solution when you open it, thereby reminding you to run the package and get the latest copies of the packages deployed to SSIS. The package will look like this:

CREATE CONNECTIONS

DATABASE:

Create a connection to the msdb database on the server where the SSIS packages are being stored.

Although the packages are deployed out to Integration Services

They are actually stored in the MSDB database, which is what you will write your source query against.

BATCH FILE:

Create a Flat File Connection Manager called “Batch File”. Create a flat file called “_Import Packages.bat”. It will house the dtutil commands in a .bat file which will import all the dtsx packages from the MSDB. Choose a location within your BIDS SSIS solution folder, since you will have one of these batch files per BIDS SSIS solution.

There is one column called dtutil_command.

The column width is 4000 to accommodate the length of the dtutil command lines.

STEP 1 in the Package: Delete Batch File

Drag a File System task from the Toolbox into your Control Flow. Rename it “Delete Batch File”, and configure it to the operation “Delete file” with a Source Connection to the ”Batch File connection” you created above.

STEP 2 in the Package – Populate Batch File

Drag a Data Flow task from the Toolbox into the Control Flow surface. Rename it to Populate Batch File. Connect the “Delete Batch File” task to the “Populate Batch File” task on success. Move to the Data Flow tab and drag an OLE DB Source on the Data Flow surface. Configure it as follows:

Here is that Query again, so you can cut and paste it:

select
CAST(
‘dtutil /SQL "’
+ case
when len(FolderName) = 0 then ”
else FolderName + ”
end
+ [name]
+ ‘" /ENCRYPT FILE;"C:BI ProjectsETL’
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘ ETL’ + FolderName + ‘ ETL’
end
+ [name]
+ ‘.dtsx";1’
+ ‘ /QUIET’ –suppresses prompt to delete if an older file with same name exists )
as nvarchar(4000))
as dtutil_command
from msdb.dbo.sysdtspackages90 pkg
join msdb.dbo.sysdtspackagefolders90 fld
on pkg.folderid = fld.folderid
WHERE foldername = ‘Maintenance’ —CHANGE VARIABLE
order by FolderName, [name]

The results of this query will look like this:

dtutil /SQL "MaintenancePackage1" /ENCRYPT FILE;"C:BI ProjectsETLMaintenance ETLMaintenance ETLPackage1.dtsx";1 /QUIET
dtutil /SQL "MaintenancePackage2" /ENCRYPT FILE;"C:BI ProjectsETLMaintenance ETLMaintenance ETLPackage2.dtsx";1 /QUIET

Notice that the folder name from SSIS is also used in the folder structure on the file system. You will need to adjust the query to suit your naming convention for your BIDS solutions.

Drag a Flat File Destination onto your Data Flow tab and connect the OLE DB source to the Flat File destination. Configure the Flat File destination to the Batch File connection.

Step 3 in the Package: Run Batch file

Drag and Execute Process task from the Toolbox onto the Control Flow tab. Connect the “Populate Batch File” task to the “Run Batch File” task on success. Configure the Executable on the Process tab by navigating to the _Import Packages.bat you created earlier.

Now you can run the SSIS package you just created. It will import all the packages in the specified SSIS folder into the solution folder you specified in the SQL Query.

***Please note that this will not add any new packages to your BIDS solution. It will only refresh existing packages with any deployed changes. You will still need to add the packages within the BIDS solution to the SSIS Packages folder even though they are sitting in the file system. You don’t want to simply ‘Add Existing Package’ either, as you will end up with a duplicate file in your BIDS directory. I’ve blogged a simple way to Add Multiple Packages to a BIDS solution.