Cómo recuperar información acerca de los paquetes SSIS almacenados en la Base de Datos MSDB

Introducción

Hoy en día, la mayor parte de las compañías medianas han implementado una solución de Almacén de datos (en inglés: Data Warehouse, DWH). Este DWH puede ser diseñado usando una serie de herramientas (o características) de Microsoft SQL Server. Una de ellas es SQL Server Integration Services, también conocido como SSIS.

Básicamente, esta característica (SSIS) nos permite extraer, transformar y cargar datos desde casi cualquier tipo de fuente a casi cualquier tipo de destino: archivos planos, archivos de Excel, RDBMS. Basados en nuestra versión de SQL Server, usaremos ya sea Visual Studio (comenzando por SQL Server 2012) o SQL Server BI Development Studio (antes de SQL Server 2012).

Microsoft define SSIS como una plataforma para construir soluciones de transformación e integración de datos a nivel empresarial. Podemos fácilmente imaginar que construir un almacén de datos no es la única aplicación para la que SSIS está diseñado. Hay un artículo e Technet listando diez usos típicos de SSIS: usos típicos de los Servicios de Integración.

Con algo de búsqueda básica, usted encontrará en la documentación de Microsoft artículos y entradas de blogs que hay dos tipos de productos entregables cuando trabajamos con los Servicios de Integración de SQL Server: podemos construir y desplegar productos entregables de SSIS como paquetes o proyectos.

Hay muchas diferencias entre estos dos modos:

Usted no puede desplegar proyectos previos a SQL Server 2012: el despliegue de proyectos apareció con esta versión. Este modo de despliegue se sobrepone al despliegue de paquetes, lo que ahora es conocido como “modo de despliegue heredado”.

Así que, antes de SQL Server 2012, sólo el despliegue de paquetes estaba disponible. Microsoft provee herramientas para migrar desde una versión antigua de paquetes SSIS a un proyecto SSIS que contiene a este paquete.
Un paquete puede ser comparado con código ejecutable o una descripción de flujo de trabajo. Un proyecto contiene un conjunto de paquetes y tiene funcionalidades avanzadas comparado a un simple paquete. Por ejemplo, usted puede definir una conexión a una instancia de SQL Server o un archivo Excel a nivel de proyecto. Esta conexión será accesible para todos los paquetes definidos en el proyecto. En modo heredado, usted necesitará crear la exacta misma conexión en cada paquete que agruparía en un proyecto.
Los paquetes y proyectos no son almacenados de la misma manera. Los primeros son almacenados ya sea en la base de datos msdb o en el sistema de archivos, mientras que los últimos son almacenados en una base de datos de SQL Server llamada Catálogo SSIS (SSIS Catalog).
Etc.

Usted puede encontrar una comparación en MSDN: Despliegue Proyectos y Paquetes de Servicios de Integración (SSIS).

Sin importar estas diferencias, en ambos modos, usted puede correr paquetes usando los Trabajos (programados) de SQL Server Agent.

Pero este artículo no es acerca del desarrollo de SSIS o los despliegues. Hay una plétora de tutoriales y cursos acerca del tópico disponibles en la red. De hecho, este artículo se enfocará en la extracción de información acerca de Paquetes SSIS (no proyectos) desplegados en la base de datos MSDB (no en el sistema de archivos), usando un conjunto de consultas T-SQL. Los resultados de esas consultas podrían estar almacenados en tablas dentro de una base de datos.

Este proceso de extracción fue diseñado para ser la entrada de un proceso de análisis para preparar una migración lado a lado y determinar con precisión aspectos problemáticos antes de continuar. El objetivo era planear correcciones preventivas un largo tiempo antes del proceso de migración real, cuando sea posible. Este proceso de análisis será eventualmente el tema de otro artículo. Pienso que el proceso entero (extracción más análisis) puede ser también usado para asegurar la calidad de desarrollo, o simplemente documentar los paquetes existentes.

Ahora, sumerjámonos en los fundamentos de SSIS. Veremos (o revisaremos) cómo un paquete SSIS es definido y diseñado. Esto nos permite determinar con precisión los componentes relevantes o propiedades que deberían ser útiles para extractar, sólo como una lista o para análisis posterior.

Paquetes SSIS: lo que necesitamos saber

En las siguientes secciones, a veces usaremos capturas de pantalla de un paquete SSIS en SQL Server BI Development Studio. El paquete usado es llamado “Lookup Sample”, y es tomado desde Muestras de Producto de Servicios de Integración de SQL Server. Nos referiremos a este paquete como nuestro paquete de demostración o paquete de ejemplo.

