在SSIS中使用自定義的DLL檔案

步驟

１、開發dll(需要簽名）

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.Xml.Schema;

namespace ETLXmlParser

{

    public class ETLXmlParser

        private static bool isValid = true;

        public static bool Validate(string XmlFilepath, string XsdFilePath)

            try

            XmlReader reader;

            XmlReaderSettings settings = new XmlReaderSettings();

            XmlSchemaSet schemaSet = new XmlSchemaSet();

            schemaSet.Add(null, XsdFilePath);

            settings.Schemas.Add(schemaSet);

            settings.ValidationType = ValidationType.Schema;

            settings.ValidationEventHandler += new ValidationEventHandler(settings_ValidationEventHandler);

            settings.ValidationFlags = XmlSchemaValidationFlags.ReportValidationWarnings | XmlSchemaValidationFlags.ProcessInlineSchema | XmlSchemaValidationFlags.AllowXmlAttributes | XmlSchemaValidationFlags.ProcessIdentityConstraints | XmlSchemaValidationFlags.ProcessSchemaLocation;

            reader = XmlReader.Create(XmlFilepath, settings);

            while (reader.Read())

                string xmlFile = reader.Value;

            }

            reader.Close();

            return isValid;

            catch(Exception ex)

                return false;

        }

        private static void settings_ValidationEventHandler(object sender, ValidationEventArgs e)

            isValid = false;

    }

}

2 将編譯好的dll拷貝到C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents（SQL Server 安裝目錄）和C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

3、将DLL拖進C:\WINDOWS\assembly檔案夾，也可以用指令安裝該dll（gacutil.exe /i myassembly.dll）

4 在SSIS中拖入一個Script Task,然後設計腳本，添加應用dll