/EFRepository/DependencyAnalyzer2008/SsisEnumerator.cs

Large files files are truncated, but you can click here to view the full file

///

/// Microsoft SQL Server 2008 Business Intelligence Metadata Reporting Samples

/// Dependency Analyzer Sample

/// 

/// Copyright (c) Microsoft Corporation.  All rights reserved.

///

using System;

using System.Collections.Generic;

using System.Data.OleDb;

using System.Diagnostics;

using System.Text;

using System.Xml;





using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using DTR = Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Collections.Specialized;

using TSQLParser;

#if SQL2005

using IDTSComponentMetaData = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90;

using IDTSPipeline = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline90;

using IDTSInput = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInput90;

using IDTSInputColumn = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn90;

using IDTSOutput = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutput90;

using IDTSOutputColumn = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumn90;

using IDTSRuntimeConnection = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSRuntimeConnection90;

using IDTSPath = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPath90;

using IDTSCustomProperty = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSCustomProperty90;

#else

using IDTSComponentMetaData = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100;

using IDTSPipeline = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100;

using IDTSInput = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInput100;

using IDTSInputColumn = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100;

using IDTSOutput = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutput100;

using IDTSOutputColumn = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumn100;

using IDTSRuntimeConnection = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSRuntimeConnection100;

using IDTSPath = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPath100;

using IDTSCustomProperty = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSCustomProperty100;

#endif



namespace Microsoft.Samples.DependencyAnalyzer

{

    /// <summary>

    ///  Enumerates Integration Services objects and the relationships between them. The result of this enumeration is persisted

    ///  in a repository.

    /// </summary>

    class SSISEnumerator

    {

        // 5 access modes used by ole db adapters

        enum AccessMode : int

        {

            AM_OPENROWSET = 0,

            AM_OPENROWSET_VARIABLE = 1,

            AM_SQLCOMMAND = 2,

            AM_SQLCOMMAND_VARIABLE = 3,

            AM_OPENROWSET_FASTLOAD_VARIABLE = 4

        }



        enum SqlStatementSourceType : int

        {

            DirectInput = 1,

            FileConnection = 2,

            Variable = 3

        }



        private Application app;

        private Repository repository;



        private const string dtsxPattern = "*.dtsx";



        /// <summary>

        /// Different component Class IDs that we understand about

        /// </summary>

        private class ClassIDs

        {

#if SQL2005

            internal const string OleDbSource = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";

            internal const string ExcelSource = "{B551FCA8-23BD-4719-896F-D8F352A5283C}";

            internal const string FlatFileSource = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

            internal const string RawFileSource = "{E2568105-9550-4F71-A638-B7FE42E66930}";

            internal const string XmlSource = "Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter, Microsoft.SqlServer.XmlSrc, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";



            internal const string OleDbDestination = "{E2568105-9550-4F71-A638-B7FE42E66922}";

            internal const string SqlDestination = "{5244B484-7C76-4026-9A01-00928EA81550}";

            internal const string FlatFileDest = "{A1DF9F6D-8EE4-4EF0-BB2E-D526130D7A7B}";

            internal const string RawFileDest = "{E2568105-9550-4F71-A638-B7FE42E66933}";



            internal const string Lookup = "{0FB4AABB-C027-4440-809A-1198049BF117}";

            internal const string FuzzyLookup = "{9F4EB4D4-AD71-496D-B70B-31ECE1139884}";



            internal const string ManagedComponentWrapper = "{bf01d463-7089-41ee-8f05-0a6dc17ce633}";



            internal const string DerivedColumn = "{9CF90BF0-5BCC-4C63-B91D-1F322DC12C26}";

            internal const string MultipleHash = "Martin.SQLServer.Dts.MultipleHash, MultipleHash2005, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44";

            internal const string KimballSCD = "MouldingAndMillwork.SSIS.KimballMethodSCD, KimballMethodSCD90, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c";

            internal const string OLEDBCommand = "{C60ACAD1-9BE8-46B3-87DA-70E59EADEA46}";

#else

            internal const string OleDbSource = "{BCEFE59B-6819-47F7-A125-63753B33ABB7}";

            internal const string ExcelSource = "{A4B1E1C8-17F3-46C8-AAD0-34F0C6FE42DE}";

            internal const string FlatFileSource = "{5ACD952A-F16A-41D8-A681-713640837664}";