Composición y propiedades de los Paquetes SSIS (vista general)

Flujos

Primero que todo, cuando abrimos un paquete SSIS nuevo o existente en SQL Server Business Intelligence Development Studio o Visual Studio, notaremos que está compuesto en dos tipos de “flujos”:

El flujo de control: un flujo de trabajo que lista las tareas a realizar durante la ejecución del paquete. Las tareas pueden ser agrupadas en contenedores. En este caso, los contenedores son vistos como tareas indivisibles.
El flujo de datos: Un flujo de datos existe para la mayoría de las tareas sobre datos en el flujo de control. Es la manera en que los datos son tratados. Algunas tareas pueden ser definidas sin flujo de datos. Particularmente, las conexiones de bases de datos son usadas ahí (como Fuente de Datos o Destino de Datos).

Aquí está un diagrama deMicrosoft que resume la explicación anterior:

Usted encontrará abajo una captura de pantalla con un ejemplo de tarea de control y su correspondiente vista de flujo de datos.

Conexiones

En el flujo de datos, generalmente tomamos datos desde una fuente (extractar), eventualmente procesamos estos datos (transformar) y los almacenamos en un destino (cargar). La fuente y destino están definidos por conexiones. Hay varios tipos de conexiones: archivos XML, archivos CSV, SQL Server, ODBC, Bases de Datos Oracle, Excel…Dele un vistazo a esta página de documentación para más detalles. Múltiples conexiones pueden ser usadas en un solo paquete.

Definir y editar las conexiones es realizado usando un componente de SSIS llamado “Connection Manager (Administrador de Conexiones)”.

Aquí están las conexiones definidas en nuestro paquete de ejemplo.

Durante la creación, estableceremos las propiedades que describen la conexión física a ser usada durante la ejecución de este paquete. Entre esas propiedades, encontraremos el tipo de conexión (Excel, CSV, etc.) y la cadena de conexión.

Recomendación

Cuando defina las cadenas de conexión a la instancia de base de datos o a un anfitrión…

Use alias de DNS en lugar de la dirección IP o el nombre del servidor. Esto simplifica los cambios de administración.
Establezca la propiedad ApplicationName cuando sea posible. Esto le permitirá simplificar la depuración y el rastreo. Por ejemplo, en SQL Server Profiler, usted podrá filtrar en ese ApplicationName en particular. Recomiendo establecer esta propiedad en cualquier aplicación que desarrolle.

Propiedad Identifier

Cada paquete tiene un identificador único, el GUID. Como ejemplo, usted encontrará el ID para nuestro paquete de demostración.

Este identificador es generado en la creación y puede ser generado en cualquier momento, a demanda. Este identificador es usado para registrar y algunas otras funciones disponibles. Usted encontrará abajo la manera de generar un nuevo identificador: simplemente haga clic en la flecha cerca del ID actual, luego en “”.

Muchos desarrolladores SSIS tiene el hábito común que consiste en crear una plantilla de paquete. La ventaja es que ellos pueden reutilizarla para desarrollos futuros. Un problema con esta práctica es que no necesariamente generan un nuevo GUID. SI eso pasa, múltiples paquetes pueden compartir el mismo GUID, ¡llevando a un dolor de cabeza en el caso de la depuración (cuando el registro de SSIS es usado) !

Recomendación

Si usted define una plantilla de paquete, no olvide generar un nuevo GUID para el paquete creado basado en esa plantilla.

Variables y configuraciones

En cada paquete SSIS, podemos definir variables. Aquí están algunas variables definidas en nuestro paquete de demostración:

Como cualquier lenguaje de programación, las variables contienen valores disponibles en tiempo de ejecución. Por ejemplo, la variable User ::ErrorCount contendrá el número de errores que ocurrieron durante la ejecución.

Otro uso común de las variables consiste en mantener las cadenas de conexión que son generadas basadas, por ejemplo en el contenido de una tabla de SQL Server.

Generalmente, las variables vienen junto a las configuraciones del Paquete SSIS. Ellas están definidas como sigue:

Una configuración es un par propiedad/valor que usted añade a un paquete completado.

Organización de paquetes: carpetas

Finalmente, pero la última cosa a saber, los paquetes pueden ser agrupados en carpetas de paquetes. Podemos construir una jerarquía más o menos compleja o simplemente agrupar los paquetes por proyecto o por funcionalidad. Por ejemplo:

Descripción de un archivo de Paquete SSIS

