Ejecución de paquetes SSIS por código

En todo proyecto, puede surgir la necesidad de lanzar paquetes SSIS al vuelo mediante .NET. Este post está centrado en mostrar una forma de hacerlo utilizando las librerias de administración de SSIS y en dar una idea de al menos, como podeis empezar.

Ejecutar manualmente

Recordemos que para lanzar un paquete SSIS podeis utilizar el SQL Server Management Studio de la siguiente forma:

1. Conectar al servidor de SSIS (por ejemplo yo-pc\sql2008r2):

NOTA: No es posible utilizar las herramientas de SQL Server Express para conectar a Integration Services, hay que utilizar la versión Developer o Enterprise

2. Ir a Stored Packages->MSDB->DTS (la carpeta DTS ha sido creada manualmente por mi para el ejemplo)

3. Con botón derecho en el paquete SSIS a ejecutar, si lanzar “Run Package”:

Este es el formulario de ejecución donde poder dar valor a variables. Para terminar ejecutando, click en “execute”.

Por código Visual Basic.NET

El proposito de la entrada de este post es ejecutar código mediante código .NET. Para ello existen dos opciones para realizar ejecuciones de paquetes SSIS mediante código, la primera consiste en utilizar las clases de la librería Microsoft.SqlServer.ManagedDTS.dll y la otra utilizar la aplicación enominada dtexec que viene con las herramientas de inteligencia de negocio de SQL Server.

En ambos casos, hay que instalar como mínimo las herramientas cliente de inteligencia de negocio como vemos en la imagen (no es necesario instalar ningun sevicio de SQL Server en los clientes por tanto, ni las herramientas de administración):

NOTA: La imagen corresponde a instalación de SQL Server express con herramientas avanzadas

Para obtener tanto la herramienta, como las dll, en este caso si que nos vale por tanto utilizar la versión express con herramientas cliente avanzadas: http://www.microsoft.com/downloads/details.aspx?familyid=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=es

 

Ejecución mediante código Visual Basic

Una vez tenemos las librerías instaladas en el equipo desde donde queramos lanzar los paquetes SSIS, podemos utilizar código como el siguiente para efectuar ejecuciones de los mismos:

A continuación vemos como sería el código en Visual Basic.NET para ejecutar un Paquete SSIS.

NOTA: Si la aplicación no es .NET (por ejemplo, si es VB6) hay que programar un wrapper de acceso a la libreria ManagedDTS mencionada anteriormente (el código de más abajo es VB.NET). Más adelante, se da opción de utilizar dtexec, si no se quiere implementar dicho wrapper

Los paquetes deben estar desplegados en el servicio de Integration Services y además según se puede ver en el ejemplo de código, haberse desplegado sobre la carpeta DTS (no es requisito, pero para el ejemplo se ha realizado de esta forma, para ir alineados con las imágenes anteriores también).

Imports DTS = Microsoft.SqlServer.Dts.Runtime

Module Module1

    Sub Main()

        Dim instance As DTS.Application

        Dim packagePath As String

        Dim serverName As String

        Dim serverUserName As String

        Dim serverPassword As String

        Dim events As DTS.IDTSEvents

        Dim returnValue As DTS.Package

        Dim executionResult As DTS.DTSExecResult

        instance = New DTS.Application()

        packagePath = "\DTS\__TuDTSVaAqui__"

        serverName = "__TuServidorVaAqui__"

        serverUserName = "solidq" 'Nombre de usuario

        serverPassword = "solidq" 'Password de usuario

        events = Nothing

        returnValue = instance.LoadFromSqlServer(packagePath, serverName, serverUserName, serverPassword, Nothing)
 ‘Para asignar propiedades a variables

  ‘pkg.Variables("VarName").Value = "Value"

        executionResult = returnValue.Execute()

        If executionResult = DTS.DTSExecResult.Success Then

            Console.WriteLine("Paquete ejecutado correctamente")

        Else

            If executionResult = DTS.DTSExecResult.Failure Then

                Console.WriteLine("Se produjo un error al ejecutar el paquete")

            End If

        End If

        Console.ReadKey()

    End Sub

End Module

NOTA: El usuario que ejecute debe tener permisos en la Base de datos MSDB, puesto que es necesario listar las carpetas acceder a la carpeta DTS y cargar el paquete.

 
Ejecución mediante línea de commandos dtexec

Este método es el más sencillo y consiste en lanzar el paquete utilizando la aplicación dtexec, destinada especialment para ello.