            internal const string RawFileSource = "{51DC0B24-7421-45C3-B4AB-9481A683D91D}";

            internal const string XmlSource = "Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter, Microsoft.SqlServer.XmlSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";



            internal const string OleDbDestination = "{5A0B62E8-D91D-49F5-94A5-7BE58DE508F0}";

            internal const string SqlDestination = "{E4B61516-847B-4BDF-9CC6-1968A2D43E73}";

            internal const string FlatFileDest = "{D658C424-8CF0-441C-B3C4-955E183B7FBA}";

            internal const string RawFileDest = "{485E7329-8754-42B4-AA5B-29C5DA09CAD5}";

            internal const string ExcelDestination = "{C9269E28-EBDE-4DED-91EB-0BF42842F9F4}";



            internal const string Lookup = "{27648839-180F-45E6-838D-AFF53DF682D2}";

            internal const string FuzzyLookup = "{5056651F-F227-4978-94DF-53CDF9E8CCB6}";



            internal const string ManagedComponentWrapper = "{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}";



            internal const string DerivedColumn = "{2932025B-AB99-40F6-B5B8-783A73F80E24}";

            internal const string MultipleHash = "Martin.SQLServer.Dts.MultipleHash, MultipleHash2008, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44";

            internal const string KimballSCD = "MouldingAndMillwork.SSIS.KimballMethodSCD, KimballMethodSCD100, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c";

            internal const string OLEDBCommand = "{8E61C8F6-C91D-43B6-97EB-3423C06571CC}";

#endif

        }



        private const string objectTypePackage = "SSIS Package";



        private const string attributePackageLocation = "PackageLocation";

        private const string attributePackageID = "PackageGUID";



        private OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder();



        /// <summary>

        /// information about registered pipeline components

        /// </summary>

        private PipelineComponentInfos pipelineComponentInfos = null;



        /// <summary>

        ///  information about registered connections

        /// </summary>

        private ConnectionInfos connectionInfos = null;



        /// <summary>

        /// information about registered tasks

        /// </summary>

        private TaskInfos taskInfos = null;



        private Dictionary<string, string> connectionTypeToIDMap = null;



        public SSISEnumerator()

        {

        }



        public bool Initialize(Repository repository)

        {

            bool success;



            try

            {

                this.repository = repository;



                // lets handle the infos enumeration right here.

                app = new Application();



                EnumerateInfos(app);



                success = true;

            }

            catch (System.Exception ex)

            {

                Console.WriteLine(string.Format("Could not initialize the SSIS Packages Enumerator: {0}", ex.Message));

                success = false;

            }



            return success;

        }



        /// <summary>

        ///  enumerates all packages stored off in SQL Server database

        /// </summary>

        /// <param name="sqlConnectionString"></param>

        public void EnumerateSqlPackages(string server, string user, string pwd, string[] rootFolders)

        {

            try

            {

                Queue<string> folders = new Queue<string>();



                foreach (string folderName in rootFolders)

                {

                    folders.Enqueue(folderName);

                }



                if (folders.Count == 0)

                {

                    folders.Enqueue("\\"); // the root folder

                }



                do

                {

                    string folder = folders.Dequeue();



                    PackageInfos packageInfos = app.GetPackageInfos(folder, server, user, pwd);

                    foreach (PackageInfo packageInfo in packageInfos)

                    {

                        string location = packageInfo.Folder + "\\" + packageInfo.Name;

                        if (packageInfo.Flags == DTSPackageInfoFlags.Folder)

                        {

                            folders.Enqueue(location);

                        }

                        else

                        {

                            Debug.Assert(packageInfo.Flags == DTSPackageInfoFlags.Package);



                            try

                            {

                                Console.Write(string.Format("Loading SQL package '{0}'... ", location));

                                using (Package package = app.LoadFromSqlServer(location, server, user, pwd, null))

                                {

                                    EnumeratePackage(package, location);

                                }

                                Console.WriteLine("Completed Successfully.");

                            }

                            catch (System.Exception ex2)

                            {

                                Console.WriteLine(string.Format("Error occurred: '{0}'", ex2.Message));

                            }

                        }

                    }

                } while (folders.Count > 0);

            }

            catch (System.Exception ex)

            {

                Console.WriteLine(string.Format("Error enumerating packages on SQL Server '{0}': {1}", server, ex.Message));

            }

        }