Digamos que nuestro paquete está listo (o grabado pero aún bajo desarrollo), podemos construirlo de todos modos. Estas operaciones resultan en la creación de un archivo ‘dtsx’. Este archivo es realmente un archivo XML que describe completamente el paquete SSIS (flujos, variables, conexiones…). Usted encontrará un ejemplo de su contenido a continuación.

<?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"></DTS:Property>

<DTS:Property DTS:Name="CreatorComputerName"></DTS:Property>

<DTS:Property DTS:Name="CreationDate" DTS:DataType="7">5/14/2003 8:58:00 AM</DTS:Property>

<DTS:Property DTS:Name="PackageType">0</DTS:Property>

<DTS:Property DTS:Name="ProtectionLevel">0</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>

...

Como para cualquier XML bien formado, hay una Definición de Esquema XML correspondiente, también conocida como “XSD”. Para aquellos quienes no están acostumbrados a este término, aquí está una definición de Wikipedia:

XSD (XML Schema Definition, Definición de Esquema XML), una recomendación del Consorcio World Wide Web (W3C), especifica cómo describir formalmente los elementos en un documento de Lenguaje de Marcado Extensible (XML). Puede ser usado por programadores para verificar cada elemento contenido en un documento. Pueden verificar si se adhiere a la descripción del elemento en el que están colocados.

La definición correspondiente puede ser vista en esta página.

Objetos de administración SSIS (en la base de datos msdb)

En este artículo, extraemos información de paquetes SSIS que están almacenados en la base de datos msdb. Para este propósito, Microsoft nos provee con tres tablas. Usted notará que sólo dos de ellas son relevantes para ser usadas en lo siguiente de este artículo.

dbo.sysssispackages :

Contiene una fila por cada paquete que es grabado a Microsoft SQL Server.Hay una columna llamada packagedata, la cual es una copia exacta del contenido del archivo ‘dtsx’ usado en el despliegue.
dbo.sysssispackagefolders :

Contiene una fila por cada carpeta lógica en la jerarquía de carpetas que Microsoft SQL Server Integration Services usa.
dbo.sysssislog :

Contiene una fila por cada entrada de registro que es generado por paquetes o sus tareas y contenedores en tiempo de ejecución.

Cómo ejecutar un paquete SSIS usando SQL Server Agent

Correr un Paquete de SQL Server Integration Services usando SQL Server Agent consiste en crear y programar un Trabajo de SQL Server Agent del tipo “SSIS”. Una vez creado, usted puede ya sea correrlo cuando desee o dejarlo correr automáticamente (basado en los programas que usted defina).

Para aquellos de ustedes que están interesados en un procedimiento más directo, recomiendo ver la siguiente página de MSDN: Programar un Paquete usando SQL Server Agent..

De todas maneras, SQL Server Agent sólo tomará el rol de una interfaz a una herramienta de línea de comandos llamada “dtutil”. Usted encontrará en la página de documentación (Utilidad dtutil ) de esta herramienta que toma un parámetro /SourceServer, lo cual le permite a usted hacer llamadas a la administración remota de SSIS.

¿Qué recolectar?

Ahora estamos en un punto donde deberíamos definir las preguntas que podemos y deseamos responder.

Usted encontrará tal lista en la tabla a continuación. El propósito de esta lista es no ser exhaustiva, así que siéntase libre de contactarme con sus sugerencias.

		Debe tener	Debería tener	Podría tener	No tendrá
#	Pregunta
1	¿Qué paquetes SSIS son desplegados en un servidor, en qué carpeta?	X
2	¿Quién es el dueño de un Paquete SSIS dado?	X
3	¿Qué capacidad de almacenamiento toma un paquete dado?			X
4	¿Cuál es la versión de un paquete dado?			X
5	¿Qué conexiones son definidas en un paquete dado?	X
6	¿Qué proveedores son usados en el Administrador de Conexión (Connection Manager)?		X
7	¿Qué variables son definidas en un paquete dado?			X
8	¿Qué configuraciones de paquete están definidas en un paquete dado?				X
9	¿Qué paquetes son ejecutados usando un Trabajo de SQL Server Agent?	X
10	¿Cumple el Dueño del Paquete SSIS con los estándares empresariales?			X
11	¿Hay paquetes con conexiones que no cumplen con los estándares empresariales? Por ejemplo, referencias a un servidor local deberían ser puntualizadas. For instance, references to local server should be pointed out.	X
12	¿Hay trabajos de Microsoft SQL Server Agent corriendo Paquetes SSIS que no cumplen con los estándares empresariales? Por ejemplo, referencias a un servidor local deberían ser puntualizadas.	X

Basados en estas preguntas y todo lo que discutimos arriba, podemos listar las siguientes acciones:

