postgresql: cómo obtener datos entre diferentes base de datos

Fuente: http://stackoverflow.com/questions/6083132/postgresql-insert-into-select

Fuente: http://roy-rc.blogspot.com/2010/08/postgresql-dblink-conexion-entre-bases.html

A veces es necesario poder copiar datos entre diferentes bases de datos, que incluso pueden residir en diferentes servidores. Postgresql proporciona una librería interesante para poder realizar dicho cometido.

DBlink consta de un conjunto de funciones diseñadas para realizar conexiones entre bases de datos Postgres, en el mismo server o en otros.. lo que se necesita es instalar DBlink en el servidor que hace la peticion..

Para implementar esto debes instalar el paquete contrib de la version que usas de postres

?

1	# aptitude install postgresql-contrib-8.3

el contrib de postgres provee una serie de funciones muy utiles para desarrolladores y administradores.

Para instalar la funcion en tu BD debes ser el usuario postgres y por lineas de comando:

?

1 2	$ cd /usr/share/postgresql/8.3/contrib $ psql test_db u_test -h localhost < dblink.sql

Te conectas a la BD que tiene instalado el DBlink y realizas la consulta

?

1	$ psql test_db u_test -h localhost

La idea es trear datos de la BD my_db que esta en el servidor 191.168.50.90 y mostrarlos en la conexion establecida en test_db en localhost

?

1 2 3

test_db=# select * from dblink ('dbname=my_db hostaddr=191.168.50.90 user=u_test password=123456 port=5432', 'select id,descripcion from tabla')  as t1(id int4,descripcion text);

Por ejemplo:

En la bbdd1 creamos una tabla:

psql dbtest CREATE TABLE tblB (id serial, time integer);

INSERT INTO tblB (time) VALUES (5000), (2000);

 

En la bbd2, creamo otra tabla, y la alimentaremos desde la tabla que reside en otra instancia:

psql postgres CREATE TABLE tblA (id serial, time integer);

INSERT INTO tblA     SELECT id, time      FROM dblink(‘dbname=dbtest’, ‘SELECT id, time FROM tblB’)     AS t(id integer, time integer)     WHERE time > 1000;

illatà !

 

como horizontalizar en sql (SQL Transpose Rows as Columns)

Fuente: http://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns

Fuente: http://roy-rc.blogspot.com/2010/07/postgresql-crosstab-filas-columnas-en.html

Exportar un Excel a MS Sql Server

Fuente: http://www.netveloper.com/2008/01/importar-exportar-datos-con-sql-server-2005/
Para realizar estas operaciones SQL Server dispone de una herramienta en forma de asistente que nos guiará paso a paso. En el ejemplo importaremos una hoja de Excel a una tabla de SQL Server pero podríamos hacerlo al revés o importar tablas de Access.

Para realizar estas operaciones nos ponemos encima de la base de datos con la que queremos trabajar, en el ejemplo utilizaremos la base de datos AdventureWorks que viene con el SQL Server 2005 y con el botón derecho seleccionamos “Tareas” y del siguiente menú seleccionamos “Importar Datos” (pulsar encima de la imagenes para verlas completas)

La siguiente ventana es de información, pulsamos siguiente. Aparece la ventana para elegir el origen de los datos, en nuestro ejemplo seleccionamos Excel y elegimos un fichero de datos de Excel para importar

En la siguiente ventana elegimos el destino de los datos que vamos a importar, en el ejemplo utilizamos el propio SQL Server y la base de datos AdventureWorks

La siguiente ventana nos pide la forma que utilizaremos para extraer los datos, importaremos una o varias las tablas o vistas o utilizaremos una consulta determinada

Elegimos la primera opción y en la siguiente ventana nos aparece el listado de hojas de Excel disponibles para importar

Seleccionamos la única hoja de Excel que tenemos en el ejemplo y pulsamos siguiente donde nos pedirá qué queremos hacer con este paquete si ejecutarlo solo o ejecutarlo y guardarlo como un SSIS (antiguamente en SQL Server 2000 los DTS)

Después nos mostrará un resumen de todo el proceso

Y lo ejecutamos

Y con esto ya tenemos importada la hoja d Excel.

Saludos

Equivalencias Oracle – MS SQL Server

 Fuente: http://www.sql-server-helper.com/faq/dates-p01.aspx

Para insertar desde una select:

INSERT INTO yourTable (f1, f2, f3)
SELECT f1, f2, f3
FROM yourTable
WHERE … some condition ….

Para crear una tabla a partir de una select:

SELECT *
INTO employees
FROM all_emp

Para los que venimos de Oracle, éstas son algunas equivalencias interesantes:

// //

1.  How can I get the current system date and time?

To get the current system date and time in SQL Server, you will use the GETDATE() date function. SELECT GETDATE() AS [CurrentDateTime] GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.  Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

2.  What’s the equivalent of Oracle’s SYSDATE in SQL Server?

The equivalent of Oracle’s SYSDATE in SQL Server is the GETDATE() date function. SELECT GETDATE() AS [SYSDATE] Similar to Oracle’s SYSDATE, GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.  Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

3.  How do I add or subtract days in a date?

There are 2 ways to add or subtract a certain number of days from a date.  The first option is to simply add or subtract the number of days you want using the addition (+) or subtraction (-) mathematical operators.  Here’s an example on how to do it: SELECT GETDATE() - 7 AS [SevenDaysAgo] SELECT GETDATE() + 3 AS [ThreeDaysFromToday] The other way is to use the DATEADD date function.  The DATEADD date function returns a new datetime value based on adding an interval to the specified date. SELECT DATEADD(DD, -7, GETDATE())AS [SevenDaysAgo] SELECT DATEADD(DD, 3, GETDATE()) AS [ThreeDaysFromToday] The syntax of the DATEADD date function is as follows: DATEADD ( datepart , number, date ) datepart is the parameter that specifies on which part of the date to return a new value.  For days, you can use either DD, D, or DAY.  number is the value used to increment datepart.  date is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Regardless of which of these two methods is used, both of them will return a datetime value that includes the time part.

4.  How can I extract the day, month and year parts of a DateTime column?

There are a couple of ways of extracting either the day, month or year parts from a datetime column.  The first method is the use of the DAY, MONTH and YEAR date functions.  The DAY date function returns an integer representing the day datepart of the specified date.  The MONTH date function returns an integer that represents the month part of a specified date.  Lastly, the YEAR date function returns an integer that represents the year part of a specified date. The syntax of these date functions are as follows: DAY ( date ) MONTH ( date ) YEAR ( date ) In all three date functions, the date parameter is an expression returning a datetime or smalldatetime value, or a character string in a date format.  Use the datetime data type only for dates after January 1, 1753.  Here’s an example of how to use it to get the month, day and year of the current system date: SELECT MONTH(GETDATE()) AS [CurrentMonth], DAY(GETDATE()) AS [CurrentDay], YEAR(GETDATE()) AS [CurrentYear] The second method of extracting the day, month or year from a datetime data type is with the use of the DATEPART date function.  The DATEPART date function returns an integer representing the specified datepart of the specified date. The syntax of the DATEPART date function is as follows: DATEPART ( datepart , date ) The datepart is the parameter that specifies the part of the date to return.  For the day, you can specify either DAY, DD, or D.  For the month, you can specify either MONTH, MM, or M.  Lastly, for the year, you can specify either YEAR, YYYY, or YY.  The date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.  Use the datetime data type only for dates after January 1, 1753. Here’s an example of how to use the DATEPART date function to extract the month, day and year from the current system date: SELECT DATEPART(MONTH, GETDATE()) AS [CurrentMonth], DATEPART(DAY, GETDATE()) AS [CurrentDay], DATEPART(YEAR, GETDATE()) AS [CurrentYear] Choosing between the two methods, it is preferred to use the MONTH, DAY and YEAR date functions over the DATEPART date function simply because of simplicity of use.  The three functions take only 1 parameter while DATEPART takes 2.

5.  How can I get the date part only of a DateTime field similar to Oracle’s TRUNC function?

In SQL Server, there is no single function that is the equivalent of Oracle’s TRUNC function, which can be used to get just the date part of a datetime data type.  But there are different ways of getting just the date part of a datetime data type and this is discussed on the following link:  Get Date Only User-Defined Function

6.  How can I get just the time part of a DateTime data type?

To get just the time part of a DateTime data type, the CONVERT function can be used.  The CONVERT function explicitly converts an expression of one data type to another.  The syntax of the CONVERT function is as follows: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type.  length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types.  Lastly, style is the style of date format used to convert datetime or smalldatetime to character data. To get the time part of a datetime data type, the style to use is 108, which will format the datetime expression to HH:MI:SS format. SELECT CONVERT(VARCHAR(10), GETDATE(), 108) AS [CurrentTime] If you want to include the millisecond (HH:MI:SS.MMM), the style to use is 114: SELECT CONVERT(VARCHAR(13), GETDATE(), 114) AS [CurrentTime] In both cases, the returned time part is of varchar data type.  If you want to return the time part as a datetime data type, a CAST needs to be performed: SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 108) AS DATETIME) AS [CurrentTime] SELECT CAST(CONVERT(VARCHAR(13), GETDATE(), 114) AS DATETIME) AS [CurrentTime] Casting the time part to a datetime data type will include a date part with a value of 01/01/1900 in the result.  Here’s an example: CurrentTime ------------------------- 1900-01-01 09:30:25.000

7.  How can I display the date in MM/DD/YYYY format?

To display a date in the MM/DD/YYYY format, you will be using the CONVERT function.  The syntax of the CONVERT function is as follows: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type.  length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types.  Lastly, style is the style of date format used to convert datetime or smalldatetime to character data. To display the date in the MM/DD/YYYY format, the value to be passed to the style parameter will be 101: SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] If today is December 25, 2000, the output of this will be: MM/DD/YYYY ------------ 12/25/2000 It should be noted that the output is now of VARCHAR data type and not DATETIME data type.

8.  How can I get the first day of the month?

There’s no built-in function in SQL Server that will return the first day of the month for any given date.  However it can easily be determined using the different date and mathematical functions that already exist in SQL Server.  The basic concept is simply replacing the day part of the given date with 1.  The following link discusses a few ways of getting the first day of the month: Get First Day of the Month User-Defined Function

9.  How can I get the difference in days between two dates?

To get the difference in days between two dates, you will be using the DATEDIFF date function.  The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows: DATEDIFF ( datepart , startdate , enddate ) datepart is the parameter that specifies on which part of the date to calculate the difference.  Since we are looking for the difference in days, we will be passing a value of DAY, DD or D in this parameter.  startdate is the beginning date for the calculation and it’s an expression that returns a datetime or smalldatetime value, or a character string in a date format.  enddate is the ending date for the calculation and it’s an expression that returns a datetime or smalldatetime value, or a character string in a date format. To get the number of days between the Fourth of July and Christmas day: DECLARE @FourthOfJuly DATETIME DECLARE @Christmas DATETIME SET @FourthOfJuly = '2005/07/04' SET @Christmas = '2005/12/25' SELECT DATEDIFF(DAY, @FourthOfJuly, @Christmas) AS [DateDifference] The output of this script is 174 days.

10.  What’s the difference between smalldatetime and datetime and when do I use each?

