3/24/2011

Microsoft SSIS Package to Query Active Directory into SQL Server Table

Now here's an excellent reference on Microsoft SSIS Package to Query Active Directory into SQL Server Table.

It requires some tweaks but just about got it working today.

http://www.mssqltips.com/tip.asp?tip=1657

Our Active Dirctory stores the user info as well as all the Cost Centers associated with a user. So when a user goes to run a report, we have a hidden variable @User in the SSRS report, we run a query against our local SQL-Server table to see what cost centers are associated with the user, and display just those cost centers in the parameter drop down. Pretty groovy!!

So we use Active Directory Groups to restrict folder and report access in SSRS and will implement the cost center security on the data so the user can only see their stuff.

Here's what our package looks like:











And now the Script:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.DirectoryServices
Imports System.Collections

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        ' TODO: Replace "vs" with your domain name
        Dim domain As String = "enter_your_domain_here"
        ' TODO: Replace "LDAP://dc=vs,dc=local" with your dc
        Dim searchRoot As New DirectoryEntry("LDAP://168.123.45.67/DC=enter_your_domain_here,DC=local", Nothing, Nothing, AuthenticationTypes.Secure)

        Dim dirSearch As New DirectorySearcher(searchRoot)
        Dim props As ResultPropertyCollection
        Dim values As ResultPropertyValueCollection
        Dim key As String
        Dim userAccountName As String
        Dim departmentHome As String
        Dim jobtitle As String
        Dim GroupName As String
        Dim email As String
        Dim displayName As String
        Dim groups As ArrayList

        dirSearch.SearchScope = SearchScope.Subtree
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.PropertiesToLoad.Add("memberof")
        dirSearch.PropertiesToLoad.Add("department")
        dirSearch.PropertiesToLoad.Add("title")
        dirSearch.PropertiesToLoad.Add("mail")
        dirSearch.PropertiesToLoad.Add("displayname")
        dirSearch.Filter = "(objectclass=person)"
        'dirSearch.Filter = "(objectCategory=group)"
        dirSearch.PageSize = 1000

        Using searchRoot
            Using results As SearchResultCollection = dirSearch.FindAll()
                For Each result As SearchResult In results
                    props = result.Properties
                    For Each entry As DictionaryEntry In props
                        key = CType(entry.Key, String)
                        If key = "samaccountname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            userAccountName = CType(values.Item(0), String)
                        End If
                        If key = "department" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            departmentHome = CType(values.Item(0), String)
                        End If
                        'job title
                        If key = "title" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            jobtitle = CType(values.Item(0), String)
                        End If
                        'email
                        If key = "mail" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            email = CType(values.Item(0), String)
                        End If
                        'displayName
                        If key = "displayname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            displayName = CType(values.Item(0), String)
                        End If
                        'set the groups here (its in an array/collection
                        If key = "memberof" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groups = GetGroups(values)
                        End If
                    Next
                    'export all fields here
                    For Each item As String In groups
                        If userAccountName.EndsWith("$") = False Then
                            OutputBuffer.AddRow()
                            OutputBuffer.UserAccountName = userAccountName
                            OutputBuffer.Domain = domain
                            OutputBuffer.GroupName = item.ToString
                            'department (4 digits)
                            If Len(item.ToString) >= 5 Then
                                If item.ToString.Contains("-") Then
                                    If IsNumeric(Left(item.ToString, 4)) Then
                                        OutputBuffer.CostCenter = Left(item.ToString, 4)
                                    End If
                                End If
                                OutputBuffer.DepartmentHome = departmentHome
                                OutputBuffer.JobTitle = jobtitle
                                OutputBuffer.Email = email
                                OutputBuffer.DisplayName = displayName
                            End If
                        End If
                    Next
                Next
            End Using
        End Using

    End Sub

    Private Function GetGroups(ByVal values As ResultPropertyValueCollection) As ArrayList
        Dim valueList As ArrayList = New ArrayList()
        For Each Item As Object In values
            Dim memberof As String = Item.ToString()
            Dim pairs As String() = memberof.Split(",".ToCharArray)
            Dim group As String() = pairs(0).Split("=".ToCharArray)
            valueList.Add(group(1))
        Next
        Return valueList
    End Function
End Class