¿Cómo recolectaremos datos?

Bien, mientras que el objetivo es bien conocido, aún necesitamos tomar tiempo para responder la siguiente pregunta: “¿cómo lo haremos?”.

Realmente, la respuesta será muy “simple”. Como hemos visto en una sección anterior, hay una columna llamada packagedata en la tabla dbo.sysssispackages (almacenada en la base de datos msdb). Mientras que esta tabla nos permitirá listar los paquetes SSIS (acción 1), la columna packagedata puede ser usada como entrada para extraer los atributos del paquete SSIS, como las conexiones (acción 2). De hecho, el contenido de esta columna es un XML y SQL Server provee todo lo que necesitamos para analizar/consultar XML. Es el lenguaje XQuery que es parte de T-SQL. Este lenguaje no es objeto de este artículo, así que no será completamente cubierto. Si usted está interesado en este tópico, por favor refiérase a la siguiente página en el sitio web de documentación de Microsoft: Introducción a XQuery en SQL Server 2005 o la página que describe todos los Métodos T-SQL para consultar Tipos de Datos XML.

Como se espera, no usaremos XQuery para obtener la lista de trabajos de SQL Agent que corren paquetes SSIS. Por qué? Simplemente porque no está almacenada en la columna packagedata, y ni siquiera en dbo.sysssispackages. En lugar de eso, consultaremos tablas relacionadas con SQL Server Agent, también en msdb:

La última acción que queda (acción 4) explorará los resultados de acciones previas, pero eso no es parte de este artículo.

En resumen, aquí está el algoritmo que seguiremos:

$PkgList = "Extract the list of SSIS Packages (with definition XML)" ;

Store $PkgList TO <UserTable> ;

Foreach $P in $PkgList {

"Extract the list of Connections in $P and store in <UserTable1>" ;

"Extract the list of SQL Server agent jobs steps using $P" ;

"Analyze collected data";

}

Acción 1: Extraer la lista de paquetes SSIS

Para extraer la lista de Paquetes SSIS, obviamente usaremos la tabla msdb.dbo.sysssispackages . Esta tabla sola debería ser suficiente, pero si usted desea obtener la ruta completa de un Paquete SSIS dado, hay algo más de trabajo por hacer. Esta tabla contiene una columna folderid que hace referencia a la columna clave con el mismo nombre en la tabla msdb.dbo.sysssispackagefolders. Las carpetas pueden ser estructuradas y tener una carpeta padre. Así que podemos encontrar rutas como /Finance/Billing o /CRM/Customers.

Ya que tenemos este identificador único para una carpeta dada, podemos aprovechar la característica Expresión Común de Tabla (Common Table Expression, CTE) y la recursividad de consulta para obtener la ruta completa de un Paquete SSIS dado.

Revisemos la estructura de la consulta objetivo. Ahí está la declaración del CTE seguida por la consulta SELECT real:

with ChildFolders

as (

UNION ALL

)

SELECT <ListOfColumns>

INTO <TemporaryTable>

FROM ChildFolders

Primero revisemos los detalles de una tabla objetivo temporal que contendrá los resultados de esa consulta. A continuación, usted encontrará las sentencias TSQL para crear tal tabla.

IF(OBJECT_ID('tempdb..#SSISPackagesList') IS NOT NULL)

BEGIN

EXEC sp_executesql N'DROP TABLE #SSISPackagesList';

END;

CREATE TABLE #SSISPackagesList (

PackageUniqifier BIGINT IDENTITY(1,1) NOT NULL,

PackageRunningId NVARCHAR(50) NOT NULL,

RootFolderName VARCHAR(256) NOT NULL,

ParentFolderFullPath VARCHAR(4000) NOT NULL,

PackageOwner VARCHAR(256) NOT NULL,

PackageName VARCHAR(256) NOT NULL,

PackageDescription VARCHAR(4000) NULL,

isEncrypted BIT NOT NULL,

PackageFormat4Version CHAR(4) NOT NULL,

PackageType VARCHAR(128) NOT NULL,

CreationDate DATETIME NULL,

PackageVersionMajor TINYINT NOT NULL,

PackageVersionMinor TINYINT NOT NULL,

PackageVersionBuild INT NOT NULL,

PackageVersionComments VARCHAR(4000) NOT NULL,

PackageSizeKb BIGINT NULL,

PackageXmlContent XML NULL

);

La siguiente tabla mapea las columnas creadas en la última sentencia T-SQL con las respuestas que necesitamos proveer.