A datetime data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).  Values are rounded to increments of .000, .003, or .007 seconds On the other hand, a smalldatetime data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.  smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers.  The first 4 bytes store the number of days before or after the base date, January 1, 1900.  The base date is the system reference date.  Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. The smalldatetime data type stores dates and times of day with less precision than datetime.  SQL Server stores smalldatetime values as two 2-byte integers.  The first 2 bytes store the number of days after January 1, 1900.  The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime is usually used when you don’t need to store the time of the day such as in cases of effectivity dates and expiration dates.  datetime  is used if the time of the day is needed and up to the second accuracy is required.

Related Topics: User-Defined Function – Get Date Only User-Defined Function – Get First Day of the Month Tips and Tricks – Date Formats

More Frequently Asked Questions – About Dates

SQL Server 2005 Remote Connectivity Issue TroubleShoot

Fuente: http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote connection against SQL 2k5, plus, if running SQL Server on XP/WIN2K3/VISTA behind firewall, such problem occurs more frequently and harder for customer to get clue of behind reason.

I. Background of remote connectivity issue.

The reason we saw the issue comes out w/ SQL 2k5 instead of SQL 2k is because two breaking change in SQL 2k5 compared to SQL 2000.

1) Network Protocols Off by Default: In order to make system secure, we limit connectivity when a user install SQL 2k5, thereby reducing surface area for attack. By default, SQL server (SQLEXPR, SQLDEV and EVAL SKUs), on installation will listen only on Shared memory and local-only Named Pipe. TCP and remote Named Pipe will be off-by-default. VIA will also be off-by-default; SQL server (SQLENT, STANDARD and Workgroup SKUs), on installation will listen only on Shared memory, local-only Named Pipe and TCP. Remote Named Pipe will be off-by-default. VIA will also be off-by-default.

Therefore, when you mae remote connection, you should make sure at least either Named Pipe or TCP are enabled in your remote SQL instance.

2) SQL Browser service: which is a replacement of SSRP system in SQL Server 2000, run as a Windows Service on installation of SQL 2k5. Upon startup, SQL Server Browser starts and claims UDP port 1434. When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance.

Therefore, you need to make sure SQL Browser is enabled and started when remote client ask for which tcp port or pipe name SQL Server is listening on. If your remote SQL Instance is a default instance, you do not necessary enable sql browser since client would always try default TCP port 1433 and pipe name \\<remoterserver>\pipe\sql\query. But, if you have SQL 2k5 named instance installed or SQL 2000 and SQL 2k5 side by side installed, you must enable and start sqlbrowser.

II. Problem list:

By understanding background 1) and 2), I belive you can imagine issues you may face when make remote connection against SQL 2k5:

1) Fail to connect over TCP/IP or Named Pipe if  the request protocol was not enabled.

2) Fail to connect over TCP/IP or Named Pipe if Firewall enabled on the remote server and tcp port or “File and Printer Sharing”  is not added to the Firewall exception list.

3) Fail to connect to remote sql named instance if SQL Browser was not enabled or UDP port 1434 that browser listening on is added to the Firewall exception list.

III. Troubleshooting Tips:

Assumption: your SQL Server was installed on remote server and behind firewall; SQL Instance was started; and  your client app specify correct remote sql instance name.

On your server side:

[1] Enable remote named pipe or tcp: All programs | Microsoft SQL Server 2005| Configuration Tools | SQL Server Surface Area Configuration | Configuration for Services and Connections | Remote Connections, choose either enable TCP or Named Pipe or both.

[2] Sql Instance was restarted successfully, check Server ErrorLog, find which tcp port or pipe name server is listening on.

[3] netstat -ano | findstr <portnumber> if server enable TCP, and make sure server is listening on the correct port.

[4] go to services.msc, find service “SQL Server Browser”, enable it and restarted, also, go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active.

[5] Enable “Fire and Printer Sharing” in Firewall exception list.

[6] Add TCP port or sqlservr.exe to Firewall exception list, either add “..\Binn\sqlsevr.exe” or add port.

If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.

[7] Add Sql Browser service to Firewall exception list, you can either add program ” C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe” or add UDP port 1434.

[8] Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \\.\pipe\sql\query.

On your Client Side:

[1] ping <remote server> return correct IP address of your remote server.

[2] telnet <remoteserver> <portnumber> works, <portnumber> is the port that your remote sql instance is listening on.

     telnet < ipaddress> <port> works

[3] \\<remoteserver>, make sure you can access share of remote server.

[4] Go to SQL Server Configuration Manager | SQL Native Client Configuration | Client Protocols, make sure Named pipe or tcp is enabled; Click properties of Client Protocols, make Sure you see at least Np or tcp is in enabled protocols, plus, recommend put TCP on the top of the order.

[5] If you are using MDAC ( Driver={SQL Server} or SQLOLEDB.x) in your client application, in command line, tyep “cliconfg.exe”, also enable NP and TCP and put TCP on top of order.

[6] Use osql or sqlcmd to try ” osql /S<remoteserver> /E” or “osql /S<remoteserver>\<Instancename> /E” see whether connection works.

[7] Check your client connection string syntax: a. do you specify correct remote server name? b. do you specify correct instance name? if remote sql is default instance, you just need to specify remote server machine name, otherwise, you need to specify ” <remoteserver>\<instancename>” c. do you  spcify tcp port or pipe name in your connection string, if so, double check the port and pipe name are the one server is listening on, otherwise, remove it.

[8] If your client app connect to remote server using server alias name, a. if you are using MDAC, try “cliconfg.exe”, in “Alias” Tab, check whether you specify any alias in which the tcp port or the pipe name should be the one remote server is listening on; b. if you are using SQL Native Client, go to SQL Server Configuration Manager, check “Aliases” configuration.

If you are sure the network protocol configuration for remote connection are all correct by going through above checklist, and still face specific problem, pleas refer following blog that give troubleshooting tips based on concrete error message:

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

MING LU

SQLServer: compactar el tamaño de los ficheros _log

A veces, cuando una base de datos tiene un índice alto de operaciones de actualizaciones y/o borrado, el tamaño del fichero _LOG crece de forma exponencial.

Un remedio “casero” es el siguiente:

use master

go
drop procedure sp_ReducirTamLog
drop procedure sp_ReducirTamLogBD

go
create procedure sp_ReducirTamLogBD
  @Bd   varchar(50)   — base de datos
AS
            checkpoint                                                                              — punto de control
            exec(‘backup log ‘ + @Bd + ‘ with truncate_only’)                        — truncado del log
            exec(‘dbcc shrinkdatabase (‘ + @Bd + ‘,truncateonly)’)                — reduccion de todos los ficheros de db’s
go

create procedure sp_ReducirTamLog as
declare @bbdd varchar(250)

— todas las bbdd
declare cbbdd cursor local fast_forward for
  select name from master..sysdatabases where lower(name) like ’BBDD%’ or lower(name) like ’BBDD1%’ or lower(name) like ‘BBDD2%’
            open                 cbbdd
                        fetch next from cbbdd into @bbdd
            while @@FETCH_STATUS=0    begin
                        exec sp_ReducirTamLogBD @bbdd
                        fetch next from cbbdd into @bbdd
            end
            close      cbbdd
            deallocate cbbdd

go

grant all privileges on sp_ReducirTamLog to public
grant all privileges on sp_ReducirTamLogBD to public

go

exec sp_ReducirTamLog

 Sólo recordar que el permiso ALL es obsoleto y sólo se mantiene con fines de compatibilidad. NO implica los permisos ALL definidos en la entidad.

SQL Server Integration Services (SSIS) FAQ y planificación

Fuente: http://www.guillesql.es/Articulos/SQLServerIntegrationServices_SSIS_FAQ.aspx

Fuente: http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/1ed22887-91a9-43bf-8b0f-dbb6de728d86

Recientemente hemos tenido que realizar la replicación automática de determinadas tablas entre dos bases de datos diferentes. El procedimiento seguido ha seguido el más abajo.   También se detalla un FAQ sobre SSIS de MS SQL Server.

Para planificar jobs de SSIS:

Lo primero es asegurarte que el servidor de Integration Services existe. Luego te conectas con el Management Studio (es como conectarse a un servidor de bases de datos, pero eligiendo uno de Integration Services) para registrar los dtsx en él (este paso no es obligatorio, luego lo explico). En la carpeta Stored Packages, das botón derecho + Import Package. Rellenas el formulario que aparece con los datos del dtsx y lo almacenas en el servidor. 

Para ejecutarlo, crea un job. Te conectas al servidor de bases de datos (puede ser el mismo u otro), creas un job y un paso en él. En la propiedades del paso tienes una lista desplegable donde elegir qué hará ese paso, una de las opciones es ejecutar un paquete de Integration Services. Eliges el que has registrado, en función de cómo lo hayas hecho, deberás elegir una ruta (en ese caso no precisas haber registrado el dtsx en el servidor), un repositorio o un servidor. Y programas el job para que se ejecute a la hora elegida.

FAQ de SSIS:

La desaparición de los antiguos Paquetes DTS por la nueva ETL de Microsoft (los Paquetes DTSX), SQL Server Integration Services (SSIS), ha sido uno de los cambios más importantes en Microsoft SQL Server 2005, y dónde adquirimos una ventaja competitiva frente a otros fabricantes de base de datos. En este Artículo, intentaremos resolver las preguntas más frecuentes sobre Microsoft SQL Server Integration Services (SSIS) para aquellos que se inician… y por supuesto, en Castellano !! (ante todo que se entienda… jeje

Este Artículo es de utilidad para el examen 70-445 y el examen 70-446 de las certificaciones MCTS y MCITP de Business Intelligence.

• Manejar datos de Texto en los Flujos de Datos (Data Flows) de SQL Server Integration Services (SSIS)
• ¿Cómo se puede en un Flujo de Datos (Data Flow) de SSIS insertar sólo las filas nuevas?
• ¿Es posible alterar el esquema de una base de datos utilizada por un paquete de SSIS?
• ¿Dónde guardar los ficheros DTSX de los paquetes de SSIS 2005?
• ¿Cuántas instancias del servicio Integration Services de SSIS 2005 pueden existir en una única máquina?
• ¿Es posible instalar SSIS 2005 en Microsoft Cluster (MSCS)?
• ¿Es posible integrar ActiveX Scripts (ej: VBScript) en Paquetes DTSX de SSIS?
• ¿Es posible integrar Scripts con .Net Famework en Paquetes DTSX de SSIS?
• ¿De qué formas es posible ejecutar un Paquete DTSX de SSIS?
• ¿Por qué al intentar ejecutar un Paquete DTSX de SSIS se produce el error de Error Loading y el Paquete DTSX no se ejecuta? ¿Para qué sirve la propiedad ProtectionLevel?
• ¿Es posible especificar a un Paquete DTSX distintas configuraciones, en función de en qué entorno o servidor se ejecute? ¿Cómo funcionan los Ficheros de Configuraciones?
• ¿Para qué sirven las Variables y Expresiones en SSIS? ¿Cómo se utilizan?
• ¿Qué posibilidades de Registro (Logging) tienen los Paquetes DTSX de SSIS? ¿Cómo se utiliza?
• ¿Para qué sirven las Transacciones en SSIS? ¿Cómo trabajar con Transacciones en SSIS?
• ¿Es posible ejecutar un Paquete DTSX en modo 32-bit sobre una máquina 64-bit (arquitectura x64)? ¿Y si no tenemos Proveedores OLEDB o .Net de 64-bit? ¿Qué ventaja tiene ésto?
• ¿Qué permisos son necesarios para que los Programadores puedan guardar sus Paquetes DTSX en MSDB? ¿Qué permisos son necesarios para poder crear, editar y ejecutar un JOB del Agente de SQL Server para sus Paquetes DTSX?
• ¿Por qué no puedo ejecutar un Paquete DTSX en Business Intelligence Development Studio (BIDS)? ¿Por qué está deshabilitado el botón Iniciar Depuración (Start Debugging) y el botón Iniciar sin Depurar (Start without Debugging)?

 

Manejar datos de Texto en los Flujos de Datos (Data Flows) de SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) funciona como una pequeña base de datos, y como tal, tiene sus propios tipos de datos. Estos tipos de datos, se infieren a su vez de los tipos de datos existentes en los orígenes y destinos de datos (ficheros Microsoft Excel, bases de datos Microsoft SQL Server, etc.). Es muy importante saber si trabajamos con datos de tamaño fijo o con datos de tamaño variable. Esto es debido a que muchas tareas, como por ejemplo Merge, Merge Join, Lookup, etc., al realizar comparaciones entre dos valores, pueden producirse resultados no deseados. Así, si estamos leyendo texto de Microsoft Excel, SSIS tomará por defecto valores UNICODE de hasta 250 caracteres de longitud, pero al hacer un Merge Join con un campo NCHAR(10) de Microsoft SQL Server podríamos obtener un resultado no deseado por los caracteres blancos existentes a la derecha (sería necesario hacer un TRIM, por ejemplo con una tarea Derived Column). En esta situación, no se tomaría como iguales los valores del JOIN, y podríamos pensar que existe algún error o bug. No, simplemente, así es la naturaleza de los datos de texto de tamaño fijo y de tamaño variable.