        /// <summary>

        /// Enumerates all packages that're in a directory and all sub directories if user asked us to.

        /// </summary>

        /// <param name="rootFolders"></param>

        /// <param name="recurseSubFolders"></param>

        public void EnumerateFileSystemPackages(string[] rootFolders, bool recurseSubFolders)

        {

            foreach (string rootFolder in rootFolders)

            {

                if (System.IO.Directory.Exists(rootFolder) == false)

                {

                    throw new Exception(string.Format("Root package folder '{0}' doesn't exist.", rootFolder));

                }



                EnumeratePackages(rootFolder, dtsxPattern, recurseSubFolders);

            }

        }



        private void EnumeratePackages(string rootFolder, string pattern, bool recurseSubFolders)

        {

            Console.Write("Enumerating packages...");

            string[] filesToInspect = System.IO.Directory.GetFiles(rootFolder, pattern, (recurseSubFolders) ? System.IO.SearchOption.AllDirectories : System.IO.SearchOption.TopDirectoryOnly);



            Console.WriteLine("done.");



            foreach (string packageFileName in filesToInspect)

            {

                try

                {

                    Console.Write(string.Format("Loading file package '{0}'... ", packageFileName));



                    // load the package

                    using (Package package = app.LoadPackage(packageFileName, null))

                    {

                        EnumeratePackage(package, packageFileName);

                    }



                    Console.WriteLine("Completed Successfully.");

                }

                catch (System.Exception ex)

                {

                    Console.WriteLine(string.Format("Error occurred: '{0}'", ex.Message));

                }

            }

        }



        private void EnumerateInfos(Application app)

        {

            Console.Write("Enumerating registered SSIS Data Flow components...");

            pipelineComponentInfos = app.PipelineComponentInfos;

            foreach (PipelineComponentInfo pipelineComponentInfo in pipelineComponentInfos)

            {

                repository.AddObjectType(Repository.Domains.SSIS, pipelineComponentInfo.ID, pipelineComponentInfo.Name, pipelineComponentInfo.Description);

            }

            Console.WriteLine("Done");



            Console.Write("Enumerating registered SSIS Connection Managers...");



            connectionInfos = app.ConnectionInfos;

            connectionTypeToIDMap = new Dictionary<string, string>(connectionInfos.Count);

            foreach (ConnectionInfo connectionInfo in connectionInfos)

            {

                connectionTypeToIDMap.Add(connectionInfo.ConnectionType, connectionInfo.ID);

                repository.AddObjectType(Repository.Domains.SSIS, connectionInfo.ID, connectionInfo.Name, connectionInfo.Description);

            }



            Console.WriteLine("Done");



            Console.Write("Enumerating registered SSIS Tasks...");



            taskInfos = app.TaskInfos;

            foreach (TaskInfo taskInfo in taskInfos)

            {

                repository.AddObjectType(Repository.Domains.SSIS, taskInfo.ID, taskInfo.Name, taskInfo.Description);

            }



            Console.WriteLine("Done");

        }



        /// <summary>

        /// recurses through all dtsContainers looking for any pipelines...

        /// Now handles ForEachLoop, ForLoop and Sequence Containers

        /// Disabled Tasks are not tested...

        /// </summary>

        /// <param name="package">The SSIS Package that is being investigated</param>

        /// <param name="location">The text string of the location of this package</param>

        /// <param name="packageRepositoryID">The internal ID for this package</param>

        /// <param name="dtsContainer">The Container that is to be assessed</param>

        private void EnumerateTask(Package package, string location, int packageRepositoryID, DtsContainer dtsContainer)