#	Pregunta	Lista de columnas
1	¿Qué paquetes SSIS son desplegados en un servidor, en qué carpeta? /td>	PackageRunningId ParentFolderFullPath PackageName PackageDescription
2	¿Quién es el dueño de un Paquete SSIS dado?	PackageOwner
3	¿Qué capacidad de almacenaje toma un paquete dado?	PackageSizeKb
4	¿Cuál es la versión de un paquete dado?	PackageVersionMajor PackageVersionMinor PackageVersionBuild PackageVersionComments

Nota

Mientras que la columna PackageRunningId podría ser usada como la clave primaria para esta tabla, yo no lo recomendaría y sugeriría, como hice con PackageUniqifier, añadir otro identificador (como una columna (like an IDENTITY ), por un hábito común de usar una plantilla de paquete SSIS sin generar un nuevo identificador.

Hay otras columnas que también mantenemos, ya que pueden ser útiles algún día. Por ejemplo, la columna isEncryptedpuede permitirnos determinar con precisión la necesidad de extraer un certificado u obtener una contraseña usada para encriptar este paquete. También tomaremos otras columnas para un eventual análisis.

Ahora que conocemos qué información es necesitada, tomaremos el tiempo para construir la consulta que llenará la tabla temporal #SSISPackagesList paso a paso.

Primero, obtendremos la lista de las “carpetas de paquetes a nivel de raíz”, por ejemplo, carpetas que están creadas en la raíz de la jerarquía de carpetas. Note que los siguientes parámetros son usados:

@RootLabel mantendrá la cadena a ser usada como etiqueta raíz. Por ejemplo “/”.
@SeparatorChar mantendrá el carácter separador de carpetas a ser usado.

Query 1 – get root-level packages

select

PARENT.parentfolderid,

PARENT.folderid,

PARENT.foldername,

cast(@RootLabel as sysname) as RootFolder,

cast(CASE WHEN (LEN(PARENT.foldername) = 0)

THEN @SeparatorChar

ELSE PARENT.foldername

END as varchar(max)) as FullPath,

0 as Lvl

from msdb.dbo.sysssispackagefolders PARENT

where PARENT.parentfolderid is null

La consulta previa traerá de vuelta el conjunto mínimo de carpetas, las carpetas de primer nivel. Eso corresponde a la etiqueta <FirstLevelQuery> introducida previamente. Note que hay una columna FullPath que retiene la ruta completa de la carpeta (la cual es exactamente lo que queremos obtener al momento). Para iniciar la recursión, tomaremos la unión de este conjunto mínimo con otra sentencia SELECT . Esta sentencia usará la tabla msdb.dbo.sysssispackagefolders combinada con el CTE en sí mismo, basado en la igualdad entre el valor de la columna folderid de esta tabla y el valor de la columna parentfolderiden el CTE. Corresponde a la etiqueta <SelfRecursiveQuery>.

select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

case ChildFolders.Lvl

when 0 then CHILD.foldername

else ChildFolders.RootFolder

end as RootFolder,

cast(CASE WHEN (ChildFolders.FullPath = @SeparatorChar)

THEN ''

ELSE ChildFolders.FullPath

END + @SeparatorChar + CHILD.foldername as varchar(max))

as FullPath,

ChildFolders.Lvl + 1 as Lvl

from msdb.dbo.sysssispackagefolders CHILD

inner join ChildFolders

on ChildFolders.folderid = CHILD.parentfolderid

Finalmente, podemos obtener de vuelta la lista de paquetes SSIS con su ruta completa en la jerarquía de carpetas. La siguiente sentencia es la consulta T-SQL completa que llena la tabla temporal #SSISPackagesList.

with ChildFolders

as (

select

PARENT.parentfolderid,

PARENT.folderid,

PARENT.foldername,

cast(@RootLabel as sysname) as RootFolder,

cast(CASE

WHEN (LEN(PARENT.foldername) = 0) THEN @SeparatorChar

ELSE PARENT.foldername

END as varchar(max)) as FullPath,

0 as Lvl

from msdb.dbo.sysssispackagefolders PARENT

where PARENT.parentfolderid is null

UNION ALL

select

CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

case ChildFolders.Lvl

when 0 then CHILD.foldername

else ChildFolders.RootFolder

end as RootFolder,

cast(

CASE WHEN (ChildFolders.FullPath = @SeparatorChar) THEN ''

ELSE ChildFolders.FullPath

END + @SeparatorChar + CHILD.foldername as varchar(max)

) as FullPath,

ChildFolders.Lvl + 1 as Lvl

from msdb.dbo.sysssispackagefolders CHILD

inner join ChildFolders

on ChildFolders.folderid = CHILD.parentfolderid

)

INSERT INTO #SSISPackagesList (

PackageRunningId,RootFolderName,ParentFolderFullPath,PackageOwner,

PackageName,PackageDescription,isEncrypted,PackageFormat4Version,

PackageType,CreationDate,PackageVersionMajor,PackageVersionMinor,

PackageVersionBuild,PackageVersionComments,

PackageSizeKb,PackageXmlContent

)

