BIML Active Directory SSIS Data Source

Since posting Active Directory SSIS Data Source I've received a few requests for the package. And I always felt bad because there wasn't a nice way to say change, these N things from my copy and it'll work for you and here's a copy for 2005, 2008, 2012 and now 2014. Egads.

Fortunately, there's a new tool in my toolbelt called Biml and it's awesomesauce.

1. Download BIDS Helper
2. Install BIDS Helper
3. Open Visual Studio, create/open an Integration Services project type.
4. Right click on the project and select "Add New Biml File"
5. In your project's Miscellaneous folder, double-click the BimlScript.biml file and paste the following content into it replacing the file's content.
6. Save
7. Edit line 6. Replace HOME.BILLFELLOWS.NET with the name of your domain controller (DC).
   • To determine your DC, from a command prompt, type set | find /i "userdnsdomain"
   • Sample results

     J:\Users\bfellows>set | find /i "userdnsdomain"
     USERDNSDOMAIN=HOME.BILLFELLOWS.NET
     

8. Edit line 46. Replace LDAP://DC=home,DC=billfellows,DC=net with the path to the domain controller. Follow the pattern and you should be fine.
9. Right click on the BimlScript.biml file and choose "Generate SSIS Packages"

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
    <AdoNetConnection
        Name="AD" 
        Provider="System.Data.OleDb.OleDbConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
        ConnectionString="Data Source=HOME.BILLFELLOWS.NET;Provider=ADsDSOObject;Integrated Security=SSPI;" 
        />
</Connections>
<Packages>
    <Package
        Name="ADQueryMulti"
        ConstraintMode="Linear"
        DelayValidation="true"
        >
        <Annotations>
            <Annotation AnnotationType="Description">$header$</Annotation>
        </Annotations>
        <Connections>
            <Connection ConnectionName="AD" />
        </Connections>
        <Variables>
            <Variable Name="RowCountSource" DataType="Int32" Namespace="User">0</Variable>
        </Variables>
        <Tasks>
            <Dataflow
                Name="DFT AD Extract"
                DelayValidation="true"
            >
                <Transformations>
                    <AdoNetSource
                        Name="ADO AD"
                        ConnectionName="AD"
                    >
                        <DirectInput>
                            SELECT
                                distinguishedName
                            ,   mail
                            ,   samaccountname
                            ,   Name
                            ,   objectSid
                            ,   userAccountControl
                            ,   givenName
                            ,   middleName
                            ,   sn
                            FROM
                                'LDAP://DC=home,DC=billfellows,DC=net'
                            WHERE
                                sAMAccountType = 805306368
                            ORDER BY
                                sAMAccountName ASC
                        </DirectInput>
                    </AdoNetSource>
                    
                    <RowCount
                        Name="RC Source"
                        VariableName="User.RowCountSource"
                    />

                    <DataConversion
                        Name="DC NTEXT to DT_WSTR"
                    >
                        <Columns>
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="distinguishedName"
                                    TargetColumn="distinguishedName"
                                    IsUsed="true"
                            />
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="mail"
                                    TargetColumn="mail"
                                    IsUsed="true"
                            />
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="samaccountname"
                                    TargetColumn="samaccountname"
                                    IsUsed="true"
                            />
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="Name"
                                    TargetColumn="Name"
                                    IsUsed="true"
                            />
                            <Column DataType="Int32"
                                    
                                    SourceColumn="userAccountControl"
                                    TargetColumn="userAccountControl"
                                    IsUsed="true"
                            />
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="givenName"
                                    TargetColumn="givenName"
                                    IsUsed="true"
                            />
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="middleName"
                                    TargetColumn="middleName"
                                    IsUsed="true"
                            />
                            <Column DataType="String"
                                    Length="500"
                                    SourceColumn="sn"
                                    TargetColumn="sn"
                                    IsUsed="true"
                            />
                        </Columns>
                    </DataConversion>

                    <DerivedColumns Name="DER Check Account Status">
                        <Columns>
                            <Column DataType="Boolean" Name="IsActive" >(([userAccountControl] &amp; 2) == 2) ? false : true</Column>
                        </Columns>
                    </DerivedColumns>

                    <ConditionalSplit Name="CSPL Filter Inactive Accounts">
                        <OutputPaths>
                            <OutputPath Name="ActiveAccounts">
                                <Expression>IsActive</Expression>
                            </OutputPath>
                        </OutputPaths>
                        <InputPath OutputPathName="DER Check Account Status.Output"></InputPath>
                        <DataflowOverrides>
                            <OutputPath OutputPathName="Default"
                                        Description="AD accounts that are deactivated"
                                        PathAnnotation="SourceName"
                                        ></OutputPath>
                        </DataflowOverrides>
                    </ConditionalSplit>
                    <DerivedColumns Name="bit bucket Active">
                        <InputPath OutputPathName="CSPL Filter Inactive Accounts.ActiveAccounts"></InputPath>
                    </DerivedColumns>
                    <DerivedColumns Name="bit bucket InActive">
                        <InputPath OutputPathName="CSPL Filter Inactive Accounts.Default"></InputPath>
                    </DerivedColumns>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>
</Biml>

If everything went well, you'll end up with a package like this (minus the data viewers)

Caveats

The original limitations still exist. The next post of this will have the script task included to generate group membership. The final installment will use a .NET script source to provide an alternative to the limitations of the query route.