¿Cómo se puede en un Flujo de Datos (Data Flow) de SSIS insertar sólo las filas nuevas?

Esta es una situación muy típica al realizar procesos de carga con SSIS que se ejecutan periódicamente. Por ejemplo, si estamos cargando la facturación de una empresa, queremos que sólo se inserten los nuevos clientes, pues al intentar insertar un cliente existente se produciría un error de violación de clave.

Principalmente, tenemos de formas de afrontar este problema con SSIS.

• Utilizando la tarea Merge Join. Esta tarea permite realizar un Join entre dos Flujos de datos de SSIS. A diferencia de la tarea Merge, Merge Join permite realizar no sólo un INNER JOIN, sino también un LEFT OUTER JOIN o un FULL OUTER JOIN.La tarea Merge Join, y al igual que la tarea Merge, sólo tiene dos entradas (es posible utilizar varias tareas Merge o Merge Join en cascada). Es requisito que sus entradas estén ordenadas, para lo cual, se suele utiliza tareas Sort.

  En el caso que nos ocupa, deberemos utilizar un LEFT OUTER JOIN. Seguidamente, utilizaremos una tarea Conditional Split, que nos permita definir una salida con sólo los nuevos clientes, siendo esta salida la que conectaremos con nuestro destino de datos. La condición que utilizaremos en la nueva salida será algo como “ISNULL(ClienteID)”.

  Finalmente, el Flujo de Datos (Data Flow), quedará como se muestra en la siguiente imagen.

  
• Utilizando la tarea Lookup. Esta tarea permite hacer una búsqueda (Lookup) sobre otra tabla u origen de datos, permitiendo devolver un valor asociado. Un ejemplo de caso de uso típico es el siguiente: si estamos cargando en SQL Server un fichero de empleados en el que viene el código de la categoría profesional del empleado, pero que no incluye el nombre o descripción de dicha categoría, se podría utilizar la tarea Lookup para hacer una búsqueda sobre la tabla de Categorías, y obtener de la misma la descripción de la categoría para cada empleado.En el ejemplo de las Facturas, al cargar la tabla de Clientes, podemos utilizar una tarea Lookup para obtener de la tabla Clientes de nuestro Data Warehouse, el código del mismo. De este modo, si obtenemos como código NULL, será porque el cliente es nuevo !!.

  Aquí existe un problema: la tarea Lookup por defecto, sólo funciona si para cada fila encuentra otra fila en la tabla de búsqueda. En caso contrario se produce el error “Row yielded no match during lookup”. Por lo tanto, no podríamos utilizar la tarea Lookup para nuestros fines.

  Sin embargo, es posible alterar la configuración de la salida de error de la tarea Lookup. Para ello, editar la tarea Lookup (click con el botón derecho sobre la tarea Lookup, y después click Edit). En el diálogo Lookup Transformation Editor, click sobre el botón Configure Error Output. A continuación, en el diálogo Configure Error Output, modificar su configuración como se muestra en la siguiente imagen de ejemplo.

  

  Realizado esto, es necesario utilizar una tarea Conditional Split, que nos permita definir una salida con sólo los nuevos clientes, siendo esta salida la que conectaremos con nuestro destino de datos. La condición que utilizaremos en la nueva salida será algo como “ISNULL(ClienteID)”.

  

Como conclusión, podemos comentar lo siguiente:

• Utilizando la tarea Merge Join. Es la manera natural de resolver el problema que nos ocupa. Aunque el desarrollo del Data Flow se hace más complejo, se da un uso apropiado a las tareas utilizadas.
• Utilizando la tarea Lookup. Resulta más fácil y rápido el desarrollo del Data Flow. Sin embargo, implica realizar un uso no apropiado de la tarea configuración de la salida de error.

¿Es posible alterar el esquema de una base de datos utilizada por un paquete de SSIS?

En principio, si es posible, pero debemos tener en cuenta que es probable encontrarnos con diferentes errores, tanto en tiempo de ejecución como en tiempo de diseño. Esto depende principalmente de que tipo de modificación de esquema estemos realizando, ya que el propio Paquete DTSX almacena metadatos sobre las fuentes de datos (orígenes y destinos) y sobre las distintas tareas utilizadas en cada Flujos de Datos (Data Flow).

La recomendación, es intentar mantener los Paquetes DTSX actualizados con sus Metadatos congruentes con el esquema de las bases de datos subyacentes, es decir, si alteramos el esquema de la base de datos (por ejemplo, por necesidad de una aplicación que no tiene nada que ver con SSIS), editar los Paquetes DTSX para corregir las advertencias y errores que puedan haberse generado por el cambio. Si no podemos permitirnos este trabajo, al menos tendremos que hacerlo así para aquellos cambios que impliquen errores en tiempo de ejecución de los Paquetes DTSX.

Por ejemplo, si tenemos un paquete DTSX de SSIS funcionando, y agregamos un campo a uno de sus orígenes de datos, el paquete DTSX seguirá funcionando correctamente. Sin embargo, si posteriormente editamos dicho Paquete DTSX, obtendremos una advertencia en tiempo de diseño (The external metadata column collection is out of synchronization with the data source columns. The column “MiColumna” needs to be added to the external metadata column collection.), como se muestra en la siguiente imagen:

Esta situación se soluciona fácilmente, editando dicho origen de datos (ej: click con el botón derecho sobre el origen de datos, y después click Edit), y sobre la página Columns desmarcamos o marcamos las nuevas columnas, en función de si deseamos incluirlas en el Flujo de Datos (Data Flow). Finalmente, click OK para cerrar el diálogo editor del origen de datos.

Sin embargo, en otras ocasiones nos podemos encontrar con el siguiente error:

Error at Nombre_del_DataFlow [DTS.Pipeline]: The index is not valid. ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)

En concreto, este error también se produce al intentar editar un origen de datos en un Flujo de Datos (Data Flow) de SSIS después de haber agregado columnas a la tabla subyacente, mostrándose el siguiente diálogo:

Para solucionarlo, click OK sobre dicho diálogo. Después, volvemos a editar el origen de datos, seleccionamos la pestaña Columns, deseleccionamos la nueva Columna, y click OK para cerrar el diálogo editor del origen de datos.

Otros cambios, como por ejemplo alterar una columna VARCHAR a INT en un destino de datos, puede provocar un error en tiempo de ejecución, y adicionalmente, al intentar modificar el correspondiente paquete DTSX, nos encontraremos con la advertencia producida por la falta de sincronización entre el esquema de la base de datos y los metadatos del paquete DTSX de SSIS.

¿Dónde guardar los ficheros DTSX de los paquetes de SSIS 2005?

SSIS ofrece dos posibles almacenes dónde guardar nuestros Paquetes DTSX de SSIS 2005 (Package Store y SQL Server), pudiendo adicionalmente utilizar el propio sistema de ficheros para almacenar Paquetes DTSX:

• File System. Es la única opción posible durante el desarrollo. Da igual que se trate de una unidad local del equipo utilizado para el desarrollo, o que sea una unidad de red.
  Una gran ventaja es que Business Intelligence Development Studio (BIDS) se puede integrar con Source Safe, y así disponer de un control de versiones en nuestro proyecto de Integration Services, que además facilita compartir el código con distintos miembros de un equipo de trabajo.
  Una desventaja, es que el servicio de Integration Services sólo es capaz de gestionar los paquetes almacenados en SQL Server (MSDB) o en el Package Store, como acontinuación se explica.
  Por último, aunque hemos dicho que BIDS sólo trabaja con Paquetes DTSX almacenados en el File System, también es cierto que BIDS nos permite importar y exportar Paquetes DTSX. Para exportar un Paquete DTSX utilizaremos la opción Guardar Copia de Paquete Como (Save Copy of Package As). Esta opción la podemos encontrar en BIDS, una vez que hemos abierto el Paquete DTSX deseado, desde el menu Archivo (File). Del mismo modo, para importar un Paquete DTSX desde BIDS, desde la ventana Explorador de Soluciones (Solution Explorer), nos posicionaremos sobre el nodo Paquetes SSIS (SSIS Packages), click con el botón derecho sobre dicho nodo, y click sobre la opción Agregar Paquete Existente (Add Existing Package).
• Package Store (También es File System, pero gestionado por SSIS, no una ruta cualquiera). Este almacén de paquetes DTSX si es gestionado por el servicio de Integration Services. Siempre que importemos un paquete a esta ubicación, se depositará por defecto en C:\Program Files\Microsoft SQL Server\90\DTS\Packages, salvo que la instalación de SSIS se haya realizado sobre una ruta distinta de la de por defecto. Es posible crear una jerarquía de subcarpetas, sobre las cuales importar nuestros paquetes DTSX. En cualquier caso, como parte de nuestro Plan de Contingencias, deberemos contemplar hacer Backups del contenido completo de esta carpeta del sistema de ficheros.
• SQL Server (en MSDB, también se podría considerar como Package Store). Este almacén de paquetes DTSX si es gestionado por el servicio de Integration Services. Consiste en guardar los paquetes DTSX dentro de la tabla sysdtspackages90 de la base de datos MSDB. Una ventaja de utilizar MSDB, es el hecho de poder realizar los Backups y Restores junto con el resto de bases de datos de SQL Server, y delegar el problema de la encryptación de los datos sensibles de los Paquetes DTSX (ej: las contraseñas de las conexiones) a SQL Server, en vez de encriptar con User Key o con Password. Es recomendable establecer el valor de la propiedad ProtectionLevel a ServerStorage. Adicionalmente, existen varios roles de base de datos que permiten conceder distintos niveles de privilegio.