        {

            if (!dtsContainer.Disable)

            {

                if (dtsContainer is TaskHost)

                {

                    TaskHost taskHost = dtsContainer as TaskHost;



                    IDTSPipeline pipeline = taskHost.InnerObject as IDTSPipeline;



                    if (pipeline != null)

                    {

                        // this is a data flow task

                        packageRepositoryID = InspectDataFlow(pipeline, packageRepositoryID, package, taskHost, location);

                    }

                    else if (dtsContainer.CreationName.Contains("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask"))

                    {

                        EnumerateSqlTask(package, location, packageRepositoryID, taskHost);

                    }

                }

                else if (dtsContainer is ForEachLoop)

                {

                    ForEachLoop felContainer = dtsContainer as ForEachLoop;

                    foreach (DtsContainer innerDtsContainer in felContainer.Executables)

                    {

                        EnumerateTask(package, location, packageRepositoryID, innerDtsContainer);

                    }

                }

                else if (dtsContainer is ForLoop)

                {

                    ForLoop flContainer = dtsContainer as ForLoop;

                    foreach (DtsContainer innerDtsContainer in flContainer.Executables)

                    {

                        EnumerateTask(package, location, packageRepositoryID, innerDtsContainer);

                    }

                }

                else if (dtsContainer is Sequence)

                {

                    Sequence sContainer = dtsContainer as Sequence;

                    foreach (DtsContainer innerDtsContainer in sContainer.Executables)

                    {

                        EnumerateTask(package, location, packageRepositoryID, innerDtsContainer);

                    }

                }

            }

        }



        /// <summary>

        /// Handles parsing an SQLTask object, and finding what is used within it.

        /// </summary>

        /// <param name="package">The SSIS Package that is being investigated</param>

        /// <param name="location">The text string of the location of this package</param>

        /// <param name="packageRepositoryID">The internal ID for this package</param>

        /// <param name="taskHost">The SQL Task that is to be investigated.</param>

        private void EnumerateSqlTask(Package package, string location, int packageRepositoryID, TaskHost taskHost)

        {

            if (packageRepositoryID == -1)

            {

                // add this package to the repository

                packageRepositoryID = AddObject(package.Name, package.Description, objectTypePackage, repository.RootRepositoryObjectID);

                repository.AddAttribute(packageRepositoryID, attributePackageLocation, location);

                repository.AddAttribute(packageRepositoryID, attributePackageID, package.ID);

            }

            // Add this task to the repository

            int sqlTaskRepositoryObjectID = AddObject(taskHost.Name, taskHost.Description, taskHost.CreationName, packageRepositoryID);

            

            if (taskHost.Properties.Contains("SqlStatementSource") & taskHost.Properties.Contains("SqlStatementSourceType") & taskHost.Properties.Contains("Connection"))

            {

                string queryDefinition = string.Empty;



                SqlStatementSourceType stmtSource = (SqlStatementSourceType)taskHost.Properties["SqlStatementSourceType"].GetValue(taskHost);

                switch (stmtSource)

                {

                    case SqlStatementSourceType.DirectInput:

                        queryDefinition = taskHost.Properties["SqlStatementSource"].GetValue(taskHost).ToString();

                        repository.AddAttribute(sqlTaskRepositoryObjectID, Repository.Attributes.QueryDefinition, queryDefinition);

                        break;

                    case SqlStatementSourceType.FileConnection:

                        break;

                    case SqlStatementSourceType.Variable:

                        queryDefinition = GetVariable(package, taskHost.Properties["SqlStatementSource"].GetValue(taskHost).ToString());

                        repository.AddAttribute(sqlTaskRepositoryObjectID, Repository.Attributes.QueryDefinition, queryDefinition);

                        break;

                    default:

                        throw new Exception(string.Format("Invalid Sql Statement Source Type {0}.", stmtSource));

                }

                ConnectionManager connectionManager = package.Connections[taskHost.Properties["Connection"].GetValue(taskHost).ToString()];



                    string connectionManagerType = connectionManager.CreationName;

                    int connectionID = repository.GetConnection(connectionManager.ConnectionString);



                    string serverName = null;

                    if (connectionID == -1)

                    {

                        connectionID = CreateConnection(connectionManager, out serverName);

                    }



                    if (!string.IsNullOrEmpty(queryDefinition))

                    {

                        try

                        {

                            SqlStatement toBeParsed = new SqlStatement();



                            //toBeParsed.sqlString = statement;

                            toBeParsed.quotedIdentifiers = true;

                            if (toBeParsed.ParseString(queryDefinition))

                            {

                                foreach (string tableName in toBeParsed.getTableNames(true))

                                {

                                    int tableID = repository.GetTable(connectionID, tableName);

                                    repository.AddMapping(tableID, sqlTaskRepositoryObjectID);

                                }

                                foreach (string procedureName in toBeParsed.getProcedureNames(true))

                                {

                                    int procID = repository.GetProcedure(connectionID, procedureName);

                                    repository.AddMapping(procID, sqlTaskRepositoryObjectID);

                                }

                                foreach (string funcName in toBeParsed.getFunctionNames(true))

                                {

                                    int funcID = repository.GetFunction(connectionID, funcName);

                                    repository.AddMapping(funcID, sqlTaskRepositoryObjectID);

                                }

                            }

                            else

                            {

                                string errorMessage = "The following messages where generated whilst parsing the sql statement\r\n" + queryDefinition + "\r\n";

                                foreach (string error in toBeParsed.parseErrors)

                                {

                                    errorMessage += error + "\r\n";

                                }

                                Console.WriteLine(errorMessage);

                            }

                        }

                        catch (System.Exception err)

                        {

                            Console.WriteLine("The exception \r\n{0}\r\nwas raised against query \r\n{1}\r\nPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspx\r\n", err.Message, queryDefinition);

                        }

                    }

            }

            //ExecuteSQLTask sqlTask = taskHost.InnerObject as ExecuteSQLTask;

            //string queryDefinition = sqlTask.SqlStatementSource;

            //// add the query definition attribute if we have one

            //if (queryDefinition != null)

            //{

            //    repository.AddAttribute(sqlTaskRepositoryObjectID, Repository.Attributes.QueryDefinition, queryDefinition);

            //}

        }