Se parte de la base nuevamente en que se ha instalado la herramienta en el cliente y por tanto se encuentra instalada y accessible (por defecto se encuentra en C:\Program Files\Microsoft SQL Server\100\DTS\Binn\)

Se trataria por tanto de realizar una llamada desde visual basic a la aplicación dtexec con los parámetros necesarios (ver imagen adjunta como ejemplo sencillo):

Por ejemplo, para lanzar el paquete llamado “paquetePrueba” que se encuentra en el servidor “yo-pc\sql2008r2”, utilizando un usuario de sql “usuariosql” y password “passwordusuario”, asignando valor a la variable llamada “miVariable”, podríamos crear una llamada como esta:

Dtexec /ser yo-pc\sql2008r2 /U usuariosql /P passwordusuario /sq paquetePrueba /set \package.variable[miVariable].Value;AquiPonesElValorQueQuieresAsignar

NOTA: Para información sobre los parámetros de entrada podemos utiliza dtexec /? O diréctamente dirigirnos a la web de consulta de dtexec aqui: http://technet.microsoft.com/en-us/library/ms162810(SQL.100).aspx

Que lo disfruteis.

Detectar usuarios huérfanos en BBDD

En caso de que querais buscar usuarios huérfanos en BBDD (que no tengan login asociado), como recordais podeis utilizar el procedimiento almacenado sp_change_users_login.

Pues bien, he aqui un buen tip para detectar todos los usuarios huérfanos de todas las BBDD que tengas con una simple consulta:

sp_msforeachdb 'use ?;

declare @mitabla as table (dbname sysname default db_name(),uname sysname, usid varbinary(max))

insert into @mitabla(uname,usid)

EXEC sp_change_users_login ''Report''

select * from @mitabla'

Ahora bien, si estais en un caso como el mio en que tengo que revisar más de 150 BBDD…quizás te resulte esto mas util

DECLARE cursorBBDD CURSOR read_only fast_forward forward_only FOR

   select name from sys.databases where database_id>4



declare @nombreBBDD sysname

create table #mitabla (dbname sysname default db_name(),uname sysname, usid varbinary(max))



OPEN cursorBBDD

FETCH NEXT FROM cursorBBDD

INTO @nombreBBDD

WHILE @@FETCH_STATUS = 0