And the other script:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.DirectoryServices
Imports System.Collections

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        ' TODO: Replace "vs" with your domain name
        Dim domain As String = "enter_your_domain_here"
        ' TODO: Replace "LDAP://dc=vs,dc=local" with your dc
        Dim searchRoot As New DirectoryEntry("LDAP://168.123.45.67/DC=enter_your_domain_here,DC=local", Nothing, Nothing, AuthenticationTypes.Secure)

        Dim dirSearch As New DirectorySearcher(searchRoot)
        Dim props As ResultPropertyCollection
        Dim values As ResultPropertyValueCollection
        Dim key As String
        Dim groupAccountName As String
        'Dim departmentHome As String
        'Dim jobtitle As String
        Dim groupDept As String
        Dim groupDeptName As String
        'Dim email As String
        'Dim displayName As String
        Dim groups As ArrayList

        dirSearch.SearchScope = SearchScope.Subtree
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.PropertiesToLoad.Add("memberof")
        'dirSearch.PropertiesToLoad.Add("department")
        'dirSearch.PropertiesToLoad.Add("title")
        'dirSearch.PropertiesToLoad.Add("mail")
        'dirSearch.PropertiesToLoad.Add("displayname")
        'dirSearch.Filter = "(objectclass=person)"
        dirSearch.Filter = "(objectCategory=group)"
        dirSearch.PageSize = 1000

        Using searchRoot
            Using results As SearchResultCollection = dirSearch.FindAll()
                For Each result As SearchResult In results
                    props = result.Properties
                    For Each entry As DictionaryEntry In props
                        key = CType(entry.Key, String)
                        If key = "samaccountname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groupAccountName = CType(values.Item(0), String)
                        End If
                        'If key = "department" Then
                        '    values = CType(entry.Value, ResultPropertyValueCollection)
                        '    departmentHome = CType(values.Item(0), String)
                        'End If
                        ''job title
                        'If key = "title" Then
                        '    values = CType(entry.Value, ResultPropertyValueCollection)
                        '    jobtitle = CType(values.Item(0), String)
                        'End If
                        ''email
                        'If key = "mail" Then
                        '    values = CType(entry.Value, ResultPropertyValueCollection)
                        '    email = CType(values.Item(0), String)
                        'End If
                        ''displayName
                        'If key = "displayname" Then
                        '    values = CType(entry.Value, ResultPropertyValueCollection)
                        '    displayName = CType(values.Item(0), String)
                        'End If
                        'set the groups here (its in an array/collection
                        If key = "memberof" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groups = GetGroups(values)
                        End If
                    Next
                    'export all fields here
                    For Each item As String In groups
                        'If userAccountName.EndsWith("$") = False Then
                        OutputBuffer.AddRow()
                        'OutputBuffer.UserAccountName = userAccountName
                        OutputBuffer.Domain = domain
                        OutputBuffer.GroupAccountName = groupAccountName
                        'OutputBuffer.GroupDept = item.ToString
                        'Groups
                        OutputBuffer.GroupDeptName = item.ToString
                        If Len(item.ToString) >= 5 Then
                            If item.ToString.Contains("-") Then
                                If IsNumeric(Left(item.ToString, 4)) Then
                                    OutputBuffer.GroupDept = Left(item.ToString, 4)
                                End If
                            End If
                            'OutputBuffer.DepartmentHome = departmentHome
                            'OutputBuffer.JobTitle = jobtitle
                            'OutputBuffer.Email = email
                            'OutputBuffer.DisplayName = displayName
                        End If
                    Next
                Next
            End Using
        End Using

    End Sub

    Private Function GetGroups(ByVal values As ResultPropertyValueCollection) As ArrayList
        Dim valueList As ArrayList = New ArrayList()
        For Each Item As Object In values
            Dim memberof As String = Item.ToString()
            Dim pairs As String() = memberof.Split(",".ToCharArray)
            Dim group As String() = pairs(0).Split("=".ToCharArray)
            valueList.Add(group(1))
        Next
        Return valueList
    End Function
End Class

It's been working for almost a year now!

We can now query Active Directory from a SQL-Server table...

Not too shabby!