        /// <summary>

        /// handles loading information about a package to the repository

        /// </summary>

        /// <param name="package"></param>

        private void EnumeratePackage(Package package, string location)

        {

            // add this package to the repository

            int packageRepositoryID = AddObject(package.Name, package.Description, objectTypePackage, repository.RootRepositoryObjectID);

            repository.AddAttribute(packageRepositoryID, attributePackageLocation, location);

            repository.AddAttribute(packageRepositoryID, attributePackageID, package.ID);



            // loop through all data flow tasks

            foreach (DtsContainer dtsContainer in package.Executables)

            {

                EnumerateTask(package, location, packageRepositoryID, dtsContainer);

            }



            //foreach (Executable executable in package.Executables)

            //{

            //    if (executable is TaskHost)

            //    {

            //        if ((executable as TaskHost).InnerObject is ExecuteSQLTask)

            //        {

            //            EnumerateSqlTask(package, location, packageRepositoryID, (executable as TaskHost));

            //        }

            //    }

            //}

        }



        /// <summary>

        /// adds interesting components to the repository. If at least one interesting object exists in the data flow task 

        /// the data flow task is also added to the repository. If at least one such interesting data flow task exists in the

        /// package and the package hasn't been created in the repository as yet, it will be created as well. What's returned

        /// is the package repository ID.

        /// </summary>

        /// <param name="packageRepositoryID"></param>

        /// <param name="package"></param>

        /// <param name="taskHost"></param>

        /// <returns></returns>

        private int InspectDataFlow(IDTSPipeline pipeline, int packageRepositoryID, Package package, TaskHost taskHost, string packageLocation)

        {

            int dataFlowRepositoryObjectID = -1;



            Dictionary<int, int> componentIDToSourceRepositoryObjectMap = new Dictionary<int, int>();

            Dictionary<int, int> componentIDToRepositoryObjectMap = new Dictionary<int, int>();



            // go through all components looking for sources or destinations

            foreach (IDTSComponentMetaData component in pipeline.ComponentMetaDataCollection)

            {

                EnumerateDataFlowComponent(ref packageRepositoryID, package, taskHost, packageLocation, ref dataFlowRepositoryObjectID, ref componentIDToSourceRepositoryObjectMap, ref componentIDToRepositoryObjectMap, component);

            }



            // after all the objects have been added, traverse their mappings

            if (componentIDToSourceRepositoryObjectMap.Count > 0)

            {

                EnumerateMappings(pipeline, dataFlowRepositoryObjectID, componentIDToSourceRepositoryObjectMap, componentIDToRepositoryObjectMap);

            }



            return packageRepositoryID;

        }



        private void EnumerateDataFlowComponent(ref int packageRepositoryID, Package package, TaskHost taskHost, string packageLocation, ref int dataFlowRepositoryObjectID, ref Dictionary<int, int> componentIDToSourceRepositoryObjectMap, ref Dictionary<int, int> componentIDToRepositoryObjectMap, IDTSComponentMetaData component)