Como vemos, para el servicio Integration Services sólo podemos elegir entre las opciones Package Store y SQL Server (MSDB). Por el contrario, con BIDS sólo podemos trabajar sobre File System.

Es posible Importar y Exportar paquetes DTSX entre el sistema de ficheros (File System) del entorno de desarrollo, y otros sistema de ficheros (File System), el Package Store o SQL Server (MSDB), utilizando el comando de consola dtutil.exe. La utilidad dtutil.exe permite copiar, mover, borrar o verificar Paquetes DTSX, así como crear subcarpetas, eliminarlas, etc. Muchas de las tareas que se pueden realizar con dtutil.exe, también se pueden realizar desde SQL Server Management Studio (SSMS).

De forma adicional a la utilización de dtutil, también podemos utilizar la utilidad de implementación (deployment utility) para de forma gráfica (mediante un asistente) publicar nuestros Paquetes DTSX en SQL Server o en el File System. Para poder utilizar esta utilidad, en las propiedades del Proyecto de SSIS, establecer el valor de la propiedad CreateDeploymentUtility a True y establecer la carpeta en que se desea generar la utilidad de implementación (esto es la propiedad DeploymentOutputPath). Al generar nuestro proyecto, se creará en la carpeta especificada una copia de los Paquetes DTSX y un fichero de Manifiesto. Para ejecutar la utilidad de implementación, ejecutar (doble-click) el fichero de Manifiesto, y el asistente de implementación se ejecutará.

Es posible cambiar la Instancia de SQL Server (base de datos MSDB) utilizada por SSIS 2005 (por defecto se utiliza la instancia por defecto), una situación típica en caso de tener en un mismo servidor múltiples instancias de SQL Server. Del mismo modo, también es posible cambiar la ruta del Package Store utilizada por SSIS 2005, por ejemplo, si queremos utilizar un disco diferente del empleado en la instalación de producto. Para realizar estas configuraciones es necesario modificar el fichero de configuración de SSIS 2005 (MsDtsSrvr.ini.xml), que por defecto es C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml.

También es posible agregar más ubicaciones raíz de almacenamiento en SSIS 2005 (ya sean Package Store o SQL Server), de tal modo, que además de tener las dos por defecto, podamos añadir otras ubicaciones (ya sean de tipo SQL Server – MSDB – o de tipo Pacage Store – File System). Esto también se realiza modificando el fichero de configuración de SSIS 2005 (MsDtsSrvr.ini.xml), agregando tags de tipo Folder por dentro del tag TopLevelFolder, como se muestra en el siguiente ejemplo.

<?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>File System Alternativo</Name>          <StorePath>..\Packages_Alternativo</StorePath>       </Folder>    </TopLevelFolders> </DtsServiceConfiguration>

Una ventaja de esta configuración, es permitir utilizar múltiples Instancias de SQL Server a SSIS 2005 (o múltiples File Systems) para simular múltiples entornos (ej: Desarrollo, Pruebas Integradas, Pre-producción, etc.), pudiendo realizar la operativa de paso entre entornos mediante la exportación e importación de paquetes DTSX entre los distintos almacenes.

¿Cuántas instancias del servicio Integration Services de SSIS 2005 pueden existir en una única máquina?

A diferencia del motor de base de datos, que permite instalar múltiples instancias de SQL Server en una misma máquina, con SSIS 2005 sólo es posible tener una única instancia del servicio Integration Services en una misma máquina.

Si deseamos disponer de múltiples instancias de SSIS 2005, tenemos las siguientes alternativas:

• Utilizar múltiples máquinas, y en cada máquina instalar una instancia de SSIS 2005.
• Utilizar una única máquina, pero utilizar múltiples ubicaciones raíz para el almacenamiento de paquetes (Package Store). Podemos realizar los pasos entre entornos exportando e importando los paquetes DTSX entre los distintos almacenamientos de paquetes (Package Store). Para crear múltiples ubicaciones raíz de almacenamiento de paquetes, es necesario modificar el fichero de configuración de SSIS: MsDtsSrvr.ini.xml.
• Utilizar una estrategia mixta. Por ejemplo, una máquina para Desarrollo, Pruebas y Pre-Producción, con una instancia de SSIS 2005 y múltiples almacenamientos raíz de paquetes, y otra máquina con una instancia de SSIS 2005 para Producción.

¿Es posible instalar SSIS 2005 en Microsoft Cluster (MSCS)?

SSIS 2005 no es una aplicación Cluster-aware, es decir, no está preparada por instalarse y configurarse en un Microsoft Cluster (MSCS) y ofrecer alta disponibilidad.

A continuación, se incluye una dirección de TechNet en la que se explica el procedimiento a seguir, si deseamos construir manualmente una instalación de SSIS 2005 en Cluster, algo que aunque es posible, no es una recomendación de Microsoft.

Configuring Integration Services in a Clustered Environment

Antes de iniciar la una configuración de SSIS en Microsoft Cluster (MSCS), leer detenidamente el contenido de la anterior dirección.

¿Es posible integrar ActiveX Scripts (ej: VBScript) en Paquetes DTSX de SSIS?

Dentro del Control Flow de un Paquete DTSX, podemos utilizar una tarea de tipo ActiveX Script Task, en la cual debemos establecer las siguientes propiedades:

• Propiedad Language. Elegir el lenguaje empleado en nuestro Script (ej: VBScript).
• Propiedad Script. Deberemos escribir el Script que deseamos ejecutar. Es muy importante que el código aquí incluido se deberá codificar en funciones, es decir, que no debemos escribir el código tal cual, sino escribir una función principal (ej: Function Main()), y dentro de la misma el código que deseamos ejecutar.
• Propiedad EntryMethod. Esta propiedad debe contener el nombre de la función principal (ej: Si la función principal es Function Main(), estableceremos el valor de esta propiedad a Main) que hemos utilizado en nuestro código.

Habitualmente, resulta muy útil poder acceder a variables del Paquete DTSX desde el ActiveX Script, para lo cual, utilizaremos la colección DTSGlobalVariables, pudiendo especificar qué variable deseamos acceder, como por ejemplo DTSGlobalVariables(“MiVariable”).Value. De este modo, podemos leer el contenido de una variable del Paquete DTSX, o bien establecer un valor a dicha variable.

A continuación se muestra un ejemplo didáctico, de un Script en Visual Basic, capaz de mostrar en una ventana de diálogo el contenido de la variable MiVariable (suponemos que existe dicha variable):

Function Main()    MsgBox(DTSGlobalVariables(“MiVariable”).Value) End Function

Un problema habitual al utilizar VBScript en Paquetes DTSX de SSIS, es que al escribir o generar código VB (ej: Visual Basic 6) ó VBA (ej: Macro Microsoft Excel XP), este código aprovechando las referencias de su proyecto a las librerías correspondientes, tiene visibilidad de las constantes que se utilizan por los métodos en las clases. Sin embargo, en un fichero VBScript (o en una tarea ActiveX Script Task) es necesario establecer los valores literales y nos las constantes, pues éstas últimas no podrán ser resueltas y el código no funcionará.

En cualquier caso, la recomendación es utilizar la tarea Script Task (Scripting utilizando .Net Framework), ya que además de la ventaja de la riqueza propio de .Net Framework, se dispone de un diseñado gráfico para escribir el código (algo que no tiene la tarea ActiveX Script Task, en la cual el código lo escribimos en una enorme y simple caja de texto) dónde disfrutaremos de IntelliSense, del Object Browser, etc.

¿Es posible integrar Scripts con .Net Famework en Paquetes DTSX de SSIS?

La respuesta es SI. Más aún, el método natural de agregar un Script a un Paquete DTSX es utilizando una tarea Script Task, he incluyendo en la misma código .Net Framework. Del mismo modo que era muy habitual utilizar VBScript (o similares como JScript) en los Paquete DTS de SQL Server 2000, con SSIS lo natural es utilizar .Net Framework en tareas Script Tasks (aunque podamos utilizar tareas ActiveX Script Task para incluir código VBScript). Con ésto, podemos aprovechar el diseñador gráfico para escribir código, con IntelliSense, el Object Browser, la riqueza de .Net Framework, etc.

Dentro del Control Flow de un Paquete DTSX podemos utilizar una tarea de tipo Script Task, en la cual deberemos especificar habitualmente las siguientes propiedades:

• ReadOnlyVariables y ReadWriteVariables. Para poder acceder a variables de nuestro Paquete DTSX, deberemos especificarlas en estas propiedades (en una o en otra propiedad, en función del tipo de acceso que deseemos realizar), separadas por comas. Si no lo hacemos así obtendremos el error “The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there“.
• El botón DesignScript. A través de éste botón, podemos acceder al editor de código en el cual editar y escribir el código .Net Framework para nuestra tarea.

Algo muy habitual dentro de un Paquete DTSX es acceder a las variables del paquete. Además de utilizar las propiedades ReadOnlyVariables y ReadWriteVariables para especificar a qué variables deseamos acceder, desde nuestro código podemos utilizar la colección Dts.Variables para acceder a las variables del Paquete DTSX, como por ejemplo Dts.Variables(“MiVariable”).Value.

También puede resultar interesante lanzar o forzar un error (raise error), por ejemplo ejecutando Dts.Events.FireError(0, “Comprobando Fecha de la Excel y Fecha del Nombre del Fichero”, “Las fechas no coinciden”, “”, 0).

¿De qué formas es posible ejecutar un Paquete DTSX de SSIS?

Existen distintas formas de ejecutar un Paquete DTSX de SSIS, cada una de las cuales puede presentar ciertas ventajas o inconvenientes según en el caso particular en que la deseemos aplicar.