Select

CONVERT(NVARCHAR(50),P.id) As PackageId,

F.RootFolder,

F.FullPath,

SUSER_SNAME(ownersid) as PackageOwner,

P.name as PackageName,

P.[description] as PackageDescription,

P.isencrypted as isEncrypted,

CASE P.packageformat

WHEN 0 THEN '2005'

WHEN 1 THEN '2008'

ELSE 'N/A'

END AS PackageFormat,

CASE P.packagetype

WHEN 0 THEN 'Default Client'

WHEN 1 THEN 'SQL Server Import and Export Wizard'

WHEN 2 THEN 'DTS Designer in SQL Server 2000'

WHEN 3 THEN 'SQL Server Replication'

WHEN 5 THEN 'SSIS Designer'

WHEN 6 THEN 'Maintenance Plan Designer or Wizard'

ELSE 'Unknown'

END as PackageType,

P.createdate as CreationDate,

P.vermajor,

P.verminor,

P.verbuild,

P.vercomments,

DATALENGTH(P.packagedata) /1024 AS PackageSizeKb,

cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData

from ChildFolders F

inner join msdb.dbo.sysssispackages P

on P.folderid = F.folderid

order by F.FullPath asc, P.name asc

;

Acción 2: Extraer la lista de conexión definida y proveedores de conexión

Aquí está la parte de XSD que define los contenidos esperados del XML describiendo un paquete SSIS. Está tomado de 5.1 DTS XSD.

<xs:simpleType name="BasePropertyNameEnum">

<xs:restriction base="xs:string">

<xs:enumeration value="Description"/>

<xs:enumeration value="DTSID"/>

<xs:enumeration value="CreationName"/>

<xs:enumeration value="ObjectName"/>

</xs:restriction>

</xs:simpleType>

<xs:simpleType name="ConnectionManagerPropertyNameEnum">

<xs:union memberTypes="DTS:BasePropertyNameEnum">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:enumeration value="DelayValidation"/>

</xs:restriction>

</xs:simpleType>

</xs:union>

</xs:simpleType>

<xs:simpleType name="ConnectionManagerObjectDataPropertyNameEnum">

<xs:restriction base="xs:string">

<xs:enumeration value="Retain"/>

<xs:enumeration value="ConnectionString"/>

<xs:enumeration value="FileUsageType"/>

<xs:enumeration value="Format"/>

<xs:enumeration value="LocaleID"/>

<xs:enumeration value="Unicode"/>

<xs:enumeration value="HeaderRowsToSkip"/>

<xs:enumeration value="HeaderRowDelimiter"/>

<xs:enumeration value="ColumnNamesInFirstDataRow"/>

<xs:enumeration value="RowDelimiter"/>

<xs:enumeration value="DataRowsToSkip"/>

<xs:enumeration value="TextQualifier"/>

<xs:enumeration value="CodePage"/>

<xs:enumeration value="ServerName"/>

<xs:enumeration value="UseFile"/>

<xs:enumeration value="UseEncryption"/>

<xs:enumeration value="RetainData"/>

</xs:restriction>

</xs:simpleType>

Revisemos algunos campos que encuentro interesantes para investigación posterior.

Primero que todo, hay una propiedad Retain que usted puede establecer a “True” (verdadero) si usted desea compartir esta conexión a través de múltiples tareas en un paquete dado. Significa que si no está en “True”, SSIS creará una nueva conexión cada vez que tenga que usarla.

La propiedad ConnectionString es la más interesante, ya que es el descriptor real de la conexión.

Además, cuando editamos una nueva conexión, le decimos a SSIS que retrase la validación de esta conexión hasta el tiempo de ejecución. Entonces, usaremos la propiedad DelayValidation .

Finalmente, hay dos propiedades base, ObjectName y Description, que pueden ser útiles.

Esto lleva al diseño de esta tabla temporal:

CREATE TABLE #StagingPackageConnStrs (

PackageUniqifier BIGINT NOT NULL,

DelayValidation VARCHAR(100),

ObjectName VARCHAR(256),

ObjectDescription VARCHAR(4000),

Retain VARCHAR(100),