CREATE TABLE [dbo].[stg_UserGroupList](
 [Domain] [nvarchar](50) NOT NULL,
 [AccountName] [nvarchar](50) NOT NULL,
 [Group] [nvarchar](50) NOT NULL,
 [DomainUser]  AS (([Domain]+'\')+[AccountName]),
 [FK_DimUser] [int] NULL,
 [FK_DimRole] [int] NULL
)

3/23/2011

1st Quarter 2011 - Summary

Well, the year 2011 is almost 1/4 over. What do we have to show for it?

Started a new job Jan 31, Applications Administrator for the school board. Getting some managerial experience. Wrote some SSRS reports. Dabbled in PHP. Learned some AJAX. Wrote some c# connecting to SSRS. Learned the SSRS server environment. Queried Active Directory using T-SQL.

Attended the SQL-Saturday and pre-conference for Business Intelligence. Attended the February and March Tampa Bay BI Users Group. Got a book on SSRS and PHP/Linux/MySQL. Joined the BI site BIDN.com posted a few entries. Bought a new Dell computer. Attended OBIEE training early January at the old job.

Still working my part time contract at night. Wrote an SSIS package. Converted a bunch of Crystal Reports to SSRS. Created new SSRS reports. The part time hours slowed down in Feb/Mar. Been doing this one since last August and the one prior since March 2010.

Budget cuts are immenent at the school board. Next week is Spring Break -will try to relax in the Georgia mountains. Overall the year is off to a good start.

I wonder what's in store for 2nd Quarter?

Query Active Directory (AD) from SQL


We are attempting to query Active Directory through SQL to populate a SQL Table.  Here's what I've got so far (changed the IP address/table names).  It's a work in progress.  I've read about a limitation on returning # of rows per query, have not tackled that yet.
SELECT *
FROM OPENQUERY (ADSI,
      '
      SELECT
            mail, displayName
            ,userPrincipalName,sAMAccountName
            ,CN,givenName,SN,name,objectCategory
            ,company,department,manager
      FROM ''LDAP://168.123.45.67/DC=county,DC=local1''
      WHERE objectCategory=''person'' AND objectClass = ''user''
      ')
UNION
SELECT *
FROM OPENQUERY (ADSI,
      '
      SELECT

            mail, displayName
            ,userPrincipalName,sAMAccountName
            ,CN,givenName,SN,name,objectCategory
            ,company,department,manager

      FROM ''LDAP://168.123.45.67/DC=county,DC=local1''
      WHERE objectCategory=''group''
      ')
ORDER BY objectCategory

Enjoy!

3/17/2011

AJAX - Javascript on Steroids

Today I experimented with AJAX embedded in PHP.

After working with Javascript for the past decade, I was surprised to see the simalarities.  The power behind  Javascript is the code can run on the client which allows for field validation.  Now you can have Javascript reference the AJAX calls from the Client to the Server.  You can basically update portions of your web page without having to make a full round trip back to the server.

This concept is quite amazing!  And it runs in PHP, .net, JSP, Cold Fusion.

Good stuff.

3/16/2011

PHP Web Development

Well, today trying something new.  PHP Web Development.

Just customizing some existing code to add another drop down list to the web page dynamically populated from the database depending on the user selection of another drop down list.

I enjoy the challenge!!!

3/14/2011

Tampa Bay SQL BI

Tampa Bay Business Intelligence User Group Meeting tonight


Mike Davis from Pragmatic Works presenting on:


SQL Server 2008 R2 Reporting Services


6:00pm to 8:30pm @ Microsoft Tampa Regional Offices

Tonight's meeting was pretty good - the speaker, Mike Davis knew his stuff when it came to SSRS and some cubes/SSAS topics.  The talk centered around Parameters in SSRS, hidden, internal, multi select, cascading, populating from query, stored procedure vs. inline coding, the need for the dbo.split function.

I felt pretty good that I already knew most of this stuff.  But it was good to see it in 2008 R2 version as I mostly work in 2005 version which doesn't have all the bells and whistes.

It's quite amazing how deep Microsoft BI goes.  Like embedding your own custom dlls into the custom code feature.  Crystal can't compete with such diverse methods exposing all the underlying classes in Visual Basic.net.

And the best part about tonight's event, the free pizza!

3/11/2011

Parameter Limitation in Local Mode

When to Use Local Processing
Local processing mode is recommended for applications that include small to medium-sized reports and datasets. Because all data and report processing is performed on the client, you might encounter performance degradation if you attempt to process large or complex reports and queries. Local processing mode is also recommended if you require a straightforward deployment strategy where all parts of the application run together on the same computer.

Local processing mode is less powerful than remote processing and is intended for standalone applications that do not require a report server. Users who are familiar with server reports that run on a remote SQL Server Reporting Services report server should note the following differences:

Report parameters in client report definitions (.rdlc) do not map to query parameters. There is no parameter input area in a client report definition that accepts values that are subsequently used in a query.


Client report definitions do not include embedded query information. You must define data sources that return ready-to-use data for the report.

Browser-based printing through the RSClientPrint ActiveX control is not available for client report definitions that run in the ReportViewer Web server control. The print control is part of the report server feature set.

If you are affected by these differences, you should either migrate to a Reporting Services installation or write application code that provides the functionality you require.

Click to view URL

3/10/2011

Render Sub Report in Report Viewer c#

After a lot of trial and error, I've added a sub report to the main report and rendered the application in c# app.

this.focus1800.DataSetName = "CurrentSchedule";
this.dataTable1TableAdapter = new ReportingServiceExample.Focus1800TableAdapters.DataTable1TableAdapter();
this.dataTable1TableAdapter.Connection.Open();
this.dataTable1TableAdapter.Fill(this.focus1800.DataTable1, strStudent);

Assembly _assembly = Assembly.GetExecutingAssembly();
StreamReader subReport = new StreamReader(_assembly.GetManifestResourceStream("ReportingServiceExample.subCurrentSchedule.rdlc"));

this.reportViewer1.LocalReport.SubreportProcessing +=
new SubreportProcessingEventHandler(SubreportProcessingEventHandler);

this.reportViewer1.LocalReport.LoadSubreportDefinition("subCurrentSchedule", subReport);

this.reportViewer1.RefreshReport();
}

void SubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
{
e.DataSources.Add(new ReportDataSource("CurrentSchedule", this.focus1800.DataTable1));
}

3/09/2011

c# code to export local ssrs report to pdf


I got the Visual Studio 2008 c# app to export the Transcript Report (removed the sub-reports) to pdf.

        public void ExportReport(object sender, EventArgs e)
        {
            Microsoft.Reporting.WinForms.Warning[] warnings;
            string[] streamids;
            string mimeType;
            string encoding;
            string extension;

            // The application is responsible for collecting parameters
            ReportParameterInfoCollection parameterInfo = reportViewer1.LocalReport.GetParameters();

            string strUser = "BLOOMJON";
            string strReportType = "T";
            string strPermNum = "";
            string strPCSBSchNum = "2081";
            string strGrade = "09";
            string strStudent = "5205008373";
            string strInstrSet = "";
            string strWIP = "Y";

            _paramDescriptor = parameterInfo;

            //SetParameters();
            _parameters = new ReportParameter[8];
            _parameters[0] = new ReportParameter(_paramDescriptor[0].Name);
            _parameters[1] = new ReportParameter(_paramDescriptor[1].Name);
            _parameters[2] = new ReportParameter(_paramDescriptor[2].Name);
            _parameters[3] = new ReportParameter(_paramDescriptor[3].Name);
            _parameters[4] = new ReportParameter(_paramDescriptor[4].Name);
            _parameters[5] = new ReportParameter(_paramDescriptor[5].Name);
            _parameters[6] = new ReportParameter(_paramDescriptor[6].Name);
            _parameters[7] = new ReportParameter(_paramDescriptor[7].Name);

            _parameters[0].Values.Add(strUser.ToString()); //user
            _parameters[1].Values.Add(strReportType.ToString()); //ReportType
            _parameters[2].Values.Add(strPCSBSchNum.ToString()); //PCSBSchNum
            _parameters[3].Values.Add(strGrade.ToString()); //Grade
            _parameters[4].Values.Add(strStudent.ToString()); //Students
            _parameters[5].Values.Add(strPermNum.ToString()); //PermNum
            _parameters[6].Values.Add(strInstrSet.ToString()); //InstrSet
            _parameters[7].Values.Add(strWIP.ToString()); //WIP

            // Add report parameters
            reportViewer1.LocalReport.SetParameters(Parameters);

            try
            {
                // Add the customer data source
                this.fOCUS_SS_1800DataSet.DataSetName = "CourseHistory";
                this.sp_StuCrsHistTableAdapter = new ReportingServiceExample.FOCUS_SS_1800DataSetTableAdapters.sp_StuCrsHistTableAdapter();
                this.sp_StuCrsHistTableAdapter.Connection.Open();
                this.sp_StuCrsHistTableAdapter.Fill(this.fOCUS_SS_1800DataSet.sp_StuCrsHist, strStudent, strGrade, strPCSBSchNum);

            }
            catch (NullReferenceException ee)
            {
                System.Console.WriteLine("{0} Caught exception #1.", ee);
                Console.WriteLine(String.Concat(ee.StackTrace, ee.Message));
            }
            catch (ConstraintException c)
            {
                System.Console.WriteLine("{0} Caught exception #2.", c);
                Console.WriteLine(String.Concat(c.StackTrace, c.Message));
            }

            reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("CourseHistory", this.fOCUS_SS_1800DataSet.sp_StuCrsHist));
            //this.reportViewer1.RefreshReport();

            byte[] bytes = this.reportViewer1.LocalReport.Render(
               "PDF", null, out mimeType, out encoding,
                out extension,
               out streamids, out warnings);

            FileStream fs = new FileStream(@"c:\transcript.pdf",
               FileMode.Create);
            fs.Write(bytes, 0, bytes.Length);
            fs.Close();

        }

Root Cause Analysis