• Ejecutar un Paquete DTSX desde Business Intelligence Development Studio (BIDS). Desde BIDS podemos crear nuestros Proyectos de SSIS, que estarán formados por nuestros Paquetes DTSX. Estos Paquetes DTSX se almacenarán en el File System, y NO se almacenarán ni en SQL Server (MSDB) ni en el Package Store. Como mucho, podremos integrar BIDS con Visual Source Safe, o en todo caso, desplegar o publicar los Paquetes DTSX del Proyecto en un Package Store, en un servidor SQL Server, o en otro File System. La principal ventaja de BIDS, es que al tratarse de la herramienta de desarrollo de SSIS, podemos ver de forma gráfica la ejecución del Paquete DTSX, incrustar Lectores de Datos para ver los datos que se mueven por nuestros Flujos de Datos (Data Flows), etc. Es muy cómodo, pero sólo se trata de una herramienta de desarrollo y se limita a la ejecución de Paquetes DTSX almacenados en el File System.
• Ejecutar un Paquete DTSX con la utilidad dtexec. La utilidad dtexec es un comando de consola (es decir, a lo MS-DOS) que permite ejecutar un paquete, independiente de que esté almacenado en el File System, en SQL Server, o en el Package Store. En caso de necesidad, podemos plantearnos la posibilidad de ejecutar dtexec desde el procedimiento almacenado del sistema xp_cmdshell, con lo cual, podríamos ejecutar un Paquete DTSX desde un simple Procedimiento Almacenado o desde un bloque e código Transact-SQL (recordar que por defecto, xp_cmdshell viene desactivado por seguridad, y deberá ser activado con sp_confire o Surface Area Configuration Tool).
  Cabe la posibilidad de ejecutar dtexec desde una tarea programada de Windows, desde otra herramienta utilizada para planificación de tareas, o bien, también se puede invocar manualmente (ejecutando un fichero BAT) o desde otra aplicación o proceso.
  Podríamos ver a dtexec como la herramienta que sustituye a la utilidad dtsrun, que se utilizaba para el mismo propósito con SQL Server 2000.
  Entre sus muchas posibilidades, está poder establecer valores a las variables del Paquete DTSX, actualizar las cadenas de conexión utilizadas por los Connection Managers del Paquetes, especificar ficheros de configuración, la contraseña de decriptación si el Paquete DTSX fué guardado cifrando la información sensible, y un largo etc. Es decir, todas las opciones que se le pueden indicar a un Paquete DTSX para su ejecución, se le pueden indicar utilizando dtexec.
  La realidad, es que la utilidad dtexec junto a la utilidad dtutil, hacen un equipo perfecto !!, pues esta última permite gestionar SSIS (mover Paquetes, copiarlos, borrarlos, etc.).
  Cabe destacar, que en una máquina de arquitectura x64, la instalación de SSIS instalará dos versiones de dtsexec: la de 32-bit y la de 64-bit.
  Requiere instalar SSIS en la máquina en la que se desea utilizar dtexec.
• Ejecutar un Paquete DTSX con la utilidad dtexecui. La utilidad dtexecui es una interfaz gráfica, que permite ejecutar un paquete independiente de que esté almacenado en el File System, en SQL Server, o en el Package Store. Su principal ventaja es que permite de forma sencilla especificar todas las opciones necesarias para ejecutar un Paquete DTSX, ofreciendo todas las opciones de dtexec pero de forma gráfica, incluso permite generar la línea de comandos para dtsexec desde las opciones que tengamos especificadas (vamos, que nos sirve de chuleta).
• Ejecutar un Paquete DTSX desde un Job del Agente de SQL Server. Desde un JOB del Agente de SQL Server, podemos añadir un paso del tipo SQL Server Integration Services Package, y en las propiedades de dicho paso, podemos especificar todas las opciones necesarias para ejecutar el Paquete DTSX, del mismo modo que lo haríamos con dtexecui. Sin embargo, el Agente de SQL Server nos permitirá planificar la ejecución de nuestro Paquete DTSX, y disfrutar de todas las ventajas que ofrece el Agente de SQL Server (Alertas, Operadores, etc.). Permite ejecutar un paquete independiente de que esté almacenado en el File System, en SQL Server, o en el Package Store.
  Requiere instalar SSIS en la máquina SQL Server en la que se desea ejecutar el JOB..
  Se debe garantizar que el usuario utilizado en las Credenciales (Credentials) de la cuenta Proxy empleada en el paso del JOB, tiene los suficientes permisos (ej: acceso al sistema de ficheros, conexiones de base de datos, etc.) para la ejecución del Paquete DTSX.
• Ejecutar un Paquete DTSX desde Programación. Es posible desde una aplicación .Net Framework (Windows Formas, ASP.Net, XML Web Service, etc.), utilizar el modelo de objetos de SSIS (SSIS Object Model), y con unas pocas líneas de código lograr nuestro objetivo. Necesitaremos utilizar las clases de Microsoft.SqlServer.Dts.Runtime, para poder cargar el Paquete DTSX (método LoadPackage) desde dónde se encuentra almacenado (File System, Package Store o SQL Server) y después ejecutarlo (método Execute). Puede encontrarse más información y ejemplos en los Libros en Pantalla (BOL – Books On Line). Requiere de un desarrollo .Net Framework 2.0, y además requiere instalar SSIS en la máquina en la que se desean ejecutar los Paquetes DTSX.
  De forma alternativa, desde una aplicación .Net Framework sería posible ejecutar la utilidad dtexec.exe, y así también lograr nuestro objetivo. Otro truquillo sería ejecutar un Job del Agente de SQL Server que a su vez ejecute el Paquete DTSX que deseamos, pudiendo ejecutar dicho JOB desde código Transact, o a través del modelo de objetos de SQL Server (SMO – SQL Server Management Objects).

Como conclusión, tenemos varias maneras de ejecutar nuestros Paquetes DTSX, y para gustos, hay colores. Yo personalmente desarrollo con BIDS (evidentemente) y prefiero ejecutar los Paquetes DTSX con el Agente de SQL Server y dejarlos almacenados en SQL Server (es decir, en MSDB).

En cualquier caso, debemos tener en cuenta que al desarrollar en un equipo y mover el Paquete DTSX desarrollado a otro equipo (o incluso almacenarlo en SQL Server), podemos encontrarnos con problemas relativos a seguridad, en particular, al valor de la propiedad ProtectionLevel. Quizás, sea este el principal detalle a tener en cuenta al elegir con qué método ejecutar nuestros Paquetes DTSX, pues nos podemos encontrar que nuestros Paquetes DTSX no se ejecuten y se produzca el siguiente error:

Error loading PackageName: Failed to decrypt protected XML node “PackagePassword” with error 0x8009000B “Key not valid for use in specified state.”

You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

¿Por qué al intentar ejecutar un Paquete DTSX de SSIS se produce el error de Error Loading y el Paquete DTSX no se ejecuta? ¿Para qué sirve la propiedad ProtectionLevel?

Un error muy habitual al empezar a trabajar con los Paquetes DTSX de SSIS, es que después de haber desarrollado nuestro proceso de ETL, y tras haberlo probado con éxito una y otra vez desde Business Intelligence Development Studio, al ejecutar el Paquete DTSX desde un entorno distino (Pruebas Integradas, Producción, etc.), y en consecuencia en otra máquina y/o con otro usuario, se produce el siguiente error y el Paquete DTSX no se ejecuta:

Error loading PackageName: Failed to decrypt protected XML node “PackagePassword” with error 0x8009000B “Key not valid for use in specified state.”

You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

Habitualmente esto es causa del valor de la propiedad ProtectionLevel del Paquete DTSX. Esta propiedad está concebida para proteger la información sensible del Paquete DTSX, siendo un ejemplo habitual de información sensible la contraseña de una conexión, por motivos evidentes de seguridad. Los valores posibles son:

• Do not save sensitive (DontSaveSensitive). Esta opción implica que no se guardará la información sensible. Si volvermos a abrir el Paquete DTSX desde BIDS, deberemos volver a especificar el valor de los datos sensibles.
• Encrypt all with password (EncryptAllWithPassword). Esta opción implica que se encripta el Paquete DTSX completo, utilizando para la encriptación, un clave especificada por el usuario, como si se tratase de una contraseña. Si volvermos a abrir el Paquete DTSX desde BIDS o si queremos ejecutarlo (ej:con dtexec), deberemos especificar la password para poder recuperar el Paquete DTSX.
• Encrypt all with user key (EncryptAllWithUserKey). Esta opción implica que se encripta el paquete completo, utilizando para la encriptación, un clave basada en el perfil de usuario. Sólo el mismo usuario utilizando el mismo perfil, puede volver a cargar el Paquete DTSX.
• Encrypt sensitive with password (EncryptSensitiveWithPassword). Similar a EncryptAllWithPassword, pero en este caso, sólo se encripta la información sensible del Paquete DTSX. Utiliza DPAPI.
• Encrypt sensitive with user key (EncryptSensitiveWithUserKey). Similar a EncryptAllWithUserKey, pero en este caso, sólo se encripta la información sensible del Paquete DTSX. Utiliza DPAPI.
• Rely on server storage for encryption (ServerStorage). Protege el Paquete DTSX completo mediante la utilización de los roles de base de datos de MSDB (db_dtsoperator, db_dtsadmin, db_dtsltduser). Esta opción no está soportada desde BIDS guardando el Paquete DTSX en el File System.