ConnectionString VARCHAR(MAX)

);

Y hay una sentencia T-SQL que podemos usar para poblar esta tabla, basados en el contenido de la tabla temporal previa #SSISPackagesList temporary table.

WITH XMLNAMESPACES (

'www.microsoft.com/SqlServer/Dts' AS pNS1,

'www.microsoft.com/SqlServer/Dts' AS DTS

) -- declare XML namespaces

INSERT INTO #StagingPackageConnStrs (

PackageUniqifier,

DelayValidation,

ObjectName,

ObjectDescription,

Retain,

ConnectionString

)

SELECT PackageUniqifier,

CASE

WHEN SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]', 'varchar(100)') = 0

THEN 'False'

WHEN SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]', 'varchar(100)') = -1

THEN 'True'

ELSE SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]', 'varchar(100)')

END AS DelayValidation,

SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',

'varchar(100)') AS ObjectName,

SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]',

'varchar(100)') AS ObjectDescription,

CASE

WHEN SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)') = 0 THEN 'True'

WHEN SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)') = -1 THEN 'False'

ELSE SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)')

END AS Retain,

SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') AS ConnectionString

FROM #SSISPackagesList PackageXML

CROSS APPLY

PackageXMLContent.nodes (

'/DTS:Executable/DTS:ConnectionManager'

) AS SSIS_XML(SSIS_XML)

;

Bueno, como puede ver arriba, para obtener datos acerca de las conexiones, usted usa CROSS APPLY con nodos XML del tipo ‘/DTS:Executable/DTS:ConnectionManager’.

No es difícil entender que usted hará la exacta misma cosa para extraer información acerca de cualquier configuración o variables de un paquete:

Para las configuraciones, los nodos serán de tipo ‘/DTS:Executable/DTS:Configuration’.
Para las variables, los nodos serán de tipo ‘/DTS:Executable/DTS:Configuration’.

Acción 3: Extraer la lista de trabajos de SQL Server Agent de tipo SSIS

Mientras que la acción previa es un poco más compleja, la siguiente es bastante directa. Consistemás o menos en una consulta a las tablas msdb.dbo.sysjobs y msdb.dbo.sysjobsteps. Mientras que el título de esta sección tienda a decir que vamos a construir una lista de trabajos SQL Agent, realmente construimos una lista de Pasos de Trabajos de SQL Agent. La primera lista es fácil de obtener de la segunda.

Como siempre, revisemos qué columnas compondrán nuestra tabla temporal objetivo.

Primero identificaremos el paquete SSIS correspondiente que es usado para un paso dado. Para hacer esto está el GUI del paquete para identificarlo. Sabemos que esto no es perfecto y podemos cambiar esto por un identificador generado en tiempo de ejecución.

También obtendremos de vuelta algunas informaciones generales acerca del trabajo:

Su identificador y nombre
Si el trabajo está habilitado o no
Si el trabajo ya ha sido corrido o no

Finalmente, a medida que obtenemos datos de los pasos y no sólo trabajos, recolectaremos el id del paso, el servidor objetivo y el texto entero del comando que compone el paso.

Usted encontrará a continuación la sentencia T-SQL para crear la tabla temporal que contendrá la lista de pasos de trabajos de SQL Agent que llaman a paquetes SSIS.

IF(OBJECT_ID('tempdb..#StagingPackageJobs') IS NOT NULL)

BEGIN

EXEC sp_executesql N'DROP TABLE #StagingPackageJobs';

END;

CREATE TABLE #StagingPackageJobs (

PackageUniqifier BIGINT NOT NULL,

JobId VARCHAR(128) NOT NULL,

JobName VARCHAR(256),

JobStep INT NOT NULL,

TargetServerName VARCHAR(512),

FullCommand VARCHAR(MAX),

isJobEnabled BIT,

hasJobAlreadyRun BIT

);

Y aún necesitamos una consulta para poblar esta tabla. Es lo que la siguiente consulta hace.

WITH PkgList