        {

            string objectType;

            DTSPipelineComponentType dataFlowComponentType;

            string domain, tableOrViewName, queryDefinition;

            bool tableOrViewSource;

            string kimballPropertyValue;

            XmlReaderSettings settings = new XmlReaderSettings();

            settings.IgnoreComments = true;

            settings.IgnoreProcessingInstructions = true;

            settings.IgnoreWhitespace = true;

            XmlReader objXMLText;



            // if we have a source, note it as the starting point and add it to the repository

            if (IsComponentInteresting(package, component, out objectType, out dataFlowComponentType, out domain, out tableOrViewName, out tableOrViewSource, out queryDefinition))

            {

                // if the data flow object itself hasn't been created as  yet, do so now

                if (dataFlowRepositoryObjectID == -1)

                {

                    // if the package itself doesn't exist either, create it now

                    if (packageRepositoryID == -1)

                    {

                        // add this package to the repository

                        packageRepositoryID = AddObject(package.Name, package.Description, objectTypePackage, repository.RootRepositoryObjectID);

                        repository.AddAttribute(packageRepositoryID, attributePackageLocation, packageLocation);

                        repository.AddAttribute(packageRepositoryID, attributePackageID, package.ID);

                    }



                    dataFlowRepositoryObjectID = AddObject(taskHost.Name, taskHost.Description, taskHost.CreationName, packageRepositoryID);

                }



                // add the component to the repository

                int componentRepositoryID = AddObject(component.Name, component.Description, objectType, dataFlowRepositoryObjectID);



                Debug.Assert(componentRepositoryID > 0);



                if (dataFlowComponentType == DTSPipelineComponentType.SourceAdapter)

                {

                    componentIDToSourceRepositoryObjectMap.Add(component.ID, componentRepositoryID);

                }



                componentIDToRepositoryObjectMap.Add(component.ID, componentRepositoryID);



                // add the query definition attribute if we have one

                if (queryDefinition != null)

                {

                    repository.AddAttribute(componentRepositoryID, Repository.Attributes.QueryDefinition, queryDefinition);





                }

                if (component.ComponentClassID == ClassIDs.OLEDBCommand)

                {

                    GetStringComponentPropertyValue(component, "SqlCommand");

                }

                else if (component.ComponentClassID == ClassIDs.DerivedColumn)

                {

                    foreach (IDTSInput localInput in component.InputCollection)

                    {

                        if (localInput.IsAttached)

                        {

                            foreach (IDTSInputColumn localIColumn in localInput.InputColumnCollection)

                            {

                                if (localIColumn.CustomPropertyCollection.Count == 2)

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Expression " + localIColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString());

                                }

                                else

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Expression (See Ouput Column)");

                                }

                                //repository.AddObject(localIColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                            }

                        }

                    }

                    foreach (IDTSOutput localOutput in component.OutputCollection)

                    {

                        if (localOutput.IsAttached)

                        {

                            foreach (IDTSOutputColumn localOColumn in localOutput.OutputColumnCollection)

                            {

                                if (localOColumn.CustomPropertyCollection.Count == 2)

                                {

                                    repository.AddAttribute(componentRepositoryID, localOutput.Name + " [" + localOColumn.Name + "] [ID: " + localOColumn.ID.ToString() + "]", FormatColumnDescription(localOColumn.Name, localOColumn.DataType, localOColumn.Length, localOColumn.Precision, localOColumn.Scale) + " Expression " + localOColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString());

                                }

                                //repository.AddObject(localOColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                                //ToDo: Add connection from Input to Output Column.

                            }

                        }

                    }

                }

                else if (component.ComponentClassID == ClassIDs.Lookup)

                {

                    foreach (IDTSInput localInput in component.InputCollection)

                    {

                        if (localInput.IsAttached)

                        {

                            foreach (IDTSInputColumn localIColumn in localInput.InputColumnCollection)

                            {

                                if (localIColumn.CustomPropertyCollection.Count == 2)

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Reference Column [" + localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value.ToString() + "]");

                                }

                                else

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Expression (See Ouput Column)");

                                }

                                //repository.AddObject(localIColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                            }

                        }

                    }

                    foreach (IDTSOutput localOutput in component.OutputCollection)

                    {

                        if (localOutput.IsAttached)

                        {

                            foreach (IDTSOutputColumn localOColumn in localOutput.OutputColumnCollection)

                            {

                                if (localOColumn.CustomPropertyCollection.Count == 1)

                                {

                                    repository.AddAttribute(componentRepositoryID, localOutput.Name + " [" + localOColumn.Name + "] [ID: " + localOColumn.ID.ToString() + "]", FormatColumnDescription(localOColumn.Name, localOColumn.DataType, localOColumn.Length, localOColumn.Precision, localOColumn.Scale) + " Return Column [" + localOColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value.ToString() + "]");

                                }

                                //repository.AddObject(localOColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                            }

                        }

                    }

                }

                else if (component.ComponentClassID == ClassIDs.FuzzyLookup)

                {

                    foreach (IDTSInput localInput in component.InputCollection)

                    {

                        if (localInput.IsAttached)

                        {

                            foreach (IDTSInputColumn localIColumn in localInput.InputColumnCollection)

                            {

                                if (localIColumn.CustomPropertyCollection.Count == 5)

                                {

                                    if (localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value != null)

                                    {

                                        repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Reference Column [" + localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value.ToString() + "]");

                                    }

                                    else

                                    {

                                        repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale));

                                    }

                                }

                                else

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Expression (See Ouput Column)");

                                }

                                //repository.AddObject(localIColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                            }

                        }

                    }

                    foreach (IDTSOutput localOutput in component.OutputCollection)

                    {

                        if (localOutput.IsAttached)

                        {

                            foreach (IDTSOutputColumn localOColumn in localOutput.OutputColumnCollection)

                            {

                                if (localOColumn.CustomPropertyCollection.Count == 3)

                                {

                                    if (localOColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value != null)

                                    {

                                        repository.AddAttribute(componentRepositoryID, localOutput.Name + " [" + localOColumn.Name + "] [ID: " + localOColumn.ID.ToString() + "]", FormatColumnDescription(localOColumn.Name, localOColumn.DataType, localOColumn.Length, localOColumn.Precision, localOColumn.Scale) + " Return Column [" + localOColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value.ToString() + "]");

                                    }

                                    else

                                    {

                                        repository.AddAttribute(componentRepositoryID, localOutput.Name + " [" + localOColumn.Name + "] [ID: " + localOColumn.ID.ToString() + "]", FormatColumnDescription(localOColumn.Name, localOColumn.DataType, localOColumn.Length, localOColumn.Precision, localOColumn.Scale));

                                    }

                                }

                                else

                                {

                                    repository.AddAttribute(componentRepositoryID, localOutput.Name + " [" + localOColumn.Name + "] [ID: " + localOColumn.ID.ToString() + "]", FormatColumnDescription(localOColumn.Name, localOColumn.DataType, localOColumn.Length, localOColumn.Precision, localOColumn.Scale));

                                }

                                //repository.AddObject(localOColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                            }

                        }

                    }

                }

                else if (component.ComponentClassID == ClassIDs.ManagedComponentWrapper)

                {

                    if (component.CustomPropertyCollection["UserComponentTypeName"].Value.ToString() == ClassIDs.MultipleHash)

                    {

                        foreach (IDTSInput localInput in component.InputCollection)

                        {

                            if (localInput.IsAttached)

                            {

                                foreach (IDTSInputColumn localIColumn in localInput.InputColumnCollection)

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale));

                                    //repository.AddObject(localIColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                                }

                            }

                        }

                        foreach (IDTSOutput localOutput in component.OutputCollection)

                        {

                            if (localOutput.IsAttached)

                            {

                                foreach (IDTSOutputColumn localOColumn in localOutput.OutputColumnCollection)

                                {

                                    if (localOColumn.CustomPropertyCollection.Count == 2)

                                    {

                                        string localResults = "";

                                        if (localOColumn.CustomPropertyCollection["InputColumnLineageIDs"].Value.ToString().Length > 0)

                                        {

                                            foreach (string localIDs in localOColumn.CustomPropertyCollection["InputColumnLineageIDs"].Value.ToString().Split(','))

                                            {

                                                string withoutHash = localIDs;

                                                if (localIDs[0] == '#')

                                                {

                                                    withoutHash = localIDs.Substring(1);

                                                }

                                                if (localResults.Length == 0)

                                                {

                                                    localResults = component.InputCollection[0].InputColumnCollection.GetInputColumnByLineageID(System.Convert.ToInt32(withoutHash)).Name;

                                                }

                                                else

                                                {

                                                    localResults += ", " + component.InputCollection[0].InputColumnCollection.GetInputColumnByLineageID(System.Convert.ToInt32(withoutHash)).Name;

                                                }

                                            }

                                        }

                                        repository.AddAttribute(componentRepositoryID, localOutput.Name + " [" + localOColumn.Name + "] [ID: " + localOColumn.ID.ToString() + "]", FormatColumnDescription(localOColumn.Name, localOColumn.DataType, localOColumn.Length, localOColumn.Precision, localOColumn.Scale) + " Generated From " + localResults);

                                    }

                                    //repository.AddObject(localOColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                                }

                            }

                        }

                    }

                    else if (component.CustomPropertyCollection["UserComponentTypeName"].Value.ToString() == ClassIDs.KimballSCD)

                    {

                        foreach (IDTSInput localInput in component.InputCollection)

                        {

                            if (localInput.IsAttached)

                            {

                                foreach (IDTSInputColumn localIColumn in localInput.InputColumnCollection)

                                {

                                    repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale));

                                    //repository.AddObject(localIColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);

                                }

                            }

                        }



                        foreach (IDTSOutput localOutput in component.OutputCollection)

                        {

                            if (localOutput.IsAttached)

                            {

                                kimballPropertyValue = string.Empty;

                                switch (localOutput.Name)

                                {

                                    case "New":

                                        kimballPropertyValue = component.CustomPropertyCollection["New Output Columns"].Value.ToString();

                                        break;

                                    case "Unchanged":

                                        kimballPropertyValue = component.CustomPropertyCollection["Unchanged Output Columns"].Value.ToString();

                                        break;

                                    case "Deleted":

                                        kimballPropertyValue = component.CustomPropertyCollection["Deleted Output Columns"].Value.ToString();

                                        break;

                                    case "Updated SCD1":

                                        kimballPropertyValue = component.CustomPropertyCollection["Update SCD1 Columns"].Value.ToString();

                                        break;

                                    case "Auditing":

                                        kimballPropertyValue = component.CustomPropertyCollection["Audit Columns"].Value.ToString();

                                        break;

                                    case "Expired SCD2":

                                        kimballPropertyValue = component.CustomPropertyCollection["Expired SCD2 Columns"].Value.ToString();

                                        break;

                                    case "Invalid Input":

                                        break;

                                    case "New SCD2":

                                        kimballPropertyValue = component.CustomPropertyCollection["New SCD2 Columns"].Value.ToString();

                                        break;

                                    default:

                                        break;

                                }

                                string localResults = "Unknown.";

                                if (kimballPropertyValue != string.Empty)

                                {

                                    objXMLText = XmlReader.Create(new System.IO.StringReader(kimballPropertyValue), settings);

                                    objXMLText.Read();

                                    foreach (IDTSOutputColumn localOColumn in localOutput.OutputColumnCollection)

                                    {

                                        string strLineageID;

                                        string strMappedToExistingDimensionLineageID;



                                        objXMLText.ReadToFollowing("OutputColumnDefinition");

                                        do

                                        {

                                            localResults = "Unknown.";

                                            strLineageID = objXMLText.GetAttribute("LineageID");

                                            strMappedToExistingDimensionLineageID = objXMLText.GetAttribute("MappedToExistingDimensionLineageID");

                                            if (localOColumn.LineageID == System.Convert.ToInt32(strLineageID))

                                            {

                                                foreach (IDTSInput localInput in component.InputCollection)

                                                {

                                                    IDTSInputColumn temp;

                                                    try

                                                    {

                                                        temp = localInput.InputColumnCollection.GetInputColumnByLineageID(System.Convert.ToInt32(strMappedToExistingDimensionLineageID));

                                                        localResults = "[" + localInput.Name + "] Column [" + temp.Name + "].";

                                                        break;

                                                    }

                                                    catch (Exception)

                                                    {

                                                    }

                                                }

                                                break;

                                            }

                                        }

                                        while (objXMLText.ReadToFollowing("OutputColumnDefinition"));



 …

Large files files are truncated, but you can click here to view the full file