posted 6/27/2010 by MikeMollenhour - Views: [2192]
Recently I had a customer that wanted to use their current active directory grouping and security for their reporting services security. To accomplish this of course we had to have rights to their active directory server. The next step was to add a link server to their SQL Server. In our case this was the SQL Server 2008 but that should not be necessary. Since there are several articles already written about adding a link server to SQL Server to reference an active directory server I will just provide one URL in database Journal written by “The Mac”. Here is that link
http://www.databasejournal.com/features/mssql/article.php/3849891/Query-Active-Directory-Data-from-SQL-Server-using-T-SQL.htm.
Next I did a little research on trying to find the right query to pull the information of the groups and users and their relationship. Since I wanted to find users within each group that are specifically designed to be reporting services group I first needed to query the groups for these reporting services. To accomplish this I first wrote this query keeping in mind we uniquely named our reporting services groups with RPT_ prefix. Here is the rough query where "domain" equals the name of the domain. Notice that the object class is filtered by group and the other filter is the name of the group. Notice there is a small difference when we do a “like” query from the LDAP it just references the prefix ended with a wildcard of “*”.
SELECT Name as GroupName
FROM OPENQUERY( ADSI, 'SELECT Name FROM ''LDAP://DC=”Domain”,DC=com'' WHERE objectClass = ''Group'' and name = ''RPT_*''’)
Now that we have this list of groups we can simply loop through and find members of each group. The following is an example of how to get these members. You will note in the query that we still have the reference to our domain but this time we are using the memberof function and also the the group name that we pulled in before notated as @GroupName.
SELECT distinct convert(Varchar(500),SAMAccountName) As UserName , Mail
FROM OPENQUERY( ADSI, 'SELECT mail,SAMAccountName FROM ''LDAP://DC=” Domain”,DC=com'' WHERE memberOf=''cn=@GroupName Users,ou=Groups,OU=Application Users and Groups,dc= “Domain”,dc=com''')
This query then gives us all the users belonging to the group specified by @groupname. You can accomplish loading your own tables via SSIS and the for each loop transform. Once this table is populated with relationships between groups and users you can now limit your reporting security on a group basis. This allows you to simplify and lessen the need to maintain the user and group relationship in your tables.
Great article!
Mike, after you got the list, did you then update the security tables in RS? I'm not finding much information out there about how to 'match' AD groups to RS groups.
Thanks,
Lori
Lori,
I did not actually go as far as to integrate with the RS group since this security model was more about filtering both vertically and horizontally. You should be able to do this through the RS Database, though I haven't tried it. RS already integrates into AD for the most part I think you can get around the problem by assigning right to a group or is there something more specific you are planing on doing?
Mike
Hi Mike,
found your post yesterday- that's exactly what I need :-)
I have there one question:
How do you call this for filling your tables? Do you use this directly in the SSIS? Is this possible? Or are you using this as a kind of stored procedure?
Thanks in advance!
BI-Tec
Mike had stored procedures that were executed from within an SSIS package. A linked server to AD needed to be done to do this. This was scheduled nightly to pull down the users from AD.
I hope this helps.
Thanks!
That was the information I needed :-)
Cause in the past I tried to get the Users directly over a script component, and this approach failed. I was able to get all the AD-Groups, but not the members of each one :-(
I think "@GroupName" ist his parameter for the sp, or? Does this really works, if you are using this within the select statement for ADSI?
And for what is "Users" (after the parameter?)
Actually the above scripts were used in SSIS the First gets the list of groups and puts them in an SSIS object Variable. The SSIS loop then runs the second script using the group name and replacing the =@GroupName varible. Hope that helps I will try to remember to upload my code for a generic ssis package when I get home tonight.
Sorry Mike. I knew that we had to have a linked server created. I would assume that is still being used by the data source when making the query, but it has been a while since I looked at what you did there. :)
the generic package sounds good :-)
Couldn't find a way to attach a file so I copied the dtsx package file and pasted below:
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2"> <DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property> <DTS:Property DTS:Name="VersionComments"></DTS:Property> <DTS:Property DTS:Name="CreatorName">TBC\mmollenhour</DTS:Property> <DTS:Property DTS:Name="CreatorComputerName">TSQL30D</DTS:Property> <DTS:Property DTS:Name="CreationDate" DTS:DataType="7">4/1/2010 11:32:48 AM</DTS:Property> <DTS:Property DTS:Name="PackageType">5</DTS:Property> <DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property> <DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property> <DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property> <DTS:Property DTS:Name="VersionMajor">1</DTS:Property> <DTS:Property DTS:Name="VersionMinor">0</DTS:Property> <DTS:Property DTS:Name="VersionBuild">132</DTS:Property> <DTS:Property DTS:Name="VersionGUID">{1F4E1937-7EDB-42CA-8D2A-EB9D938ECA0E}</DTS:Property> <DTS:Property DTS:Name="EnableConfig">-1</DTS:Property> <DTS:Property DTS:Name="CheckpointFileName"></DTS:Property> <DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property> <DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property> <DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property> <DTS:ConnectionManager> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:Property DTS:Name="ObjectName">EDW</DTS:Property> <DTS:Property DTS:Name="DTSID">{95A6D951-DFB7-4C24-BDE6-D034212C1792}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager> <DTS:Property DTS:Name="Retain">0</DTS:Property> <DTS:Property DTS:Name="ConnectionString">Data Source=.;Initial Catalog=EDW;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-GetADUsers-{95A6D951-DFB7-4C24-BDE6-D034212C1792}EDW;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager> <DTS:Property DTS:Name="LastModifiedProductVersion">10.0.1600.22</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">1033</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:Variable> <DTS:Property DTS:Name="Expression"></DTS:Property> <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property> <DTS:Property DTS:Name="Namespace">User</DTS:Property> <DTS:Property DTS:Name="ReadOnly">0</DTS:Property> <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property> <DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property><DTS:VariableValue DTS:DataSubType="ManagedSerializable" DTS:DataType="13"><SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <SOAP-ENV:Body> <xsd:anyType id="ref-1"> </xsd:anyType> </SOAP-ENV:Body> </SOAP-ENV:Envelope></DTS:VariableValue> <DTS:Property DTS:Name="ObjectName">oGroups</DTS:Property> <DTS:Property DTS:Name="DTSID">{3936CF34-C38E-4187-BA00-B8CD399EACB2}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable> <DTS:Variable> <DTS:Property DTS:Name="Expression"></DTS:Property> <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property> <DTS:Property DTS:Name="Namespace">User</DTS:Property> <DTS:Property DTS:Name="ReadOnly">0</DTS:Property> <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property> <DTS:Property DTS:Name="IncludeInDebugDump">2345</DTS:Property><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue> <DTS:Property DTS:Name="ObjectName">sGroupID</DTS:Property> <DTS:Property DTS:Name="DTSID">{B96217F9-2043-403D-BCC5-905A515C7FC2}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable> <DTS:Variable> <DTS:Property DTS:Name="Expression">"SELECT distinct * FROM OPENQUERY( ADSI, 'SELECT mail,Name FROM ''LDAP://DC=tbccorp,DC=com'' WHERE memberOf='cn=TK MEMDFS Users,ou=Groups,OU=Application Users and Groups,dc=tbccorp,dc=com'') " </DTS:Property> <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property> <DTS:Property DTS:Name="Namespace">User</DTS:Property> <DTS:Property DTS:Name="ReadOnly">0</DTS:Property> <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property> <DTS:Property DTS:Name="IncludeInDebugDump">2345</DTS:Property><DTS:VariableValue DTS:DataType="8">RPT_TestRight1</DTS:VariableValue> <DTS:Property DTS:Name="ObjectName">sGroupName</DTS:Property> <DTS:Property DTS:Name="DTSID">{0D1162C4-9E6E-41D6-9DBB-45E9F570A2D4}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"> <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property> <DTS:Property DTS:Name="ExecutionAddress"></DTS:Property> <DTS:Property DTS:Name="TaskContact">Execute SQL Task; Microsoft Corporation; Microsoft SQL Server 2008; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">-1</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Property DTS:Name="ObjectName">Clean Tables</DTS:Property> <DTS:Property DTS:Name="DTSID">{EFABE3CF-E624-4872-B68A-D06653D04688}</DTS:Property> <DTS:Property DTS:Name="Description">Execute SQL Task</DTS:Property> <DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><SQLTask:SqlTaskData SQLTask:Connection="{95A6D951-DFB7-4C24-BDE6-D034212C1792}" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="truncate table security.[User] truncate table security.[UserGroup]" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"/></DTS:ObjectData></DTS:Executable> <DTS:Executable DTS:ExecutableType="SSIS.Pipeline.2"> <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property> <DTS:Property DTS:Name="ExecutionAddress"></DTS:Property> <DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">-1</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Property DTS:Name="ObjectName">DF Groups</DTS:Property> <DTS:Property DTS:Name="DTSID">{80F3696A-2226-4C37-9F7E-1AE92D2329A3}</DTS:Property> <DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property> <DTS:Property DTS:Name="CreationName">SSIS.Pipeline.2</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="10" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true"> <components> <component id="1" name="OLESRC AD" componentClassID="{BCEFE59B-6819-47F7-A125-63753B33ABB7}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7"> <properties> <property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">SELECT name as GroupName FROM OPENQUERY( ADSI, 'SELECT name, ou,dc FROM ''LDAP://DC=mydomain,DC=com'' WHERE objectClass = ''Group'' and name = ''RPT_*''')</property> <property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property> <property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">2</property> <property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties> <connections> <connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <outputs> <output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="107" name="GroupName" description="" lineageId="107" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="106" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="106" name="GroupName" description="" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" mappedColumnId="0"/></externalMetadataColumns></output> <output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="108" name="GroupName" description="" lineageId="108" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> <component id="238" name="Lookup" componentClassID="{27648839-180F-45E6-838D-AFF53DF682D2}" description="Looks up values in a reference dataset by using exact matching." localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="5" pipelineVersion="0" contactInfo="Lookup;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;5"> <properties> <property id="241" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="Specifies the SQL statement that generates the lookup table." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">select * from [security].[Group]</property> <property id="242" name="SqlCommandParam" dataType="System.String" state="default" isArray="false" description="Specifies a SQL statement that uses parameters to generate the lookup table." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">select * from (select * from [security].[Group]) [refTable] where [refTable].[ADName] = ?</property> <property id="243" name="ConnectionType" dataType="System.Int32" state="default" isArray="false" description="Specifies the type of connection used to access the reference dataset." typeConverter="LookupConnectionType" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="245" name="CacheType" dataType="System.Int32" state="default" isArray="false" description="Specifies the cache type of the lookup table." typeConverter="CacheType" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="246" name="NoMatchBehavior" dataType="System.Int32" state="default" isArray="false" description="Specifies how the Lookup transformation handles rows without matching entries in the reference data set." typeConverter="LookupNoMatchBehavior" UITypeEditor="" containsID="false" expressionType="None">1</property> <property id="247" name="NoMatchCachePercentage" dataType="System.Int32" state="default" isArray="false" description="Specifies the percentage of the cache that is allocated for rows with no matching entries in the reference dataset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="248" name="MaxMemoryUsage" dataType="System.Int32" state="default" isArray="false" description="Maximum Memory Usage for Reference Cache on a 32 bit platform." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">25</property> <property id="249" name="MaxMemoryUsage64" dataType="System.Int64" state="default" isArray="false" description="Maximum Memory Usage for Reference Cache on a 64 bit platform." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">25</property> <property id="250" name="ReferenceMetadataXml" dataType="System.String" state="default" isArray="false" description="Indicates whether to reference metadata in an XML format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"><referenceMetadata><referenceColumns><referenceColumn name="GroupID" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="ADName" dataType="DT_WSTR" length="100" precision="0" scale="0" codePage="0"/><referenceColumn name="RSName" dataType="DT_WSTR" length="100" precision="0" scale="0" codePage="0"/><referenceColumn name="Type" dataType="DT_STR" length="50" precision="0" scale="0" codePage="1252"/></referenceColumns></referenceMetadata></property> <property id="251" name="ParameterMap" dataType="System.String" state="default" isArray="false" description="Specifies the list of lineage identifiers that map to the parameters that the SQL statement in the SQLCommand property uses. Entries in the list are separated by semicolons." typeConverter="" UITypeEditor="" containsID="true" expressionType="None">#107;</property> <property id="252" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property></properties> <connections> <connection id="244" name="OleDbConnection" description="Connection manager used to access lookup data." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <inputs> <input id="239" name="Lookup Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><inputColumns> <inputColumn id="258" name="" description="" lineageId="107" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"><properties> <property id="259" name="JoinToReferenceColumn" dataType="System.String" state="default" isArray="false" description="Specifies the column in the reference table that a column joins." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">ADName</property> <property id="260" name="CopyFromReferenceColumn" dataType="System.Null" state="default" isArray="false" description="Specifies the column in the reference table from which a column is copied." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"/></properties></inputColumn> </inputColumns><externalMetadataColumns isUsed="False"/></input> </inputs> <outputs> <output id="240" name="Lookup Match Output" description="" exclusionGroup="1" synchronousInputId="239" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="Lookup" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="261" name="GroupID" description="" lineageId="261" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Copy Column" errorRowDisposition="NotUsed" truncationRowDisposition="FailComponent" externalMetadataColumnId="0" mappedColumnId="0"><properties> <property id="262" name="CopyFromReferenceColumn" dataType="System.String" state="default" isArray="false" description="Specifies the column in the reference table from which a column is copied." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">GroupID</property></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="False"/></output> <output id="253" name="Lookup No Match Output" description="The Lookup output that handles rows with no matching entries in the reference dataset. Use this output when the NoMatchBehavior property is set to "Send rows with no matching entries to the no match output."" exclusionGroup="1" synchronousInputId="239" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output> <output id="254" name="Lookup Error Output" description="" exclusionGroup="1" synchronousInputId="239" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="255" name="ErrorCode" description="" lineageId="255" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="256" name="ErrorColumn" description="" lineageId="256" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> <component id="263" name="OLE DB Destination" componentClassID="{5A0B62E8-D91D-49F5-94A5-7BE58DE508F0}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4"> <properties> <property id="264" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="265" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[security].[Group]</property> <property id="266" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="267" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property> <property id="268" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property> <property id="269" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="270" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</property> <property id="272" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="273" name="FastLoadKeepNulls" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="274" name="FastLoadOptions" dataType="System.String" state="default" isArray="false" description="Specifies options to be used with fast load. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">TABLOCK,CHECK_CONSTRAINTS</property> <property id="275" name="FastLoadMaxInsertCommitSize" dataType="System.Int32" state="default" isArray="false" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">2147483647</property></properties> <connections> <connection id="271" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <inputs> <input id="276" name="OLE DB Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns> <inputColumn id="285" name="" description="" lineageId="107" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="282" mappedColumnId="0"/> </inputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="281" name="GroupID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="282" name="ADName" description="" precision="0" scale="0" length="100" dataType="wstr" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="283" name="RSName" description="" precision="0" scale="0" length="100" dataType="wstr" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="284" name="Type" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252" mappedColumnId="0"/></externalMetadataColumns></input> </inputs> <outputs> <output id="277" name="OLE DB Destination Error Output" description="" exclusionGroup="1" synchronousInputId="276" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="278" name="ErrorCode" description="" lineageId="278" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="279" name="ErrorColumn" description="" lineageId="279" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> </components> <paths> <path id="257" name="OLE DB Source Output" description="" startId="11" endId="239"/> <path id="280" name="Lookup No Match Output" description="" startId="253" endId="276"/> </paths></pipeline></DTS:ObjectData></DTS:Executable> <DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"> <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property> <DTS:Property DTS:Name="ExecutionAddress"></DTS:Property> <DTS:Property DTS:Name="TaskContact">Execute SQL Task; Microsoft Corporation; Microsoft SQL Server 2008; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">-1</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Property DTS:Name="ObjectName">Execute SQL Task</DTS:Property> <DTS:Property DTS:Name="DTSID">{21A91470-27A0-4394-98FF-481266D7996A}</DTS:Property> <DTS:Property DTS:Name="Description">Execute SQL Task</DTS:Property> <DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><SQLTask:SqlTaskData SQLTask:Connection="{95A6D951-DFB7-4C24-BDE6-D034212C1792}" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="SELECT [GroupID] ,[ADName] FROM [EDW].[security].[Group]" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_Rowset" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"><SQLTask:ResultBinding SQLTask:ResultName="0" SQLTask:DtsVariableName="User::oGroups"/></SQLTask:SqlTaskData></DTS:ObjectData></DTS:Executable> <DTS:Executable DTS:ExecutableType="STOCK:FOREACHLOOP"> <DTS:ForEachEnumerator> <DTS:Property DTS:Name="ObjectName">{A00F4DD5-E238-4DA7-A984-DC6ED6E6A47C}</DTS:Property> <DTS:Property DTS:Name="DTSID">{A00F4DD5-E238-4DA7-A984-DC6ED6E6A47C}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Runtime.Enumerators.ADO.ForEachADOEnumerator, Microsoft.SqlServer.ForEachADOEnumerator, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:ObjectData><FEEADO EnumType="EnumerateRowsInFirstTable" VarName="User::oGroups"/></DTS:ObjectData></DTS:ForEachEnumerator> <DTS:Property DTS:Name="MaxConcurrent">1</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">-1</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:Variable> <DTS:Property DTS:Name="Expression">"SELECT distinct convert(Varchar(500),SAMAccountName) As UserName , Mail FROM OPENQUERY( ADSI, '<LDAP://DC=mydomain,DC=com>;(&(objectClass=user)(memberof:1.2.840.113556.1.4.1941:=cn=" + @[User::sGroupName] + ",cn=users,dc=mydomain,dc=com));SAMAccountName,Mail; subtree')" </DTS:Property> <DTS:Property DTS:Name="EvaluateAsExpression">-1</DTS:Property> <DTS:Property DTS:Name="Namespace">User</DTS:Property> <DTS:Property DTS:Name="ReadOnly">0</DTS:Property> <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property> <DTS:Property DTS:Name="IncludeInDebugDump">2345</DTS:Property><DTS:VariableValue DTS:DataType="8">SELECT distinct convert(Varchar(500),SAMAccountName) As UserName , Mail FROM OPENQUERY( ADSI, '<LDAP://DC=mydomain,DC=com>;(&(objectClass=user)(memberof:1.2.840.113556.1.4.1941:=cn=RPT_TestRight1,cn=users,dc=mydomain,dc=com));SAMAccountName,Mail; subtree')</DTS:VariableValue> <DTS:Property DTS:Name="ObjectName">sSQLSTMT</DTS:Property> <DTS:Property DTS:Name="DTSID">{D6E916D7-D63C-4C39-BF35-63AFDCF3FD10}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Executable DTS:ExecutableType="SSIS.Pipeline.2"> <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property> <DTS:Property DTS:Name="ExecutionAddress"></DTS:Property> <DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">-1</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Property DTS:Name="ObjectName">DF Users</DTS:Property> <DTS:Property DTS:Name="DTSID">{19B75434-7CAF-4AA0-857C-20395F186D8A}</DTS:Property> <DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property> <DTS:Property DTS:Name="CreationName">SSIS.Pipeline.2</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="10" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true"> <components> <component id="1" name="SRC ADUserData" componentClassID="{BCEFE59B-6819-47F7-A125-63753B33ABB7}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7"> <properties> <property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property> <property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">User::sSQLSTMT</property> <property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property> <property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</property> <property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties> <connections> <connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <outputs> <output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="75" name="UserName" description="" lineageId="75" precision="0" scale="0" length="500" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="74" mappedColumnId="0"/> <outputColumn id="78" name="Mail" description="" lineageId="78" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="77" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="74" name="UserName" description="" precision="0" scale="0" length="500" dataType="str" codePage="1252" mappedColumnId="0"/> <externalMetadataColumn id="77" name="Mail" description="" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" mappedColumnId="0"/></externalMetadataColumns></output> <output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="76" name="UserName" description="" lineageId="76" precision="0" scale="0" length="500" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="79" name="Mail" description="" lineageId="79" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> <component id="147" name="OLE DB Destination" componentClassID="{5A0B62E8-D91D-49F5-94A5-7BE58DE508F0}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4"> <properties> <property id="148" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="149" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[security].[User]</property> <property id="150" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="151" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property> <property id="152" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property> <property id="153" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="154" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</property> <property id="156" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="157" name="FastLoadKeepNulls" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</property> <property id="158" name="FastLoadOptions" dataType="System.String" state="default" isArray="false" description="Specifies options to be used with fast load. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">TABLOCK,CHECK_CONSTRAINTS</property> <property id="159" name="FastLoadMaxInsertCommitSize" dataType="System.Int32" state="default" isArray="false" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">2147483647</property></properties> <connections> <connection id="155" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <inputs> <input id="160" name="OLE DB Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns> <inputColumn id="168" name="" description="" lineageId="75" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="166" mappedColumnId="0"/> <inputColumn id="169" name="" description="" lineageId="78" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="167" mappedColumnId="0"/> </inputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="165" name="UserID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="166" name="UserName" description="" precision="0" scale="0" length="100" dataType="str" codePage="1252" mappedColumnId="0"/> <externalMetadataColumn id="167" name="EmailAddress" description="" precision="0" scale="0" length="100" dataType="wstr" codePage="0" mappedColumnId="0"/></externalMetadataColumns></input> </inputs> <outputs> <output id="161" name="OLE DB Destination Error Output" description="" exclusionGroup="1" synchronousInputId="160" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="162" name="ErrorCode" description="" lineageId="162" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="163" name="ErrorColumn" description="" lineageId="163" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> </components> <paths> <path id="170" name="OLE DB Source Output" description="" startId="11" endId="160"/> </paths></pipeline></DTS:ObjectData></DTS:Executable> <DTS:Executable DTS:ExecutableType="SSIS.Pipeline.2"> <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property> <DTS:Property DTS:Name="ExecutionAddress"></DTS:Property> <DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> <DTS:Property DTS:Name="ForceExecValue">0</DTS:Property> <DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property> <DTS:Property DTS:Name="Disabled">0</DTS:Property> <DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property> <DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property> <DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property> <DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property> <DTS:Property DTS:Name="LocaleID">-1</DTS:Property> <DTS:Property DTS:Name="TransactionOption">1</DTS:Property> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:LoggingOptions> <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> <DTS:Property DTS:Name="FilterKind">1</DTS:Property> <DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions> <DTS:Property DTS:Name="ObjectName">DF UserGroup</DTS:Property> <DTS:Property DTS:Name="DTSID">{80f123f5-3426-45ca-92e9-063774653c05}</DTS:Property> <DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property> <DTS:Property DTS:Name="CreationName">SSIS.Pipeline.2</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="10" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true"> <components> <component id="1" name="SRC ADUSERGroup" componentClassID="{BCEFE59B-6819-47F7-A125-63753B33ABB7}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7"> <properties> <property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property> <property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">User::sSQLSTMT</property> <property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property> <property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</property> <property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties> <connections> <connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <outputs> <output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="75" name="UserName" description="" lineageId="75" precision="0" scale="0" length="500" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="74" mappedColumnId="0"/> <outputColumn id="78" name="Mail" description="" lineageId="78" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="77" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="74" name="UserName" description="" precision="0" scale="0" length="500" dataType="str" codePage="1252" mappedColumnId="0"/> <externalMetadataColumn id="77" name="Mail" description="" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" mappedColumnId="0"/></externalMetadataColumns></output> <output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="76" name="UserName" description="" lineageId="76" precision="0" scale="0" length="500" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="79" name="Mail" description="" lineageId="79" precision="0" scale="0" length="4000" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> <component id="27" name="Various Derived Columns" componentClassID="{2932025B-AB99-40F6-B5B8-783A73F80E24}" description="Updates column values using expressions" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Derived Column;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"> <inputs> <input id="28" name="Derived Column Input" description="Input to the Derived Column Transformation" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input> </inputs> <outputs> <output id="29" name="Derived Column Output" description="Default Output of the Derived Column Transformation" exclusionGroup="1" synchronousInputId="28" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="39" name="GroupID" description="" lineageId="39" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Computation" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="0" mappedColumnId="0"><properties> <property id="40" name="Expression" dataType="System.String" state="default" isArray="false" description="Derived Column Expression" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">@[User::sGroupID]</property> <property id="41" name="FriendlyExpression" dataType="System.String" state="default" isArray="false" description="Derived Column Friendly Expression" typeConverter="" UITypeEditor="" containsID="true" expressionType="Notify">@[User::sGroupID]</property></properties></outputColumn> <outputColumn id="42" name="GroupName" description="" lineageId="42" precision="0" scale="0" length="100" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Computation" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="0" mappedColumnId="0"><properties> <property id="43" name="Expression" dataType="System.String" state="default" isArray="false" description="Derived Column Expression" typeConverter="" UITypeEditor="" containsID="true" expressionType="None">@[User::sGroupName]</property> <property id="44" name="FriendlyExpression" dataType="System.String" state="default" isArray="false" description="Derived Column Friendly Expression" typeConverter="" UITypeEditor="" containsID="true" expressionType="Notify">@[User::sGroupName]</property></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="False"/></output> <output id="30" name="Derived Column Error Output" description="Error Output of the Derived Column Transformation" exclusionGroup="1" synchronousInputId="28" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="31" name="ErrorCode" description="" lineageId="31" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="32" name="ErrorColumn" description="" lineageId="32" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> <component id="117" name="Lookup Users" componentClassID="{27648839-180F-45E6-838D-AFF53DF682D2}" description="Looks up values in a reference dataset by using exact matching." localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="5" pipelineVersion="0" contactInfo="Lookup;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;5"> <properties> <property id="120" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="Specifies the SQL statement that generates the lookup table." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">SELECT UserID, EmailAddress, UserName FROM security.[User]</property> <property id="121" name="SqlCommandParam" dataType="System.String" state="default" isArray="false" description="Specifies a SQL statement that uses parameters to generate the lookup table." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">select * from (SELECT UserID, EmailAddress, UserName FROM security.[User]) [refTable] where [refTable].[UserName] = ?</property> <property id="122" name="ConnectionType" dataType="System.Int32" state="default" isArray="false" description="Specifies the type of connection used to access the reference dataset." typeConverter="LookupConnectionType" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="124" name="CacheType" dataType="System.Int32" state="default" isArray="false" description="Specifies the cache type of the lookup table." typeConverter="CacheType" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="125" name="NoMatchBehavior" dataType="System.Int32" state="default" isArray="false" description="Specifies how the Lookup transformation handles rows without matching entries in the reference data set." typeConverter="LookupNoMatchBehavior" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="126" name="NoMatchCachePercentage" dataType="System.Int32" state="default" isArray="false" description="Specifies the percentage of the cache that is allocated for rows with no matching entries in the reference dataset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="127" name="MaxMemoryUsage" dataType="System.Int32" state="default" isArray="false" description="Maximum Memory Usage for Reference Cache on a 32 bit platform." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">25</property> <property id="128" name="MaxMemoryUsage64" dataType="System.Int64" state="default" isArray="false" description="Maximum Memory Usage for Reference Cache on a 64 bit platform." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">25</property> <property id="129" name="ReferenceMetadataXml" dataType="System.String" state="default" isArray="false" description="Indicates whether to reference metadata in an XML format." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"><referenceMetadata><referenceColumns><referenceColumn name="UserID" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="EmailAddress" dataType="DT_WSTR" length="100" precision="0" scale="0" codePage="0"/><referenceColumn name="UserName" dataType="DT_STR" length="100" precision="0" scale="0" codePage="1252"/></referenceColumns></referenceMetadata></property> <property id="130" name="ParameterMap" dataType="System.String" state="default" isArray="false" description="Specifies the list of lineage identifiers that map to the parameters that the SQL statement in the SQLCommand property uses. Entries in the list are separated by semicolons." typeConverter="" UITypeEditor="" containsID="true" expressionType="None">#75;</property> <property id="131" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property></properties> <connections> <connection id="123" name="OleDbConnection" description="Connection manager used to access lookup data." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <inputs> <input id="118" name="Lookup Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><inputColumns> <inputColumn id="148" name="" description="" lineageId="75" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"><properties> <property id="149" name="JoinToReferenceColumn" dataType="System.String" state="default" isArray="false" description="Specifies the column in the reference table that a column joins." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">UserName</property> <property id="150" name="CopyFromReferenceColumn" dataType="System.Null" state="default" isArray="false" description="Specifies the column in the reference table from which a column is copied." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"/></properties></inputColumn> </inputColumns><externalMetadataColumns isUsed="False"/></input> </inputs> <outputs> <output id="119" name="Lookup Match Output" description="" exclusionGroup="1" synchronousInputId="118" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="Lookup" errorRowDisposition="IgnoreFailure" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="142" name="UserID" description="" lineageId="142" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Copy Column" errorRowDisposition="NotUsed" truncationRowDisposition="FailComponent" externalMetadataColumnId="0" mappedColumnId="0"><properties> <property id="143" name="CopyFromReferenceColumn" dataType="System.String" state="default" isArray="false" description="Specifies the column in the reference table from which a column is copied." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">UserID</property></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="False"/></output> <output id="132" name="Lookup No Match Output" description="The Lookup output that handles rows with no matching entries in the reference dataset. Use this output when the NoMatchBehavior property is set to "Send rows with no matching entries to the no match output."" exclusionGroup="1" synchronousInputId="118" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output> <output id="133" name="Lookup Error Output" description="" exclusionGroup="1" synchronousInputId="118" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="134" name="ErrorCode" description="" lineageId="134" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="135" name="ErrorColumn" description="" lineageId="135" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> <component id="172" name="OLE DB Destination" componentClassID="{5A0B62E8-D91D-49F5-94A5-7BE58DE508F0}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4"> <properties> <property id="173" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="174" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[security].[UserGroup]</property> <property id="175" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property> <property id="176" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property> <property id="177" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property> <property id="178" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="179" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</property> <property id="181" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="182" name="FastLoadKeepNulls" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property> <property id="183" name="FastLoadOptions" dataType="System.String" state="default" isArray="false" description="Specifies options to be used with fast load. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">TABLOCK,CHECK_CONSTRAINTS</property> <property id="184" name="FastLoadMaxInsertCommitSize" dataType="System.Int32" state="default" isArray="false" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">2147483647</property></properties> <connections> <connection id="180" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{95A6D951-DFB7-4C24-BDE6-D034212C1792}"/></connections> <inputs> <input id="185" name="OLE DB Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns> <inputColumn id="192" name="" description="" lineageId="39" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="190" mappedColumnId="0"/> <inputColumn id="193" name="" description="" lineageId="142" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="191" mappedColumnId="0"/> </inputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="190" name="GroupID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="191" name="UserID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0" mappedColumnId="0"/></externalMetadataColumns></input> </inputs> <outputs> <output id="186" name="OLE DB Destination Error Output" description="" exclusionGroup="1" synchronousInputId="185" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns> <outputColumn id="187" name="ErrorCode" description="" lineageId="187" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/> <outputColumn id="188" name="ErrorColumn" description="" lineageId="188" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0" mappedColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output> </outputs> </component> </components> <paths> <path id="33" name="OLE DB Source Output" description="" startId="11" endId="28"/> <path id="136" name="Derived Column Output" description="" startId="29" endId="118"/> <path id="189" name="Lookup Match Output" description="" startId="119" endId="185"/> </paths></pipeline></DTS:ObjectData></DTS:Executable> <DTS:PrecedenceConstraint> <DTS:Property DTS:Name="Value">0</DTS:Property> <DTS:Property DTS:Name="EvalOp">2</DTS:Property> <DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property> <DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{19B75434-7CAF-4AA0-857C-20395F186D8A}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{80f123f5-3426-45ca-92e9-063774653c05}" DTS:IsFrom="0"/> <DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property> <DTS:Property DTS:Name="DTSID">{A53C8E88-A206-48BC-9977-36F4C2C12BA4}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint> <DTS:Property DTS:Name="ObjectName">Loop through groups to get users</DTS:Property> <DTS:Property DTS:Name="DTSID">{4D272F70-E6A8-4A5E-A050-1DE2C651AEE2}</DTS:Property> <DTS:Property DTS:Name="Description">Foreach Loop Container</DTS:Property> <DTS:Property DTS:Name="CreationName">STOCK:FOREACHLOOP</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property> <DTS:ForEachVariableMapping> <DTS:Property DTS:Name="VariableName">User::sGroupID</DTS:Property> <DTS:Property DTS:Name="ValueIndex" DTS:DataType="3">0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{65D240AF-2F3F-43D2-8D45-2813D3A90428}</DTS:Property> <DTS:Property DTS:Name="DTSID">{65D240AF-2F3F-43D2-8D45-2813D3A90428}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:ForEachVariableMapping> <DTS:ForEachVariableMapping> <DTS:Property DTS:Name="VariableName">User::sGroupName</DTS:Property> <DTS:Property DTS:Name="ValueIndex" DTS:DataType="3">1</DTS:Property> <DTS:Property DTS:Name="ObjectName">{5EEC158B-5322-4EF5-AC1A-DC63EFEFAC5F}</DTS:Property> <DTS:Property DTS:Name="DTSID">{5EEC158B-5322-4EF5-AC1A-DC63EFEFAC5F}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:ForEachVariableMapping></DTS:Executable> <DTS:PrecedenceConstraint> <DTS:Property DTS:Name="Value">0</DTS:Property> <DTS:Property DTS:Name="EvalOp">2</DTS:Property> <DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property> <DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{EFABE3CF-E624-4872-B68A-D06653D04688}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}" DTS:IsFrom="0"/> <DTS:Property DTS:Name="ObjectName">Constraint 4</DTS:Property> <DTS:Property DTS:Name="DTSID">{E99C2A6A-0405-461B-BC93-4E8C90DB15C5}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint> <DTS:PrecedenceConstraint> <DTS:Property DTS:Name="Value">0</DTS:Property> <DTS:Property DTS:Name="EvalOp">2</DTS:Property> <DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property> <DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{21A91470-27A0-4394-98FF-481266D7996A}" DTS:IsFrom="0"/> <DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property> <DTS:Property DTS:Name="DTSID">{FDD97DC5-9AED-48C2-BEED-86B689E65852}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint> <DTS:PrecedenceConstraint> <DTS:Property DTS:Name="Value">0</DTS:Property> <DTS:Property DTS:Name="EvalOp">2</DTS:Property> <DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property> <DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{21A91470-27A0-4394-98FF-481266D7996A}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{4D272F70-E6A8-4A5E-A050-1DE2C651AEE2}" DTS:IsFrom="0"/> <DTS:Property DTS:Name="ObjectName">Constraint 1</DTS:Property> <DTS:Property DTS:Name="DTSID">{32E5923B-E03F-4F94-8ED8-0101A323B5F6}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint> <DTS:Property DTS:Name="ObjectName">GetADUsers</DTS:Property> <DTS:Property DTS:Name="DTSID">{BE99CE8B-C398-4615-BDFB-EF495F170891}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName">SSIS.Package.2</DTS:Property> <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingLeft>1000</dwd:BoundingLeft><dwd:BoundingTop>1000</dwd:BoundingTop><dwd:Layout><dds> <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="10" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="3412" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="24130" y="7197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="1" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"> <font> <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /> </font> <mouseicon> <ddsxmlobjectstreamwrapper binary="6c74000000000000" /> </mouseicon> </diagram> <layoutmanager> <ddsxmlobj /> </layoutmanager> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="OLE DB Source" left="2223" top="3016" logicalid="4" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{19B75434-7CAF-4AA0-857C-20395F186D8A}/components/1" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Truncation may occur due to inserting data from data flow column "UserName" with a length of 500 to ..." left="2519" top="8022" logicalid="5" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{19B75434-7CAF-4AA0-857C-20395F186D8A}/components/147" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="3623" top="3781" logicalid="6" controlid="3" masterid="0" hint1="0" hint2="0" width="1095" height="4741" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{19B75434-7CAF-4AA0-857C-20395F186D8A}/paths/170" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="1" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="4022" y="4180" /> <point x="4022" y="6101" /> <point x="4318" y="6101" /> <point x="4318" y="8022" /> </connector> </ddscontrol> </dds></dwd:Layout><dwd:PersistedViewPortTop>3412</dwd:PersistedViewPortTop></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">45 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:int">0</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">45 UsedTableName</dwd:key><dwd:value xsi:type="xsd:string">[Reporting].[Users]</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">45 OverwriteParamsSQLProp</dwd:key><dwd:value xsi:type="xsd:boolean">false</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">45 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">147 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">147 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{19B75434-7CAF-4AA0-857C-20395F186D8A}</DTS:Property> <DTS:Property DTS:Name="DTSID">{DF689A41-060E-4861-80D2-E27988134BBB}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:SourceName>OLE DB Source Output</dwd:SourceName><dwd:DestinationName>OLE DB Destination Input</dwd:DestinationName></PipelinePath></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{19B75434-7CAF-4AA0-857C-20395F186D8A}-170</DTS:Property> <DTS:Property DTS:Name="DTSID">{C951439B-10FA-4AF9-93A8-95C6FE5D8DA0}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PrecedenceConstraint xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:EvalOp>Constraint</dwd:EvalOp></PrecedenceConstraint></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{32E5923B-E03F-4F94-8ED8-0101A323B5F6}</DTS:Property> <DTS:Property DTS:Name="DTSID">{8F1CA229-6F97-436C-8175-3A7232F0D928}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingLeft>1423</dwd:BoundingLeft><dwd:BoundingTop>3016</dwd:BoundingTop><dwd:Layout><dds> <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="21" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="423" scrolltop="3541" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="24130" y="7197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="1" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"> <font> <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /> </font> <mouseicon> <ddsxmlobjectstreamwrapper binary="6c74000000000000" /> </mouseicon> </diagram> <layoutmanager> <ddsxmlobj /> </layoutmanager> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="OLE DB Source" left="2223" top="3016" logicalid="9" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/components/1" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Updates column values using expressions" left="2169" top="5423" logicalid="10" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/components/27" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="3569" top="3781" logicalid="11" controlid="3" masterid="0" hint1="0" hint2="0" width="853" height="2142" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/paths/33" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector sourceid="1" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="4022" y="4180" /> <point x="4022" y="4801" /> <point x="3968" y="4801" /> <point x="3968" y="5423" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Looks up values in a reference dataset by using exact matching." left="2089" top="7403" logicalid="12" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/components/117" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="3489" top="6188" logicalid="13" controlid="5" masterid="0" hint1="0" hint2="0" width="879" height="1715" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/paths/136" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="2" destid="4" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="3968" y="6587" /> <point x="3968" y="6995" /> <point x="3888" y="6995" /> <point x="3888" y="7403" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="OLE DB Destination" left="3836" top="10324" logicalid="14" controlid="6" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/components/172" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="3489" top="8168" logicalid="15" controlid="7" masterid="0" hint1="0" hint2="0" width="2546" height="2656" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1"> <adornment percentpos="74.9143835616438" controlid="9" width="2970" height="344" side="0" behavior="2" himetric="2625" distfromline="175" startobj="0" x="2490" y="9620" visible="1" allowoverlap="1" usepercent="1" /> </polyline> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}/paths/189" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="4" destid="6" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="3888" y="8567" /> <point x="3888" y="9445" /> <point x="5635" y="9445" /> <point x="5635" y="10324" /> </connector> </ddscontrol> <ddscontrol controlprogid="MSDDS.Text" left="2490" top="9620" logicalid="17" controlid="9" masterid="7" hint1="0" hint2="0" width="2970" height="344" noresize="1" nomove="0" nodefaultattachpoints="1" autodrag="1" usedefaultiddshape="1" selectable="0" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="0" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000200009a0b000058010000030000000000050000800800008001000000350001000000900144420100065461686f6d6113004c006f006f006b007500700020004d00610074006300680020004f007500740070007500740000000000" /> </control> <layoutobject> <ddsxmlobj> <property name="AnnotationAdornment" value="-1" vartype="11" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> </dds></dwd:Layout><dwd:PersistedViewPortLeft>423</dwd:PersistedViewPortLeft><dwd:PersistedViewPortTop>3541</dwd:PersistedViewPortTop></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">117 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">117 UsedTableName</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">172 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">117 OverwriteParamsSQLProp</dwd:key><dwd:value xsi:type="xsd:boolean">false</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">117 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:int">0</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">172 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80f123f5-3426-45ca-92e9-063774653c05}</DTS:Property> <DTS:Property DTS:Name="DTSID">{55A69C6B-7DEE-4DE3-BEBD-934205AB7726}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:SourceName>Derived Column Output</dwd:SourceName><dwd:DestinationName>Lookup Input</dwd:DestinationName></PipelinePath></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80f123f5-3426-45ca-92e9-063774653c05}-136</DTS:Property> <DTS:Property DTS:Name="DTSID">{6144B820-18E1-4896-B3AD-67A18DFD430A}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:SourceName>Lookup Match Output</dwd:SourceName><dwd:DestinationName>OLE DB Destination Input</dwd:DestinationName></PipelinePath></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80f123f5-3426-45ca-92e9-063774653c05}-189</DTS:Property> <DTS:Property DTS:Name="DTSID">{F034D04E-9BE2-495D-A322-04848B5A4888}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:SourceName>OLE DB Source Output</dwd:SourceName><dwd:DestinationName>Derived Column Input</dwd:DestinationName></PipelinePath></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80f123f5-3426-45ca-92e9-063774653c05}-33</DTS:Property> <DTS:Property DTS:Name="DTSID">{F525CD4D-5627-4A35-B3AF-648F98CBD68E}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingLeft>1000</dwd:BoundingLeft><dwd:BoundingTop>2064</dwd:BoundingTop><dwd:Layout><dds> <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="17" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="4309" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="24130" y="7197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="1" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"> <font> <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /> </font> <mouseicon> <ddsxmlobjectstreamwrapper binary="6c74000000000000" /> </mouseicon> </diagram> <layoutmanager> <ddsxmlobj /> </layoutmanager> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="OLE DB Source" left="1985" top="3016" logicalid="7" controlid="1" masterid="0" hint1="0" hint2="0" width="4074" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="00080000ea0f00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}/components/1" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Looks up values in a reference dataset by using exact matching." left="5794" top="6012" logicalid="8" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}/components/238" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="3623" top="3781" logicalid="9" controlid="3" masterid="0" hint1="0" hint2="0" width="4370" height="2731" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}/paths/257" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="1" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="4022" y="4180" /> <point x="4022" y="5096" /> <point x="7593" y="5096" /> <point x="7593" y="6012" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Truncation may occur due to inserting data from data flow column "GroupName" with a length of 4000 t..." left="5741" top="9240" logicalid="10" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}/components/263" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="7141" top="6777" logicalid="11" controlid="5" masterid="0" hint1="0" hint2="0" width="852" height="2963" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1"> <adornment percentpos="48.7482286254133" controlid="7" width="3432" height="344" side="0" behavior="2" himetric="1032" distfromline="175" startobj="0" x="3986" y="7689" visible="1" allowoverlap="1" usepercent="1" /> </polyline> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}/paths/280" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="2" destid="4" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="7593" y="7176" /> <point x="7593" y="8208" /> <point x="7540" y="8208" /> <point x="7540" y="9240" /> </connector> </ddscontrol> <ddscontrol controlprogid="MSDDS.Text" left="3986" top="7689" logicalid="13" controlid="7" masterid="5" hint1="0" hint2="0" width="3432" height="344" noresize="1" nomove="0" nodefaultattachpoints="1" autodrag="1" usedefaultiddshape="1" selectable="0" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="0" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="00020000680d000058010000030000000000050000800800008001000000350001000000900144420100065461686f6d6116004c006f006f006b007500700020004e006f0020004d00610074006300680020004f007500740070007500740000000000" /> </control> <layoutobject> <ddsxmlobj> <property name="AnnotationAdornment" value="-1" vartype="11" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> </dds></dwd:Layout><dwd:PersistedViewPortTop>4309</dwd:PersistedViewPortTop></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">19 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">19 UsedTableName</dwd:key><dwd:value xsi:type="xsd:string">[dbo].[DimDate]</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">238 UsedTableName</dwd:key><dwd:value xsi:type="xsd:string">[security].[Group]</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">19 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:int">0</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">238 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">238 OverwriteParamsSQLProp</dwd:key><dwd:value xsi:type="xsd:boolean">false</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">182 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">182 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">238 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:int">0</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">263 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">263 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">19 OverwriteParamsSQLProp</dwd:key><dwd:value xsi:type="xsd:boolean">false</dwd:value></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80F3696A-2226-4C37-9F7E-1AE92D2329A3}</DTS:Property> <DTS:Property DTS:Name="DTSID">{23C16BB8-54DC-46B7-908D-ED8283A8C57C}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:SourceName>OLE DB Source Output</dwd:SourceName><dwd:DestinationName>Lookup Input</dwd:DestinationName></PipelinePath></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80F3696A-2226-4C37-9F7E-1AE92D2329A3}-257</DTS:Property> <DTS:Property DTS:Name="DTSID">{2048769B-128C-4643-93E1-BF13ACFE7FA6}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:SourceName>Lookup No Match Output</dwd:SourceName><dwd:DestinationName>OLE DB Destination Input</dwd:DestinationName></PipelinePath></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{80F3696A-2226-4C37-9F7E-1AE92D2329A3}-280</DTS:Property> <DTS:Property DTS:Name="DTSID">{644AB200-8B49-428F-BE19-034C2673A511}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DataSourceID>EDW</dwd:DataSourceID></ConnectionManager></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{95A6D951-DFB7-4C24-BDE6-D034212C1792}</DTS:Property> <DTS:Property DTS:Name="DTSID">{1D6CCE56-1F92-4B19-BEEE-EA93F2FF9CE7}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PrecedenceConstraint xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:EvalOp>Constraint</dwd:EvalOp></PrecedenceConstraint></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{A53C8E88-A206-48BC-9977-36F4C2C12BA4}</DTS:Property> <DTS:Property DTS:Name="DTSID">{9D75D94C-EFAB-4A09-8003-1E997C0E366F}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingLeft>-44216</dwd:BoundingLeft><dwd:BoundingTop>-1665</dwd:BoundingTop><dwd:Layout><dds> <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="23" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="-45216" scrolltop="-897" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="24130" y="9578" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="1" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"> <font> <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /> </font> <mouseicon> <ddsxmlobjectstreamwrapper binary="6c74000000000000" /> </mouseicon> </diagram> <layoutmanager> <ddsxmlobj /> </layoutmanager> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Truncation may occur due to inserting data from data flow column "GroupName" with a length of 4000 t..." left="-36861" top="-1485" logicalid="11" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsContainerObjectManagedBridge.2" tooltip="Foreach Loop Container" left="-39099" top="2499" logicalid="12" controlid="2" masterid="0" hint1="0" hint2="0" width="7492" height="4759" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="00080000441d000097120000" /> </control> <layoutobject> <ddsxmlobj> <property name="Caption" value="Loop through groups to get users" vartype="8" /> <property name="CaptionHeight" value="1164" vartype="3" /> <property name="Collapsed" value="0" vartype="11" /> <property name="CollapsedWidth" value="7492" vartype="3" /> <property name="LogicalObject" value="{4D272F70-E6A8-4A5E-A050-1DE2C651AEE2}" vartype="8" /> <property name="RealHeight" value="3595" vartype="3" /> <property name="RealWidth" value="7492" vartype="3" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Truncation may occur due to inserting data from data flow column "Mail" with a length of 4000 to dat..." left="-38256" top="3863" logicalid="13" controlid="3" masterid="2" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{19B75434-7CAF-4AA0-857C-20395F186D8A}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Data Flow Task" left="-38253" top="5573" logicalid="14" controlid="4" masterid="2" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-36856" top="4628" logicalid="15" controlid="5" masterid="0" hint1="0" hint2="0" width="802" height="1445" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{A53C8E88-A206-48BC-9977-36F4C2C12BA4}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="3" destid="4" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-36457" y="5027" /> <point x="-36457" y="5300" /> <point x="-36454" y="5300" /> <point x="-36454" y="5573" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Execute SQL Task" left="-44216" top="-1665" logicalid="16" controlid="6" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{EFABE3CF-E624-4872-B68A-D06653D04688}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-41017" top="-1482" logicalid="17" controlid="7" masterid="0" hint1="0" hint2="0" width="4456" height="979" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{E99C2A6A-0405-461B-BC93-4E8C90DB15C5}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="6" destid="1" sourceattachpoint="15" destattachpoint="14" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-40618" y="-1083" /> <point x="-38739" y="-1083" /> <point x="-38739" y="-903" /> <point x="-36861" y="-903" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Execute SQL Task" left="-35162" top="643" logicalid="18" controlid="8" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{21A91470-27A0-4394-98FF-481266D7996A}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-35461" top="-720" logicalid="19" controlid="9" masterid="0" hint1="0" hint2="0" width="2498" height="1863" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{FDD97DC5-9AED-48C2-BEED-86B689E65852}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="1" destid="8" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-35062" y="-321" /> <point x="-35062" y="161" /> <point x="-33363" y="161" /> <point x="-33363" y="643" /> </connector> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-35752" top="1408" logicalid="20" controlid="10" masterid="0" hint1="0" hint2="0" width="2239" height="1591" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{32E5923B-E03F-4F94-8ED8-0101A323B5F6}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="8" destid="2" sourceattachpoint="5" destattachpoint="12" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-33913" y="1807" /> <point x="-33913" y="2153" /> <point x="-35353" y="2153" /> <point x="-35353" y="2499" /> </connector> </ddscontrol> </dds></dwd:Layout><dwd:PersistedViewPortLeft>-45216</dwd:PersistedViewPortLeft><dwd:PersistedViewPortTop>-897</dwd:PersistedViewPortTop></dwd:DtsControlFlowDiagram></Package></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{BE99CE8B-C398-4615-BDFB-EF495F170891}</DTS:Property> <DTS:Property DTS:Name="DTSID">{3DFB862B-30FC-4BF3-BF2F-1D91875BC9AB}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PrecedenceConstraint xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:EvalOp>Constraint</dwd:EvalOp></PrecedenceConstraint></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{E99C2A6A-0405-461B-BC93-4E8C90DB15C5}</DTS:Property> <DTS:Property DTS:Name="DTSID">{504C65B4-24C8-45A7-AB89-E0120547B395}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PrecedenceConstraint xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:EvalOp>Constraint</dwd:EvalOp></PrecedenceConstraint></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">{FDD97DC5-9AED-48C2-BEED-86B689E65852}</DTS:Property> <DTS:Property DTS:Name="DTSID">{E07FFD26-2C75-439D-AEC9-4EB02BA5BD86}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable> <DTS:PackageVariable> <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingLeft>-44216</dwd:BoundingLeft><dwd:BoundingTop>-1665</dwd:BoundingTop><dwd:Layout><dds> <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="23" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="-45216" scrolltop="-897" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="24130" y="9578" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="1" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"> <font> <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /> </font> <mouseicon> <ddsxmlobjectstreamwrapper binary="6c74000000000000" /> </mouseicon> </diagram> <layoutmanager> <ddsxmlobj /> </layoutmanager> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Truncation may occur due to inserting data from data flow column "GroupName" with a length of 4000 t..." left="-36861" top="-1485" logicalid="11" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80F3696A-2226-4C37-9F7E-1AE92D2329A3}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsContainerObjectManagedBridge.2" tooltip="Foreach Loop Container" left="-39099" top="2499" logicalid="12" controlid="2" masterid="0" hint1="0" hint2="0" width="7492" height="4759" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="00080000441d000097120000" /> </control> <layoutobject> <ddsxmlobj> <property name="Caption" value="Loop through groups to get users" vartype="8" /> <property name="CaptionHeight" value="1164" vartype="3" /> <property name="Collapsed" value="0" vartype="11" /> <property name="CollapsedWidth" value="7492" vartype="3" /> <property name="LogicalObject" value="{4D272F70-E6A8-4A5E-A050-1DE2C651AEE2}" vartype="8" /> <property name="RealHeight" value="3595" vartype="3" /> <property name="RealWidth" value="7492" vartype="3" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Truncation may occur due to inserting data from data flow column "Mail" with a length of 4000 to dat..." left="-38256" top="3863" logicalid="13" controlid="3" masterid="2" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{19B75434-7CAF-4AA0-857C-20395F186D8A}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Data Flow Task" left="-38253" top="5573" logicalid="14" controlid="4" masterid="2" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{80f123f5-3426-45ca-92e9-063774653c05}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-36856" top="4628" logicalid="15" controlid="5" masterid="0" hint1="0" hint2="0" width="802" height="1445" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{A53C8E88-A206-48BC-9977-36F4C2C12BA4}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="3" destid="4" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-36457" y="5027" /> <point x="-36457" y="5300" /> <point x="-36454" y="5300" /> <point x="-36454" y="5573" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Execute SQL Task" left="-44216" top="-1665" logicalid="16" controlid="6" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{EFABE3CF-E624-4872-B68A-D06653D04688}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-41017" top="-1482" logicalid="17" controlid="7" masterid="0" hint1="0" hint2="0" width="4456" height="979" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{E99C2A6A-0405-461B-BC93-4E8C90DB15C5}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="6" destid="1" sourceattachpoint="15" destattachpoint="14" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-40618" y="-1083" /> <point x="-38739" y="-1083" /> <point x="-38739" y="-903" /> <point x="-36861" y="-903" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="Execute SQL Task" left="-35162" top="643" logicalid="18" controlid="8" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{21A91470-27A0-4394-98FF-481266D7996A}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-35461" top="-720" logicalid="19" controlid="9" masterid="0" hint1="0" hint2="0" width="2498" height="1863" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{FDD97DC5-9AED-48C2-BEED-86B689E65852}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="1" destid="8" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-35062" y="-321" /> <point x="-35062" y="161" /> <point x="-33363" y="161" /> <point x="-33363" y="643" /> </connector> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="-35752" top="1408" logicalid="20" controlid="10" masterid="0" hint1="0" hint2="0" width="2239" height="1591" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{32E5923B-E03F-4F94-8ED8-0101A323B5F6}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="8" destid="2" sourceattachpoint="5" destattachpoint="12" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="-33913" y="1807" /> <point x="-33913" y="2153" /> <point x="-35353" y="2153" /> <point x="-35353" y="2499" /> </connector> </ddscontrol> </dds></dwd:Layout><dwd:PersistedViewPortLeft>-45216</dwd:PersistedViewPortLeft><dwd:PersistedViewPortTop>-897</dwd:PersistedViewPortTop></dwd:DtsControlFlowDiagram></Package></DTS:Property> <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> <DTS:Property DTS:Name="ObjectName">package-diagram</DTS:Property> <DTS:Property DTS:Name="DTSID">{59C8A6E1-5FC8-4815-8B5E-F8FAABCF0D53}</DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable></DTS:Executable>
that is the longest comment I ever have seen :-)
I will test it immediatly. FYI, I tried today to do this on my own, and for five minutes I get it too...
So now, I'm very interested in your solution!
I get several errors when I try do save the code to a dtsx-file. Can you upload this file to the "community root" ?
I think this could be the best solution :-)
I agree... That xml is crazy long. :)
Sorry for the Delay finally got this uploaded please see this link http://www.dtsxchange.com/GetADUsers.zip
Okay, I understand your solution and I learned now, that I can create variables with expressions - great! My solution is similar to your ones, I think one of the biggest differences is, that I create the SQL-Statement directly with the derived columns to store this into a RecordSet. In the next step I take a For-Each-Loop to go through this recordset to get all the users and write them into a raw file. Then I take the raw file to write the data into my data warehouse...
Thanks a lot for the tip with the variable!