BEGIN

   declare @sentenciaSQL nvarchar(max)

   set @sentenciaSQL = 'use ' + @nombreBBDD + ';

                     insert into #mitabla(uname,usid)

                     EXEC sp_change_users_login ''Report'''

   --print @sentenciaSQL

   exec(@sentenciaSQL)



   FETCH NEXT FROM cursorBBDD

   into @nombreBBDD

END

CLOSE cursorBBDD

DEALLOCATE cursorBBDD



select * from #mitabla

drop table #mitabla

Suerte y que no detecteis muchos! 

Macro evento 24h de PASS-Latam en castellano y portugues!!

Tengo el honor de ser uno de los speakers del primer macro evento 24h de SQL PASS LATAM que se celebrará los próximos dias 19 y 20 de Octubre de 2010

¿Qué es el evento 24h de PASS-LATAM?

Es un evento de 24h de charlas en vivo mediante livemeeting, de registro completamente gratuito y con 12 sesiones al dia (2 dias por tanto) a razón de 24 horadores reconocidos a nivel internacional. Speakers de España, Portugal, México, Puerto Rico, USA, Costa Rica, Venezuela, Colombia, Brasil y Perú.

Los horarios en la web de registro se encuentran publicados en CDT (-6UTC).

En mi caso, la temática de mi charla tiene por título “Mejores prácticas: Optimiza desde abajo mejorando el rendimiento de tus consultas” y recuerda que el horario mostrado en la web de registro es –6UTC por lo que el horario en españa de mi charla serán las 15h (8am de México).

Para más información, entra en http://sqlpass-latam.org/Inicio.aspx o http://www.sqlpass-latam.org/24horas.aspx y si quieres ir al grano, regístrate directamente en la siguiente URL: https://www323.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=jbkn3wqp1pt4w06b

Buscar componentes en DTSX rápidamente

Un buen truco para revisar si en alguno de nuestros paquetes DTSX de integration services hacemos uso del componente obsoleto DTS 2000 es lanzar la siguiente consulta PowerShell.

get-childitem *.dtsx | select-string -pattern "Execute DTS 2000 Package Task" | gci -Name

Importar datos de excel 2007 ó 2010

Aviso a navegantes intrépidos que usen entornos SQL Server 2008 y/o 2008 R2. Cuando quereis importar datos desde SSMS usando el import wizard y el origen es un excel creado con la versión 2007 o 2010, acordaros de instalar los “2007 Office System Driver: Data Connectivity Components”. Sobre todo si os da un error que dice esto:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
machine.

Porque aunque el excel haya sido creado con Excel 2010 y lo tengais instalado…lo que os está pidiendo son los componentes de conectividad de Office 2007 (sino diria Microsoft.ACE.OLEDB.14.0)

Lo podeis encontrar aqui: http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en 

Salu2!

Detectar consultas con referencias a bases de datos distintas a la actual

Un problema habitual en todo proyecto de migración, escalabilidad o rearquitectura de aplicaciones y/o servidores de bases de datos es la necesidad de conocer la interdependencia de bases de datos.

Los que me conocen, saben que yo apuesto siempre por molestar al cliente al mínimo y eso implica que aunque suelo pedir información relativa a la arquitectura actual, dependencias, etc, etc,…siempre acabo verificando por mi cuenta la realidad (parafraseando a house: “El paciente siempre miente, aunque no lo sepa” )

Bien, si tenemos que pelearnos con un problema como el que comento (que por ejemplo hayan aplicaciones conectadas a la BBDD X, que lancen queries del estilo “Select * from Y.dbo.tabla_en_bbdd_y”), hay una solución bien sencilla, pero que obviamente tarda su tiempecito

Consiste en usar trazas de profiler para capturar la actividad contra la BBDD. Una vez tenemos esta actividad, nos crearemos una BBDD o tabla en la que incorporaremos información relativa a database_id, databaseName y servername (se sobreentiende que esto se hace porque además del ejemplo que estoy exponiendo, hay que hacer algo parecido para referencias entre instancias, pero se sale del ámbito del post).

Una vez tenemos dicha tabla creada y rellenada (a la que llamaremos dbo.Databases), lo siguiente que tenemos que hacer por tanto es, a grandes rasgos:

1. Obtener los SMTP: BatchCompleted events
2. Obtener el databaseid de los mismos, junto a su TextData NOT NULL e información relevante como hostname, loginname,…y lo que queramos para posteriormente buscar datos en ellos
3. Filtrar conexiones a bases de datos tipo pubs, adventureworks y northwind
4. Por último, obtener aquel texto, en el que se haga referencia a cualquier databasename seguido por “.” y que además no sea el mismo databasename al que estaba la conexión atacando (referencia externa por tanto)

Obviamente pueden haber falsos positivos (igual, tenemos mala suerte y nos sale algun comentario donde se haga referencia,…), pero habrá positivos de haberlos.

No olvideis crear los índices adecuados en la tabla dbo.Databases para que la cosa no vaya lentísima.

Aqui os dejo el código para explorar los resultados:

--
-- ECB: Meta query to detect queries that references databases
--
-- 
declare @ServerName sysname = 'servidor_sql'
declare @trcFile nvarchar(max) = 'path_to_trc_file.trc'
   
      
      
;with subselect as(            
select 
       dbs.dbid,
       dbs.DBName,
       textdata,
       applicationName,
       hostname,
       loginname,
       starttime              
from ::fn_trace_gettable(@trcFile,default) trc
 left join dbo.DataBases dbs on trc.DatabaseID =  dbs.DBId 
                                and dbs.ServerName = @ServerName
                                and dbs.dbid > 4  AND dbs.ServerName not in ('pubs','Northwind','Adventureworks')         
where trc.textdata IS NOT NULL AND trc.EventClass = 12
)
select 
       trc.dbname as db_connected ,       
       dbs.dbName as db_referenced,
       trc.applicationname,
       trc.hostname,
       trc.loginname,
       trc.starttime,
       cast(trc.textdata as nvarchar(1000)) as [definition]
from subselect trc, dbo.DataBases dbs
where dbs.servername = @ServerName 
    and dbs.DBId <> trc.DBId
    and (trc.TextData LIKE '%'+dbs.dbname+'.%'
           or textdata LIKE '%'+dbs.dbname+'].%'
           )  

Aqui veis el plan de ejecución, que siempre me gusta ver

Por último solo me queda comentar que esto mismo hay que hacerlo con los objetos de BBDD (procedimientos almacenados,funciones,…) y con servidores vinculados y aperturas tipo openrowset y similares.

Salu2!

Restaurar multiples bases de datos a la vez

En ocasiones hace falta realizar restauraciones masivas de ficheros de backup. Situaciones como resolver una catástrofe o realizar pruebas de migración, pueden requerirnos restaurar 40, 100 bases de datos…y aqui es donde entra este script.

Para el proyecto en el que estoy ahora involucrado, estoy realizando pruebas que involucran directamente restauraciones de más de 50 BBDD y claro…los informáticos no nos caracterizamos por nuesta pasión a las tareas repetitivas, asique…¿mejor que lo haga otro, no?. Pues bien, ese otro será nuestro SQL Server

Los parámetros de inicio del siguiente script son:

1. dbs: Cursor con los nombres de ficheros de backup a restaurar (si se llama mibbdd.bak, pues mibbdd)
2. pathFisicoMDF: Ruta donde acabarán los ficheros de datos
3. pathFIsicoLDF: Ruta donde acabarán los ficheros de log
4. pathToBak: Ruta donde buscar los ficheros de backup .bak

Solo una cosa mas,

/*
  Enrique Catala Bañuls
*/
declare @path nvarchar(max)
DECLARE @dbName sysname
DECLARE @logicalName sysname
declare @physicalName nvarchar(max)
declare @fileName nvarchar(max)
declare @sSql nvarchar(max)
declare @fileType char(1)
declare @pathToBak nvarchar(max)
declare @pathFisicoMDF nvarchar(max)
declare @pathFisicoLDF nvarchar(max)

/*********************************************************************************************/
/*********************************************************************************************/
--
--                    FILL HERE DATA NEEDED TO THE RESTORNG PROCESS
--
DECLARE dbs CURSOR READ_ONLY
    -- bases de datos restaurándose
FOR select 'bdeap'


    
select @pathFisicoMDF = N'e:\datos\',
        @pathFisicoLDF = N'd:\logs\',
        @pathToBak = 'E:\fulldiaria\'
/*********************************************************************************************/
/*********************************************************************************************/

create table #tmp
(
LogicalName sysname
,PhysicalName nvarchar(max) 
,Type char(1) 
,FileGroupName sysname  NULL
,Size numeric(20,0) 
,MaxSize numeric(20,0),
Fileid tinyint,
CreateLSN numeric(25,0),
DropLSN numeric(25, 0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlocSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEthumbprint varbinary(32) NULL
)

OPEN dbs
FETCH NEXT FROM dbs INTO  @dbName
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN  
      
        SELECT @sSql = '',  
               @path = 'restore filelistonly from disk = N'''+@pathToBak+@dbName+'.bak'''
        
        --PRINT @path
        
        insert #tmp        
        EXEC (@path)

        --select * from #tmp

        DECLARE dbs2 CURSOR READ_ONLY            
        FOR select logicalName,physicalName,type from #tmp
        OPEN dbs2

        FETCH NEXT FROM dbs2 INTO  @logicalName,@physicalName,@fileType
        WHILE (@@fetch_status <> -1)
        BEGIN
            IF (@@fetch_status <> -2)
            BEGIN        
            set @fileName= substring(@physicalName,
                 2+len(@physicalName)-charindex('\',reverse(@physicalName)),                 
                 charindex('\',reverse(@physicalName)))
                 
            if @fileType = 'D'
                set @sSql = @sSql+' MOVE N'''+@logicalName+''' TO N'''+@pathFisicoMDF+@fileName+''','
            else
                set @sSql = @sSql + ' MOVE N'''+@logicalName+''' TO N'''+@pathFisicoLDF+@fileName+''','
            
            --print @logicalName
            --print @pathFisicoMDF
            --print @sSql
            
            END
            FETCH NEXT FROM dbs2 INTO  @logicalName,@physicalName,@fileType
        END

        CLOSE dbs2
        DEALLOCATE dbs2
        
        --print @dbName
        --print @pathToBak
        --print @sSql
        --SET @sSql = 'RESTORE DATABASE ['+@dbName+'] FROM  DISK = N'''+ @pathToBak +'\' +@dbName + '.bak'' 
        SET @sSql = 'RESTORE DATABASE ['+@dbName+'] FROM  DISK = N'''+ @pathToBak +'\' +@dbName + '.bak'' 
                     WITH  FILE = 1, '
                     +@sSql+ 
                     ' RECOVERY, NOUNLOAD,  REPLACE,  STATS = 10'
        --print @sSql
        exec(@sSql)
            
        truncate table #tmp
    END
    FETCH NEXT FROM dbs INTO  @dbName
END

CLOSE dbs
DEALLOCATE dbs
GO
drop table #tmp