Por defecto, al crear un nuevo Paquete DTSX se utiliza el valor EncryptSensitiveWithUserKey, por lo cual, sólo podremos abrir o ejecutar el Paquete DTSX utilizando el mismo usuario y ordenador. Esta es la razón principal del error comentado (la primera en la frente . Por lo tanto:

• Si deseamos abrir o ejecutar el Paquete DTSX en otro ordenador o con otro usuario, podemos soluciar este problema utilizando un valor de ProtectionLevel que utilice encriptación por Password (ej: EncryptSensitiveWithPassword), facilitando la contraseña para poder abrir o ejecutar el Paquete DTSX. Como alternativa, podemos utilizar el valor DontSaveSensitive y utilizar un fichero de configuración para almacenar la información sensible.
• Si deseamo almacenar el Paquete DTSX en SQL Server, la recomendación es utilizar para la propiedad ProtectionLevel el valor ServerStorage, para lo cual resulta muy útil utilizar la utilidad de implementación (deployment utility), que nos permitirá de forma gráfica (mediante un asistente) publicar nuestros Paquetes DTSX en SQL Server y especificar que utilice para la propiedad ProtectionLevel el valor ServerStorage (esto se consigue activando la opción “Rely on server storage for encryption” en el asistente). Como alternativa, también podemos utilizar el valor DontSaveSensitive y utilizar un fichero de configuración para almacenar la información sensible.

Como conclusión, es recomendable tomar como costumbre utilizar la opción EncryptSensitiveWithPassword de la propiedad ProtectionLevel, al menos durante el desarrollo con BIDS, y utilizar para los entornos posteriores (Pruebas Integradas, Pre-Producción, Producción, etc.) el almacenamiento de los Paquetes DTSX en SQL Server con la opción ServerStorage de la propiedad ProtectionLevel. De hecho, al promocionar entre entornos los Paquetes DTSX se pueden cambiar las propiedades de conexión y utilizar distintos ficheros de configuración.

¿Es posible especificar a un Paquete DTSX distintas configuraciones, en función de en qué entorno o servidor se ejecute? ¿Cómo funcionan los Ficheros de Configuraciones?

En SSIS disponemos de los Ficheros de Configuraciones. Es posible crear y utilizar uno o varios Ficheros de Configuración (o ninguno, si no se quiere) para cada Paquete DTSX (siempre es más cómodo utilizar el menor número de configuraciones, pero utilizar múltiples configuraciones, pueder ser también de utilidad). Cada Fichero de Configuración permite asignar una o varias Propiedades, ya sea del Paquete DTSX o de cualquiera de sus componentes o tareas (ej: connection managers, log providers, variables, etc.). También es posible asignar valores a las Variables de los Paquetes DTSX. Los Ficheros de Configuración se cargan y evalúan en tiempo de ejecución del Paquete DTSX.

No sólo se pueden obtener configuraciones desde un Fichero de Configuración, sino que también se pueden obtener configuraciones de otras fuentes como el Registro, variables de entorno, etc. A continuación, comentamos los distintos tipos de configuraciones:

• Fichero de Configuración XML (ficheros con extension .dtsConfig). Los valores se obtienen desde un fichero XML. Permite almacenar la configuración de múltiples propiedades.
• Variable de Entorno. Permite obtener el valor de una única propiedad desde una variable de entorno.
• Entrada del Registro. Permite obtener el valor de una única propiedad desde una única entrada del registro.
• Variable del Paquete DTSX padre. Permite obtener el valor de una única propiedad desde la propiedad especificada en el Paquete DTSX padre.
• SQL Server. Permite obtener el valor de múltiples propiedades desde una tabla de SQL Server.

Como vemos, los únicos tipos de configuraciones que permiten almacenar valores para múltiples propiedades, son los Ficheros de Configuración XML y SQL Server. El resto de tipos, requiere utilizar múltiples configuraciones para poder establecer valores a múltiples propiedades.

Existe otro detalle de suma importancia. Al margen del tipo de configuración elegido, existen dos métodos para especificar la ubicación de la configuración:

• Método directo. Especificar de forma explícita la ubicación de la configuración (ej: en caso de un fichero de configuración, especificar explícitamente su ruta completa).
• Método indirecto. Especificar una variable de entorno, cuyo contenido es el valor de la ubicación de la configuración (ej: una variable SSIS_CONF_FILE_FACTU cuyo valor es la ruta completa del fichero de configuración deseado). Esta es una muy buena opción, dado que podemos asignar a la variable de entorno deseada, distintos valores en los diferentes servidores o entornos (ej: Desarrollo, Pruebas Integradas, Producción, etc.), facilitando así la promoción entre entornos de nuestros procesos de ETL. Hay que tener en cuenta también, que en el despliegue o publicación de los Paquetes DTSX, habrá que tener en cuenta el hecho de garantizar la existencia de la configuración utilizada, y la existencia de la variable con un valor correcto.

Resulta especialmente interesante el Método indirecto junto con los Ficheros de Configuración XML, por la versatilidad que aporta.

Sin embargo, ¿Cómo se puede establecer una variable de entorno?. La forma más sencilla, es hacer click con el botón derecho sobre Mi PC (My Computer), y después click en la opción Propiedades (Properties) del menú contextual. Seguidamente, en el diálogo Propiedades del Sistema (System Properties), seleccionamos la pestaña Opciones avanzadas (Advanced), y click sobre el botón Variables de Entorno (Environment Variables). En el diálogo Variables de Entorno (Environment Variables) agregaremos o modificaremos la variable de entorno del sistema que deseamos, ya que si utilizamos una variable de entorno de usuario, si ejecutamos el Paquete DTSX con un usuario distinto al usuario contextual, dicho usuario no tendrá visibilidad sobre la variable de entorno.

¿Cómo podemos crear ún Fichero de Configuración XML (.dtsConfig)?. Desde Business Intelligence Development Studio (BIDS), en el menú SSIS, click en la opción Configuraciones de Paquetes (Package Configurations). En el diálogo Organizador de configuraciones de paquetes (Package Configurations Organizer), es posible tanto habilitar o deshabilitar las configuraciones de paquetes, como agregar, modificar o eliminar cualquier configuración, del tipo que sea. De este modo, podemos crear una configuración de tipo Fichero de Configuración XML utilizando una ruta fija y especificando las configuraciones deseadas, y seguidamente modificar dicha configuración para que en vez de obtener la ubicación del fichero como un valor fijo, lo tome de una variable de entorno que crearemos previamente.

¿Para qué sirven las Variables y Expresiones en SSIS? ¿Cómo se utilizan?

Es posible definir Variables en un Paquete DTSX, con el fin de poder utilizarlas en el Paquete DTSX o en cualquiera de sus componentes (tareas, conexiones, etc.), a través de Expresiones Simples o Complejas.

Se puede distinguir entre Variables definidas por el usuario (las que creamos nosotros mismos en nuestros Paquetes DTSX) y Variables del Sistema (ya vienen incorporadas, y sólo se pueden leer o especificar un evento que salte cuando cambien de valor). Los nombres de Variables son susceptibles de mayúsculas y minúsculas.

Las Variables de SSIS también tienen ámbito. Así, se pueden crear en el ámbito de un Paquete DTSX (como si se tratase de una variable global), o en el ámbito de un contenedor, tarea o controlador de evento.

Una Expresión es una combinación de símbolos (funciones, variables, etc.) que se puede evaluar devolviendo una valor. Una Expresión Simple puede ser sencillamente una Variable, una Constante o Literal, o una Función. Una Expresión Compleja, puede incluir además varios Operadores, Funciones, Columnas, etc.

Resulta especialmente útil, asignar a Propiedades el valor de las Variables, de tal modo, que a través de Ficheros de Configuración XML (o a través de otros tipos de configuraciones: Entradas de Registro, Variables de Entorno, etc.) se pueda establecer el valor de las Variables, y en consecuencia de las Propiedades. Esto es muy cómodo, ya que en caso de utilizar un mismo valor en varios sitios, es suficiente con asignar a la Varible una Configuración, y asignar la Variable a las Propiedades que la necesiten. Sin embargo, esta técnica no siempre nos será de utilidad. Por ejemplo, no se puede asignar una Expresión (ej: una Variable) a la Propiedad Password de una conexión de base de datos, al tratarse de un dato sensible. En este caso, sólo se podrá asignar directamente el valor de la Configuración.

Otra utilidad interesante es la utilización de Expresiones en las Restricciones de Precedencia. Como sabemos, dentro del Flujo de Control (Control Flow) de un Paquete DTSX, podemos establecer Restricciones de Precedencia para indicar el orden o flujo de ejecución de las distintas tareas del Flujo de Control. Por ejemplo, podemos tener una Tarea A y otra Tarea B, y establecer una Restricción de Precedencia para que la Tarea B se ejecute después que la Tarea A si la Tarea A finalizó con éxito. Habitualmente, las Restricciones de Precedencia se basan en el estado de finalización de la Tarea origen (Con éxito, fracaso, o la finalización). Sin embargo, también podemos configurar la Restricción de Precedencia para que, por ejemplo, la Tarea destino se ejecute si la Tarea origen finaliza con éxito y además se evalúa con éxito una Expresion (que por ejemplo, podría depender de una variable que se establece en la Tarea origen). Así, las posible Operaciones de Evaluación de una Restricción de Precedencia son:

• Una Restricción (Constraint). Usa el resultado de la ejecución de la tarea anterior, que puede ser éxito, fracaso o conclusión. Así, podemos especificar que la Tarea B se ejecute sólo si la Tarea A finalizó con éxito, y en caso de fracaso se ejecute una Tarea C. Este tipo de Restricción, es la más habitual.
• Una Expresión (Expression). Usa el resultado de la evaluación de una expresión (la evaluación debe dar TRUE), para decidir si la Tarea de destino se ejecuta o no.
• Una Expresión y una Restricción (Expression and Constraint). Requiere que se cumpla la evaluación de una Expresión y además una Restricción (eligiendo en éxito, fracaso o conclusión). Es decir, se deben de cumplir ambas condiciones.
• Una Expresión o una Restricción (Expression or Constraint). Requiere que se cumpla la evaluación de una Expresión o una Restricción (eligiendo en éxito, fracaso o conclusión). Es decir, es suficiente con que se cumplir una de las dos condiciones.

Este comportamiento se puede especificar desde el diálogo de Propiedades de la Restricción, o bien, click con el botón derecho sobre la Restricción, y después click sobre la opción Editar (Edit) del menú contextual, para así abrir el diálogo Editor de Restricciones de Precedencia (Precedence Constraint Editor).

Además, existen varias tareas que requieren utilizar Expresiones y/o Variables para su funcionamiento, como son:

• Contenedor de Bucles FOR (FOR LOOP Container). Las Expresiones permiten especificar las instrucciones de inicialización, evaluación e incremento del bucle.
• Transformación División Condicional (Conditional Split). Utiliza una estructura de decisión basada en expresiones booleanas para dirigir filas a los destinos deseados.
• Transformación Columna Derivada (Derived Column). Utiliza valores creados mediante Expresiones para llenar las nuevas columnas (o sustituir el valor de columnas existentes) en un Flujo de Datos (Data Flow).

Llegados a este punto, ya podemos tener una idea de la importancia y la utilidad de las Variables y de las Expresiones en los Paquetes DTSX de SSIS. Ahora queda dar una pincelada práctica al asunto.

¿Cómo se puede crear una Variable en ámbito del Paquete DTSX?. En el menú SSIS de BIDS, seleccionaremos la opción Variables, con lo que se mostrará la ventana de Variables. A continuación, abrir el Paquete DTSX deseado y hacer click sobre el fondo o tapiz del Flujo de Control (Control Flow). Ahora, en la ventana de Variables podemos ver, crear, modificar, eliminar, etc., las Variables de ámbito de Paquete.

¿Cómo se puede crear una Variables en un ámbito de Contenedor de Bucles FOR?. En el menú SSIS de BIDS, seleccionaremos la opción Variables, con lo que se mostrará la ventana de Variables. A continuación, abrir el Paquete DTSX deseado y hacer click sobre el Contenedor de Bucles FOR deseado en el Flujo de Control (Control Flow). Ahora, en la ventana de Variables podemos ver, crear, modificar, eliminar, etc., las Variables de ámbito de Contenedor de Bucles FOR.

¿Cómo se puede asignar a una Propiedad el valor de una Variable (Expresión Simple) o el valor de una Expresión Compleja?. Abrir el Paquete DTSX deseado, y abrir el diálogo Propiedades del Paquete o del elemento que se desea configurar (click con el botón derecho, y después click en Propiedades). Seguidamente, en el diálogo de Propiedades modificar la propiedad Expresiones (podemos hacer click sobre el icono de los tres puntitos). En el diálogo Editor de Expresiones de Propiedad (Property Expression Editor) podemos elegir la Propiedad deseada y escribir para la misma la Expresión que necesitemos (ya sea de memoria, o mediante el Generador de Expresiones si hacemos click sobre los tres puntitos de la Expresión).

¿Qué posibilidades de Registro (Logging) tienen los Paquetes DTSX de SSIS? ¿Cómo se utiliza?

Una configuración vital que jamás debemos olvidar es la del Registro (Logging) de nuestros Paquetes DTSX. El Registro (Logging) es un mecanismo que permite especificar qué Eventos deseamos registrar durante la ejecución de un Paquete DTSX y dónde se desea guardar la información de dichos Eventos. Se debe considerar, que es posible habilitar el Registro (Logging) a distintos niveles: Paquete, Contenedor, y/o Tarea, de tal modo, que en cada nivel podemos configurar el Registro (Logging) de los Eventos que deseemos.

Podemos seleccionar todos aquellos eventos que necesitemos, existiendo los que a continuación se enumeran:

• OnError.
• OnExecStatusChanged.
• OnInformation.
• OnPipelinePostEndOfRowset.
• OnPipelinePostPrimeOutput.
• OnPipelinePreEndOfRowset.
• OnPipelinePrePrimeOutput.
• OnPipelineRowsSent.
• OnPostExecute.
• OnPostValidate.
• OnPreExecute.
• OnPreValidate.
• OnProgress.
• OnQueryCancel.
• OnTaskFailed.
• OnVariableValueChanged.
• OnWarning.
• Diagnostic.

Principalmente, interesará utilizar los Eventos OnError, OnWarning, y OnInformation.

Así, para cada Evento podemos seleccionar qué campos deseamos que queden registrados. Por defecto, serán todos los campos, pudiendo elegir entre:

• Computer.
• Operator.
• SourceName.
• SourceID.
• ExecutionID.
• MessageText.
• DataBytes.

Podemos utilizar distintas ubicaciones para guardar la información de nuestros Eventos, a través de los distintos Proveedores de Registro que ofrece SSIS:

• Proveedor de registro SSIS para archivos de texto. Destaca por ser el método más sencillo, fácil de explorar, y fácil de manipular si posteriormente se desea cargar en una base de datos SQL Server, por poner un ejemplo. Utiliza un formato de fichero ASCII separado por comas (CSV).
• Proveedor de registro SSIS para el Analizador de SQL Server. Permite generar ficheros para abrirlo posteriormente con la herramienta Analizador de SQL Server (SQL Server Profiler).
• Proveedor de registro SSIS para SQL Server. Permite almacenar la información en SQL Server, lo cual, tiene varias ventajas: primero, que permite poder consultar la información aprovechando la potencia de Transact-SQL, y segundo, que es posile la descarga desde MSDN de un Pack de informes de Reporting Services. Otra ventaja, es que utilizar una base de datos SQL Server es un método ideal como repositorio compartido de información para su consulta por distintos usuarios. Utiliza la tabla sysdtslog90, de tal modo, que si la tabla no existe en la base de datos especificada, la crea automáticamente (no es necesario crearla manualmente).
• Proveedor de registro SSIS para el Registro de sucesos de Windows. En entornos de producción críticos, puede resultar de especial interés registrar la información de ejecución de los Paqutes DTSX en el Registro de sucesos de Windows.
• Proveedor de registro SSIS para archivos XML. Tiene prácticamente las mismas ventajas de los archivos de texto, y además, la ventaja adicional de poder utilizar XSL Transformations (XSLT) para visualizar el Registro de Paquetes DTSX como si fuese una página Web.

La elección de cuántos Proveedores de Registros y de qué tipo utilizarlos, depende principalmente de quiénes sean los usuarios de dichos Registros. En cualquier en caso, en un Paquete DTSX podemos utilizar varias Configuraciones de Registro del mismo tipo de Proveedor (ej: una configuración de Ficheros XML en una carpeta para su revisión por un equipo de trabajo, y otra configuración similar pero en otra carpeta para su almacenamiento histórico).

Por último, queda la parte práctica: ¿Cómo se implementa el Registro (Logging) de un Paquete DTSX?. Muy Fácil. Abrimos el Paquete DTSX deseado con Business Intelligence Development Studio (BIDS). Desde el menú SSIS, click a la opción Registro (Logging). En el diálogo Configurar registros de SSIS (Configure SSIS Logs), es posible especificar de forma gráfica, en cada nivel de granularidad (Paquete, Contenedor o Tarea) que se desee, qué Registros se desean utilizar (especificando su tipo y datos de conexión). Debe tenerse en cuenta que en la pestaña Detalles (Details) se debe especificar qué Eventos, y dentro de la pestaña Detalles si utilizamos el botón Avanzadas (Advanced) es posible indicar qué Columnas se desean para cada Evento.

¿Para qué sirven las Transacciones en SSIS? ¿Cómo trabajar con Transacciones en SSIS?

Las Transacciones en SSIS (al igual que ocurre con las transacciones en los motores de base de datos como SQL Server) permiten realizar varias tareas como una única unidad de trabajo (incluso trabajando sobre distintos destinos de datos), de tal modo, que se realice todo el trabajo o nada, y así garantizar la integridad de los datos aún en el caso de que se produzca algún error de ejecucion en alguna tarea de la Transacción. SSIS soporta los siguientes dos tipos de transacciones:

Transacciones DTC (Distributed Transaction Coordinator)

Utiliza el servicio DTC, por lo cual, el servicio DTC debe estar disponible para la correcta ejecución del Paquete DTSX. En caso de que no esté disponible, se producirá el siguiente error:

Error: 0xC001401A at Data Flow Task: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B “The Transaction Manager is not available.”. The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Una ventaja de las Transacciones DTC es que no requiere programar explícitamente las transacciones con sus sentencias de tipo BEGIN TRAN, COMMIT TRAN, y/o ROLLBACK TRAN, y además, permite trabajar con destinos de datos heterogéneos. Para configurar un Paquete DTSX para usar Transacciones DTC, se debe configurar la propiedad TransactionOption a nivel de Paquete, Contenedor y/o Tarea, conforme se requiere. Los posibles valores de la propiedad TransactionOption son:

• Required. El Paquete, Contenedor y/o Tarea inicia una nueva transacción, salvo en el caso de que el componente principal ya haya iniciado una transacción, en cuyo caso de juntará con la transacción del componente principal.
• Supported. El Paquete, Contenedor y/o Tarea nunca inicia una nueva transacción. Tan sólo se puede juntar con la transacción del componente principal (si fue iniciada).
• NotSupported. El Paquete, Contenedor y/o Tarea nunca inicia una nueva transacción, y nunca se junta o combina con una transacción existente.

Es importante tener en cuenta que el valor por defecto de la propiedad TransactionOption es Supported.

Así, existen distintas configuraciones posibles, que a fin de cuentas, dependen como se configure la opción TransactionOption en los Paquetes DTSX y en sus componentes o tareas (puede verse como una jerarquía). Las más típicas son las siguientes:

• Un único Paquete DTSX con una única Transacción. El Paquete DTSX se configura con el valor Required para TransactionOption, mientras que el resto de sus tareas se configuran con el valor Supported.
• Un único Paquete DTSX con múltiples Transacciones. El Paquete DTSX se configura con el valor Supported para TransactionOption, mientras que el resto de sus tareas se configuran con el valor Required.

Debido a que los Paquetes DTSX pueden incluir a su vez tareas Execute Package para ejecutar otros Paquetes DTSX secundarios, es posible utilizar otras configuraciones como por ejemplo, usar múltiples Paquetes DTSX y un única Transacción (es necesario, que exista un Paquete DTSX principal con el valor Required de la propiedad TransactionOption, y que dicho Paquete DTSX utilice tareas Execute Package).

Dicho esto, la realidad es que las Transacciones DTC son una solución excepcional, ya que para aprovechar su funcionalidad, tan sólo será suficiente con establecer a Required la propiedad TransactionOption del Paquete (o de la Tarea deseada) y listo. Además, es la única forma de aportar trasaccionalidad a las tareas de tipo Data Flow.

El problema de las Transacciones DTS, es que no todos los proveedores de datos soportan Transacciones DTC. Y aquí entra la picardía. Por ejemplo, si tenemos un origen de datos que no soporta Transacciones DTC y un destino de datos SQL Server (que sí soporta Transacciones DTC), podemos crear un Paquete DTSX con un primer paso en el que, sin usar Transacciones DTC, se carga del origen de datos a una base de datos temporal SQL Server, tal cual…. y seguidamente, desarrollamos el Paquete DTSX, pero ya leyendo de un destino que SI soporta Transacciones DTC, y en consecuencia, activando la utilización de Transacciones. Bueno… es sólo una idea… Así conseguimos evitar errores como:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Con SAP Excel” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

De hecho, en las ocasiones que me he encontrado éste error, con quitar el soporte de Transacciones DTC, problema solucionado.

Transacciones Nativas de SQL Server

Utiliza las capacidades nativas de las Transacciones de SQL Server. Tiene la ventaja de ser la solución de transaccionalidad más ligera, pero tiene varios inconvenientes:

• Sólo funciona con bases de datos SQL Server.
• Sólo funciona con tareas Execute SQL, en consecuencia, no podremos utilizarsas en tareas de Flujo de Datos (Data Flow).
• Se debe incluir explícitamente la codificación apropiada de las transacciones (sentencias de tipo BEGIN TRAN, COMMIT TRAN, y/o ROLLBACK TRAN).

Para su configuración, todas las tareas de la misma transacción deben utilizar la misma conexión (es decir, el mismo Connection Manager), y se debe establecer la propiedad RetainSameConnection de la conexión utilizada (perdón… del Conection Manager) al valor True en las tareas de la misma transacción.

¿Es posible ejecutar un Paquete DTSX en modo 32-bit sobre una máquina 64-bit (arquitectura x64)? ¿Y si no tenemos Proveedores OLEDB o .Net de 64-bit? ¿Qué ventaja tiene ésto?

Hoy en día, en las empresas que desarrollan con SSIS, es habitual que los desarrolladores utilicen sus estaciones de trabajo (Windows XP Professional o Windows Vista, habitualmente) para realizar los desarrollos, y seguidamente, utilicen los servidores empresariales (Windows Server 2003, habitualmente) para desplegar sus soluciones y ejecutarlas en sus entornos de Pruebas Integradas, Pre-Producción y Producción. Y esto ¿qué tiene de interesante? Pues mucho, porque habitualmente los puestos de trabajo montan sistemas operativos de 32-bit (arquitectura x86) mientras que los servidores empresariales suelen montar sistemas operativos 64-bit (arquitectura x64, ya que Itanium es muy poco habitual). Y esto tiene su importancia.

El principal problema de utilizar diferentes arquitecturas (ej: x86 y x64) impacta en que no todo el software y no todos los drivers (ej: Drivers ODBC, Proveedores OLEDB, Proveedores .Net, etc.) están disponibles tanto en x86 (32-bit) como en x64 (64-bit), y mucho menos aún para Itanium. Un ejemplo significativo es el Proveedor MSDASQL, que se trata del Proveedor OLEDB que hace de puente con ODBC, es decir, a través de él podemos acceder a orígenes de datos ODBC desde OLEDB. Este Proveedor OLEDB es quizás uno de los Proveedores OLEDB más utilizados, sin embargo, resulta que sólo estaba disponible en 32-bit (x86). De hecho, Microsoft no tenía intención de darle continuidad en 64-bit, sin embargo, en respuesta a las múltiples solicitudes de los usuarios, finalmente accedió y desde Abril de 2008 está disponible para descarga gratuita el Proveedor MSDASQL de 64-bit (tanto para x64 como para Itanium – IA64). ¿Y hasta Abril 2008 que podíamos haber hecho para sacar adelante nuestros desarrollos?

Para más información sobre el Proveedor MSDASQL puede visitarse el artículo ¿Como consultar un origen de datos ODBC (DSN) desde SQL Server 2005 64-bit a través de OPENROWSET u OPENQUERY + Servidor Vinculado (Proveedor MSDASQL 64-bit)?.

Bien, con este ejemplo creo que ya estamos en situación. Es decir, en una máquina x64 puede ejecutarse código x64 (código 64-bit) y código x86 (código de 32-bit, que realmente se ejecuta emulado en lo que se denomina WoW: Windows-on-Windows). De este modo:

• Un proceso de 64-bit puede utilizar sólo y exclusivamente las conectividades (OLEDB, ODBC, .Net, etc.) de 64-bit. Por lo cual, si tenemos un Paquete DTSX y lo ejecutamos en 64-bit, tenemos una dependencia directa con la disponibilidad de los drivers 64-bit necesarios, para que el Paquete DTSX pueda ejecutarse con éxito.
• Un proceso de 32-bit puede utilizar sólo y exclusivamente las conectividades (OLEDB, ODBC, .Net, etc.) de 32-bit. Por lo cual, si tenemos un Paquete DTSX y lo ejecutamos en 32-bit, tenemos una dependencia directa con la disponibilidad de los drivers 32-bit necesarios, para que el Paquete DTSX pueda ejecutarse con éxito. Bueno… realmente, esto no es problema, ya que en 32-bit (x86) suele estar disponible todo.

Resumiendo, el problema que podemos tener (y que se trata de un problema habitual), es necesitar ejecutar un Paquete DTSX sobre un servidor de 64-bit (x64), con el inconveniente de que NO exite alguno de los Drivers (OLEDB, ODBC o .Net) que utiliza para 64-bit. En este caso ¿No podemos ejecutar el Paquete DTSX? ¿Que soluciones tenemos? ¿Que debemos tener en consideración? Aquí van algunos consejos que nos pueden ayudar:

Lo primero de todo, debemos tener en cuenta que las utilidades dtexec.exe, dtutil.exe y DTSWizard.exe (el asistente de importación y exportación), sobre una máquina x64 están disponibles tanto en 64-bit como en 32-bit. Eso si, la versión 64-bit la encontraremos dentro del directorio Program Files mientras que la versión 32-bit la encontraremos dentro del directorio Program Files (x86). En consecuencia, podremos ejecutar la versión de 32-bit o de 64-bit, según nos interese, para lo cual será suficiente ejecutar el ejecutable correspondiente (el de 32-bit o el de 64-bit).

Por ello, en la planificación de JOBs con el Agente de SQL Server para ejecutar Paquetes DTSX en 32-bit sobre máquinas x64 (64-bit), si tenemos SQL Server 2005 64-bit (x64) instalado, el Agente de SQL Server será un proceso 64-bit, y por lo tanto, un JOB del Agente de SQL Server con un paso del tipo SQL Server Integration Services Package, ejecutará el Paquete DTSX en 64-bit.

Sin embargo tenemos dos alternativas con las que podemos jugar:

• Utilizar en el JOB un paso del tipo Operating system (CmdExec), y en el comando a ejecutar, invocar a la versión 32-bit de dtexec.exe, la del directorio Program Files (x86), especificando todos los parámetros necesarios para su ejecución.
• En las Propiedades del Proyecto de SSIS, establecer la propiedad Run64BitRunTime a False. Esta propiedad por defecto es True. En las máquinas 32-bit es ignorada, sin embargo, en las máquinas 64-bit permite especificar si deseamos utilizar el entorno de ejecución (RunTime) de 64-bit o de 32-bit.

También es importante tener en cuenta, que si no tenemos disponibilidad de un Proveedor OLEDB 64-bit para acceder a una determinada base de datos, quizás podamos utilizar el correspondiente Proveedor .Net 64-bit para acceder a dicha base de datos, por poner otra alternativa.

¿Qué permisos son necesarios para que los Programadores puedan guardar sus Paquetes DTSX en MSDB? ¿Qué permisos son necesarios para poder crear, editar y ejecutar un JOB del Agente de SQL Server para sus Paquetes DTSX?

Al construir un entorno de desarrollo de SSIS siempre surgen las mismas dudas:

• ¿Qué permisos son necesarios para que un desarrollador pueda guardar, ejecutar, etc. un Paquete DTSX en MSDB? ¿Cómo asignar dichos permisos?
• ¿Qué permisos son necesarios para que un desarrollador pueda crear, editar y ejecutar JOBs del Agente de SQL Server para sus Paquetes DTSX? ¿Cómo asignar dichos permisos?

En ambos casos será necesario utilizar funciones de base de datos del sistema en MSDB (es decir, roles de base de datos – fixed database roles – … vamos, que son grupos).

Para conceder permisos a los desarrolladores (o también a operadores y administradores) sobre el almacenamiento de Paquetes DTSX en MSDB, están disponibles las siguientes funciones de base de datos (osea, grupos… que el término funciones es algo confuso) en MSDB:

• db_dtsltduser. Es necesario para poder tener acceso a los Paquete DTSX almacenados en MSDB, principalmente, para poder ver, ejecutar y exportar los Paquetes DTSX propios almacenados en MSDB.
• db_dtsoperator. Mismos permisos que db_dtsltduser, sin embargo, en éste caso es para todos los Paquetes DTSX almacenados en MSDB (no sólo para los propios).
• db_dtsadmin. Mismos permisos que db_dtsoperator, pero además puede eliminar Paquetes DTSX de MSDB, cambiar las funciones de base de datos asociadas a los Paquetes DTSX (es decir, si deseamos utilizar otras que no sean las de por defecto: db_dtsltduser, db_dtsoperator, db_dtsadmin), etc.

Además, si deseamos personalizar más la seguridad de los Paquetes DTSX almacenados en MSDB, es posible utilizar funciones de base de datos definidas por el usuario. Es decir, si nos conectamos a SQL Server Integration Services (SSIS) desde SQL Server Management Studio (SSMS), al seleccionar con el botón derecho del ratón un Paquete DTSX almacenado en MSDB, se mostrará el menú contextual correspondiente. En éste menú, al seleccionar la opción Package Roles, se mostrará la siguiente ventana de diálogo:

De este modo, podemos elegir para cada Paquete DTSX, si deseamos utilizar las funciones de base de datos por defecto de SSIS (db_dtsltduser, db_dtsoperator, db_dtsadmin), o bien, seleccionar qué funciones de base de datos definidas por el usuario (luego, tendremos que crearlas nosotros, y conceder la pertenencia a los usuarios correspondientes) deseamos utilizar para acceso de lectura y/o acceso de escritura sobre el Paquete DTSX de MSDB (SQL Server).

Del mismo modo, para conceder permisos para la creación, edición y ejecución de JOBs con el Agente de SQL Server, están disponibles las siguientes funciones de base de datos en MSDB:

• SQLAgentUserRole. Es necesario para poder crear, editar y ejecutar JOBs del Agente de SQL Server (eso sí, los JOBs de los que se es propietario… el resto ni verlos).
• SQLAgentReaderRole. Mismos permisos que SQLAgentUserRole, pero además puede ver todos los JOBs (ojo, sólo ver).
• SQLAgentOperatorRole. Mismos permisos que SQLAgentReaderRole, pero además puede ver Proxies, Alertas, Operadores, etc. También puede ejecutar o parar cualquier JOB, etc.

Es importante tener en cuenta, que si no disponemos de pertenencia a ninguna de las funciones de base de datos relativas al Agente de SQL Server, desde SQL Server Management Studio (SSMS) no podremos visualzar el nodo SQL Server Agent (excepto que seamos miembros de sysadmin, etc.).

En consecuencia, los permisos mínimos que deberemos conceder a los desarrolladores, es la pertenencia a las funciones de base de datos db_dtsltduser y SQLAgentUserRole en MSDB. Es interesante conceder siempre los mínimos permisos posibles a los desarrolladores. De hecho, si no deseamos que puedan crear JOBs, etc. (que sería lo mejor, para que los administradores puedan tener mayor control de qué ocurre en la base de datos), se debería conceder sólo y exclusivamente la pertenencia a db_dtsltduser.

Tanto las funciones de base de datos relativas a SSIS y MSDB (db_dtsltduser, db_dtsoperator, db_dtsadmin) como las funciones de base de datos relativas al Agente de SQL Server (SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole), se trata de funciones de base de datos nuevas en SQL Server 2005 (es decir, no existían en SQL Server 2000 ni en versiones anteriores).

En cualquier caso, es posible encontrar más información en los Libros en Pantalla (BOL ó Books-On-Line… vamos, la ayuda de SQL Server , principalmente si deseamos obtener un detalle más exhaustivo de los permisos asociados a cada una de las funciones de base de datos descrita (aquí he querido presentar la solución y comentarlo por encima, pero en los BOL está muy bien explicado y detallado).

Dicho ésto, lo único recordar que para poder hacer miembro de estas funciones de base de datos del sistema (de MSDB) a un usuario, es necesario crear un usuario de base de datos en MSDB para los inicios de sesión a los que deseamos conceder dichos permisos. Cara a crear un usuario de base de datos, es importante recordar que si utilizamos un Inicio de Sesión para un Grupo de Directorio Activo, es posible crear un usuario de base de datos para un Usuario de Directorio Activo en particular miembro de dicho Grupo, eso sí, deberemos hacer ejecutando la correspondiente sentencia CREATE USER … FOR LOGIN (de forma gráfica, desde SQL Server Management Studio, no podremos… sólo a través de Transact-SQL con CREATE USER).

¿Por qué no puedo ejecutar un Paquete DTSX en Business Intelligence Development Studio (BIDS)? ¿Por qué está deshabilitado el botón Iniciar Depuración (Start Debugging) y el botón Iniciar sin Depurar (Start without Debugging)?

Un problema (bueno… realmente no lo es…) al que muchos nos hemos encontrado alguna vez al desarrollar Paquetes DTSX con Business Intelligence Development Studio (BIDS), es que tenemos abierto un Paquete DTSX desde el entorno de desarrollo (Visual Studio 2005, es decir, BIDS), y cuando queremos ejecutarlo (el F5 de toda la vida para probarlo, nos encontramos que el botón de ejecutar está deshabilitado (a que jode, eh

Bueno, vamos por partes (dijo Jack). Primero, no se denomina botón de ejecutar (F5) ya que en Business Intelligence Development Studio (BIDS) el botón que utilizamos para ejecutar, se denomina Iniciar Depuración (Start Debugging). En cualquier caso, jode lo mismo cuando el botón Iniciar Depuración está deshabilitado, y no sabemos por qué.

De hecho, si observamos, además de estar deshabilitado el botón Iniciar Depuración (F5), también está deshabilitado el botón Iniciar sin Depurar (Start without Debugging). Esto jode aún más. Queremos ejecutar un Paquete DTSX desde Visual Studio 2005 (BIDS) y no podemos porque los botones están deshabilitados. Están lo botones, lo vemos, pero están en gris, y no los podemos pulsar para ejecutar el Paquete DTSX. Vamos, que es imposible ejecutar el Paquete DTSX desde BIDS, no se puede ejecutar el Paquete DTSX y no sabemos por qué.

Pues nada, que es una tontería. Así es como funciona Visual Studio 2005, que a fin de cuentas, es el entorno de desarrollo en el que estamos. Para poder ejecutar un Paquete DTSX desde Visual Studio (BIDS) es necesario que dicho Paquete DTSX pertenezca a un Proyecto, y abrir dicho Proyecto con Visual Studio 2005 (BIDS). Si sólo abrimos el Paquete DTSX, los botones que utilizamos para ejecutar, es decir, el botón Iniciar Depuración (Start Debugging) y el botón Iniciar sin Depurar (Start without Debugging) aparecerán deshabilitados.

En el peor de los casos, podemos crear un nuevo Proyecto de Integration Services (o abrir un Proyecto existente) y agregar a dicho Proyecto el Paquete DTSX deseado, eso sí, recordar que al agregarlo se creará un copia (en otros tipos de Proyecto de Visual Studio 2005, se agregaría el fichero y punto, pero con Integration Services se crea una copia… no tiene mayor importancia, pero comentarlo para evitar la confusión).

En fin, un tema la mar de sencillo, pero al menos a mí, la primera vez que me ocurrió me quedé patidifuso Espero que os sirva.

[Fecha artículo: 19/02/2008]
[Estado artículo: Abierto]
[Autor: GuilleSQL]