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.
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:
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:
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:
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.
