SSIS advanced Content Series 1

1. Introduction

Microsoft SQL Server 2005 integration services (SSIS) is a platform for generating high-performance data integration solutions, including data warehouse extraction, conversion, and loading (ETL) packages.

(1) Data Import wizard

(2) ETL Tool

(3) Control Flow Engine

(4) Application Platform

(5) High-Performance Data Conversion Data Pipeline

In ETL extraction tools, SSIS is widely used for its low cost, ease of use, and powerful functions.

This article is a previous article on SSIS learning and research, which involves some advanced SSIS features. I hope it will be helpful for beginners to learn ETL.

2. Common tips 2.1 configuration files

The configuration file isMsdtssrvr. ini. xml", Located in"Under % Program Files % "Microsoft SQL Server" 90 "DTS" binn. The file location and name are stored in the value of the registry key "HKEY_LOCAL_MACHINE" software "Microsoft" msdts "serviceconfigfile. For example, the value is:

D: "Program Files" Microsoft SQL Server "90" DTS "binn" msdtssrvr. ini. xml

Note: After modifying the configuration file, you must restartIntegration services server.

2.2 modify the file system folder location

<? XML version = "1.0" encoding = "UTF-8"?>

<Dtsserviceconfiguration xmlns: XSD = "http://www.w3.org/2001/XMLSchema" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance">

<Stopexecutingpackagesonshutdown> true </stopexecutingpackagesonshutdown>

<Toplevelfolders>

<Folder xsi: TYPE = "sqlserverfolder">

<Name> MSDB </Name>

<Servername>. </servername>

</Folder>

<Folder xsi: TYPE = "filesystemfolder">

<Name> File System </Name>

<Storepath> .. "packages </storepath>

</Folder>

<Folder xsi: TYPE = "filesystemfolder">

<Name> odsm File System </Name>

<Storepath> D: "odsm ETL deployment </storepath>

</Folder>

</Toplevelfolders>

</Dtsserviceconfiguration>

The red part is the newly added content. As shown in:

2.3 modify the instance referenced by the server

If a computer is installed with multiple instances, the integration service does not support multiple instances. Therefore, you can modify the configuration file"Msdtssrvr. ini. xml", Which instance is specified.

The modification content is as follows:

<Folder xsi: TYPE = "sqlserverfolder">

<Name> MSDB </Name>

<Servername>. "sql2k5 </servername>

</Folder>

2.4 shared MSDB storage Zone

Store all the packages on a central server. Change the server configuration files of all other SSIS servers so that they point to the same SQL Server computer.

Advantages:

1. This ensures package security and backs up and maintains the package;

2. When the central SQL server used changes, the automatic settings used by people do not need to be changed;

3. Isolate permissions. This allows end users to run packages without having to know the specific storage location of these packages. The package storage location is controlled by the Administrator.

2.5 server Fault Diagnosis

Modify the configuration file"Msdtssrvr.exe. config"And file"Msdtssrvr. ini. xml"In the same folder. The red font is the modified and added content.

<? XML version = "1.0" encoding = "UTF-8"?>

<Configuration>

<Runtime>

<Gcserver enabled = "true"/>

</Runtime>

<System. Diagnostics>

<Switches>

<Add name = "traceclientconnections" value = "4"/>

<Add name = "tracemanagementcils" value = "4"/>

<Add name = "servertraceswitch" value = "4"/>

</Switches>

<Trace autoflush = "true">

<Listeners>

<Add name = "filelog" type = "system. Diagnostics. textwritertracelistener"

Initializedata = "D:" odsm ETL logs "SSIS server log" listener. log "/>

</Listeners>

</Trace>

</System. Diagnostics>

</Configuration>

Note: the value of the trail record category is different.

0: logs are not recorded;

1: only the error messages are logged;

2: only the error message and warning information are logged;

3: log more detailed error information;

4: record detailed tracing information in the log.

The listener. Log content is as follows:

Initializecomsecurity: Enter

Coinitializesecurity: 0x0.

Initializecomsecurity: Leave

Reading configuration file D: "Program Files" Microsoft SQL Server "90" DTS "binn" msdtssrvr. ini. xml

Config processed: 3 root folders

Registercomobject: Enter

Dllgetclassobject: 0x0, system. _ comobject

Coregisterclassobject: 0

Dllgetclassobject: 0x0, system. _ comobject

Coregisterclassobject: 0

Registercomobject: Leave

Dropped dtsapplication object

Created dtsapplication object

Opening client's process to get exited event.

Registering package package1 (b4a1b509-9ad3-4fb5-8e21-58385fc3f92c) for qiangguo "Guoqiang, assigned ID 7f1f0524-bf78-4e02-b276-eb37474c74d4

Package package1 (b4a1b509-9ad3-4fb5-8e21-58385fc3f92c) registered by qiangguo "Guoqiang, assigned ID 7f1f0524-bf78-4e02-b276-eb37474c74d4

Unregister package request: 7f1f0524-bf78-4e02-b276-eb37474c74d4

Package unregistered: 7f1f0524-bf78-4e02-b276-eb37474c74d4