AS (

SELECT

PackageUniqifier,

CASE WHEN ParentFolderFullPath = '/' THEN '' ELSE ParentFolderFullPath END + '/' + PackageName as PackageFullPath

FROM #SSISPackagesList

JobSteps

AS (

SELECT CONVERT(VARCHAR(128),j.job_id) as JobId,

s.srvname as AgentServerName,

j.name as JobName,

js.step_id as JobStepId,

REPLACE(SUBSTRING(

SUBSTRING(REPLACE(js.command,'\"',''),CHARINDEX('/SQL "',REPLACE(js.command,'\"','')) + LEN('/SQL "'),LEN(REPLACE(js.command,'\"',''))-CHARINDEX('/SQL "',REPLACE(js.command,'\"',''))-LEN('/SQL "')),

CHARINDEX('"',SUBSTRING(REPLACE(js.command,'\"',''),CHARINDEX('/SQL "',REPLACE(js.command,'\"','')) + LEN('/SQL "'),LEN(REPLACE(js.command,'\"',''))-CHARINDEX('/SQL "',REPLACE(js.command,'\"',''))-LEN('/SQL "')))

),'\','/') as PackageFullPath,

LOWER(SUBSTRING(SUBSTRING(REPLACE(js.command,'\"',''),CHARINDEX('/SERVER "',REPLACE(js.command,'\"','')) + LEN('/SERVER "'),LEN(REPLACE(js.command,'\"',''))-CHARINDEX('/SERVER "',REPLACE(js.command,'\"',''))-LEN('/SERVER "')),

CHARINDEX('"',SUBSTRING(REPLACE(js.command,'\"',''),CHARINDEX('/SERVER "',REPLACE(js.command,'\"','')) + LEN('/SERVER "'),LEN(REPLACE(js.command,'\"',''))-CHARINDEX('/SERVER "',REPLACE(js.command,'\"',''))-LEN('/SERVER "')))

)) as TargetServerName,

js.command as FullCommand,

CASE WHEN j.enabled = 1 THEN 1 ELSE 0 END as isJobEnabled,

CASE WHEN js.last_run_date IS NULL THEN 0 ELSE 1 END as hasJobAlreadyRun

FROM msdb.dbo.sysjobs j

JOIN msdb.dbo.sysjobsteps js

ON js.job_id = j.job_id

JOIN master.dbo.sysservers s

ON s.srvid = j.originating_server_id

--filter only the job steps which are executing SSIS packages

WHERE subsystem = 'SSIS'

)

INSERT INTO #StagingPackageJobs (

PackageUniqifier,JobId,JobName,JobStep,

TargetServerName,FullCommand,isJobEnabled,hasJobAlreadyRun

)

SELECT

p.PackageUniqifier,

s.JobId,

s.JobName,

s.JobStepId,

s.TargetServerName,

s.FullCommand,

s.isJobEnabled,

s.hasJobAlreadyRun

FROM PkgList p

INNER JOIN JobSteps s

ON p.PackageFullPath = s.PackageFullPath

;

Resumen

Hemos visto que un Paquete SSIS está compuesto de múltiples componentes y propiedades que pueden ser extraídos usando diferentes fuentes y técnicas, especialmente la columna XML llamada packagedataen la tabla msdb.dbo.sysssispackages y XQuery.

Las consultas usadas en este artículo están agrupadas juntas en un archivo SQL que puede descargar aquí.

Qué sigue

Extrajimos algo de información. Podemos tomar esta información como entrada de un proceso de análisis para determinar con precisión las desviaciones de los estándares empresariales.

Jefferson Elias

Viviendo en Bélgica, obtuve un grado de maestría en Ciencias de la Computación en 2001 en la Universidad de Lieja.

Soy uno de los raros tipos que empezó a trabajar como DBA inmediatamente después de su graduación. Así que, trabajo en el hospital de la universidad de Lieja desde 2011. Inicialmente involucrado en la administración de Bases de Datos Oracle (las cuales aún están bajo mi cargo), tuve la oportunidad de aprender y manejar instancias de SQL Server en 2013. Desde 2013, he aprendido mucho acerca de SQL Server en administración y desarrollo.

Me gusta el trabajo de DBA porque uno necesita tener un conocimiento general en cada campo de IT. Esa es la razón por la que no pararé de aprender (y compartir) los productos de mis aprendizajes.

Ver todas las entradas de Jefferson Elias

Últimas entradas de Jefferson Elias (ver todo)

¿Cuáles son los interbloqueos de SQL Server y cómo ellos se pueden monitorear? - November 6, 2019
Cómo bajar o eliminar el inicio de sesión de SQL Server y todas sus dependencias - October 14, 2019
Cómo recuperar información acerca de los paquetes SSIS almacenados en la Base de Datos MSDB - May 28, 2018

Introducción

Paquetes SSIS: lo que necesitamos saber

¿Qué recolectar?

¿Cómo recolectaremos datos?

Acción 1: Extraer la lista de paquetes SSIS

Acción 2: Extraer la lista de conexión definida y proveedores de conexión

Acción 3: Extraer la lista de trabajos de SQL Server Agent de tipo SSIS

Resumen

Qué sigue

Referencia

Related posts: