Tarea Execute SQL

Una de las tareas más útiles en SSIS es la tarea Execute SQL. Esta tarea se puede utilizar para la ejecución de cada comando T-SQL en una base de datos originaria. Las bases de datos originarias pueden variar porque hay una amplia variedad de administradores de conexión que tienen soporte de tareas SQL. En este ejemplo, vamos a ver algunos de los escenarios más útiles, tales como la ejecución de comandos T-SQL y los resultados atractivos, o pasar parámetros de entrada y de salida.
El video de este ejercicio esta en esta dirección: https://www.youtube.com/watch?v=QAcsGILXJxg en Youtube.

Ejercicio de Aplicación

1.    Abra el SSDT y cree un nuevo tipo de proyecto Integration Services en la siguiente ubicación: C:\SSIS\Control Flow\ y nombre el proyecto TareaExecuteSQL ó puede ubicarla en cualquier otra ubicación de su agrado.

2.    Renombre el paquete como ResultSet_SingleRow.dtsx.

3.    Del menú SSIS seleccione Variables; el panel Variables aparecerá. Crear una nueva variable con Name RowCount y el Data type Int32 en Scope de paquete.

4.    En la ficha vacía Control Flow del paquete, arrastre y suelte una tarea Execute SQL desde la toolbox en Favorites en el Control Flow.

5.    Doble clic en la tarea Execute SQL; el Editor de Tarea Execute SQL se abrirá. Nombre la tarea como SQL_StaticSelectCommand.

6.    En la propiedad Connection bajo la sección SQL Statement, crear una nueva OLE DB Connection, y en el nuevo OLE DB Connection Manager, establezca el nombre del servidor como (.) para la instancia predeterminada y .\InstanceName para instancias nombradas (por ejemplo: ServerName\InstanceName). En la opción Select or enter a database name elija AdventureWorks2014. Pruebe la conexión y confírmela. Haga clic en el botón OK para retornar al Editor de Tarea Execute SQL.

7.    En la opción SQLStatement introduzca la siguiente consulta Select:

select Count(*) as Cnt from Person.Person

8.    En la pestaña General de la Execute SQL Task Editor, establezca la propiedad ResultSet a Single row.

9.    Confirme todas las configuraciones en la ficha General del editor SQL_StaticSelectCommand como se muestra en la siguiente captura de pantalla:

10.  En la ficha Result Set de la Execute SQL Task Editor, haga clic en el botón Add.  Establecer Result Name como Cnt y establecer Variable Name como User::RowCount. 

11.  Haga clic en OK y cierre el Execute SQL Task Editor. Agregue un Script Task del     Toolbox SSIS en el Control Flow justo después de SQL_StaticSelectCommand y conecte la flecha verde de SQL_StaticSelectCommand a la tarea Script.

12.  Haga doble clic en la tarea Script para abrir su editor y en la pestaña Script, en la propiedad ReadOnlyVariables, escriba el nombre de la variable de usuario como User::RowCount. En la ficha General, nombre la tarea como SCR_ShowRowCount.

13.  Haga clic en Edit Script. En el nuevo Editor de Script, en el método Main, agregue una         línea para MessageBox de la siguiente manera:

public void Main()

   {

     MessageBox.Show(

       Dts.Variables["User::RowCount"].Value.ToString()

     );

     Dts.TaskResult = (int)ScriptResults.Success;

   }

14.  Ejecute el paquete pulsando F5 y verifique el conteo de registro en la caja de mensaje. Dele clic en Aceptar y luego en el botón Stop Debugging (Shift-F5).

15.  Haga clic derecho en la carpeta SSIS Packages en el Solution Explorer y, luego, haga clic en New SSIS Package; nombre este paquete como WorkingWithParameters.

16.  En el Scope de paquete, crear una nueva variable de tipo String. Nómbrela IssueName y establezca su valor por defecto como Engine Malfunctioned. Añadir otra variable de tipo DateTime, y nómbrela Date. Confirme el valor por defecto como la fecha actual.

17.  Arrastre y suelte una nueva tarea Execute SQL en el Control Flow y nómbrelo SQL_ParametrizedCommand.

18.  Haga doble clic en la tarea Execute SQL, y en el editor, establezca una conexión a AdventureWorks2014 y escriba el siguiente comando en la propiedad SQLStatement:

INSERT INTO [Production].[ScrapReason]

        ([Name],[ModifiedDate])

    VALUES (?,?)

19.  Ir a Parameter Mapping y haga clic en el botón Add. Establezca el Variable Name como User::IssueName, Data Type como NVARCHAR y Parameter Name como 0. Luego, añada otra fila con Variable Name como User::Date, tipo de datos como DATE y el nombre del parámetro como 1.

   

20.  Haga clic en OK y cierre la tarea Execute SQL. Clic derecho en la tarea SQL_ParameterizedCommand y seleccione Execute Task. Después de ejecutar la tarea verifique la tabla Production.ScrapReason en AdventureWorks2014.

21.  Confirmar la fila recién insertada como sigue: Abrir el SSMS, bajo Bases de datos expanda AdventureWorks2014, bajo Tablas haga clic derecho en Production.ScrapReason y luego haga clic en Seleccionar las primeras 1,000 filas. Puede encontrar su nueva fila allí.

Funcionamiento

En esta ejercicio, se utilizó una tarea Execute SQL para ejecutar comandos T-SQL en una base de datos SQL Server. En primer lugar, un administrador de conexión debería ser seleccionado a una base de datos originaria; la conexión tiene una amplia gama de tipos que son soportados por SSIS como OLE DB, ADO.NET y Excel.

La sentencia SQL es donde el T-SQL Command toma lugar; el comando puede variar en función de las bases de datos originarias. Por ejemplo, si una base de datos originaria es SQL Server 2005 y superior, funciones tales como row_number() pueden ser utilizadas. Si la base de datos originaria es una Oracle DB, entonces, funciones integradas para Oracle pueden ser utilizadas.

T-SQL Commands en la tarea Execute SQL puede retornar un resultado, y el resultado es definible en la propiedad ResultSet de la ficha General, que puede ser None, Single row, Full result set, o XML. None significa que el T-SQL Command no genera resultado; Single row es utilizada cuando un solo registro de datos es retornado por T-SQL Command.

Cuando se estableció la propiedad ResultSet como una Single row, debemos definir donde se almacenarán las columnas resultantes. La ficha Result Set del editor de tareas Execute SQL es donde asigna los nombres de resultado, que son exactamente los mismos que los nombres de columna en la salida para T-SQL Commands y variables de paquete. Note que si el T-SQL Command retorna columnas sin nombre especificado, deberíamos establecer un nombre de alias para esa columna con el fin de acceder a él.

Es obvio que los T-SQL Commands deben estar parametrizados y que pueden leer los parámetros de entrada de variables de paquete o entregar los parámetros de salida en las variables. La tarea Execute SQL proporciona esta función con marcadores de parámetros en T-SQL Command. El marcador de parámetro es un marcador de posición para valores de parámetro. En el ejemplo, hemos utilizado un signo de interrogación (?) como un marcador de parámetro; el signo de interrogación es un marcador de parámetro para el tipo OLE DB Connection. Cada tipo de conexión tiene su propio marcador de parámetro. Aquí hay una lista de marcadores de parámetro para todos los tipos de conexión:

 

Además de añadir marcadores de parámetro en la propiedad SQL Statement, la asignación de parámetros con variables de paquete debería ser definido en la ficha Parameter Mapping del editor de tareas Execute SQL. En esta ficha, para cada variable o asignación de parámetro, una fila será añadida. Hay algunas propiedades aquí; Direction es de elegir entre la entrada y salida para los parámetros de entrada y de salida si queremos utilizarlas dentro de un procedimiento almacenado. Parameter Name es la siguiente propiedad importante, que debería ser seleccionada basada en el tipo de conexión. Puede ver diferentes nombres de los parámetros para los diferentes tipos de conexión que figuran en la tabla anterior.

Podemos ejecutar procedimientos almacenados sobre una base de datos originaria por ejecutar comandos en SQL (sentencias como exec dbo.CalculateSalarySP ?, ? por ejemplo). Sin embargo, hay otra opción llamada IsQueryStoredProcedure para administradores de conexión ADO.NET y ADO. Cuando esta propiedad es True, la sentencia SQL debería ser el nombre del procedimiento almacenado, tal como, CalculateSalary. La siguiente tabla muestra cómo podemos configurar una tarea Execute SQL para ejecutar procedimientos almacenados:

Adicionales

Utilizamos tareas Execute SQL con dos tipos de sentencias SQL, una sentencia con ningún conjunto de resultados, y otro con un conjunto de resultado de una sola fila. En los escenarios del mundo real tenemos que ejecutar consultas que tienen conjuntos de resultados diferentes.

Full result set

Si la sentencia SQL retorna varias filas, entonces no podemos usar un conjunto de resultados Single row; en este caso deberíamos establecer la propiedad ResultSet de la ficha General a Full result set. Además, en la ficha Result Set, una variable de tipo object debería ser asignada a nombre de resultado 0.

Después de ejecutar la tarea Execute SQL, la variable tipo object tendrá todos los registros del conjunto resultado y podremos recorrer a través de los registros y realizar las operaciones apropiadas.

XML result set

Hay algunos casos donde necesitamos conseguir resultados XML de una consulta T-SQL. Aquí es donde el conjunto resultado XML entra en juego en tareas Execute SQL. Uno de los escenarios más útiles es una consulta T-SQL que utiliza la cláusula FOR XML para retornar los datos como XML.

Todos los tipos de conjuntos resultado y la asignación de salida a variables se describen en la siguiente tabla:

BypassPrepare

La opción BypassPrepare se encuentra en la ficha General del Editor de Tarea Execute SQL. Esta opción sólo está disponible con tipos OLE DB Connection, y proporciona Prepared Execution.

Prepared Execution es una manera de reducir el tiempo para analizar y compilar una sentencia SQL. De esta manera, la función SQLPrepare primero analizará y compilará la sentencia SQL en un plan de ejecución, y luego usará la función SQLExecute para ejecutar el plan de ejecución. Así que combinando SQLPrepare y SQLExecute proporciona una manera de deshacernos de la sobrecarga del análisis y compilación de las sentencias SQL que se ejecutarán muchas veces.