Tarea Ejecutar SQL

Actualizado: 15 de septiembre de 2007

La tarea Ejecutar SQL ejecuta instrucciones SQL o procedimientos almacenados de un paquete. La tarea puede contener una sola instrucción SQL o múltiples instrucciones SQL que se ejecutarán de forma secuencial. Puede usar la tarea Ejecutar SQL para los siguientes fines:

• Truncar una tabla o vista en preparación para insertar datos.
• Crear, modificar y quitar objetos de base de datos, como tablas y vistas.
• Volver a crear tablas de hechos y tablas de dimensiones antes de cargar datos en ellas.
• Ejecutar procedimientos almacenados.
• Guardar en una variable el conjunto de filas devuelto por una consulta.

Puede configurar la tarea Ejecutar SQL de las maneras siguientes:

• Especificar el tipo de administrador de conexión que se va a utilizar para conectar con una base de datos.
• Especificar el tipo de conjunto de resultados que devolverá la instrucción SQL.
• Especificar un tiempo de espera para las instrucciones SQL.
• Especificar el origen de la instrucción SQL.
• Indicar si la tarea omite la fase de preparación para la instrucción SQL.
• Si utiliza el tipo de conexión ADO, debe indicar si la instrucción SQL es un procedimiento almacenado. Para otros tipos de conexión, esta propiedad es de sólo lectura y su valor siempre es false.

La tarea Ejecutar SQL puede usarse en combinación con los contenedores de bucles Foreach y For para ejecutar varias instrucciones SQL. Estos contenedores implementan flujos de control que se repiten en un paquete y pueden ejecutar repetidamente la tarea Ejecutar SQL. Por ejemplo, un paquete puede usar el contenedor de bucles Foreach para enumerar los archivos de una carpeta y ejecutar una tarea Ejecutar SQL repetidamente con el fin de ejecutar la instrucción SQL almacenada en cada archivo.

Conectar con un origen de datos

La tarea Ejecutar SQL puede usar distintos tipos de administradores de conexión para conectar con el origen de datos en el que se ejecuta la instrucción SQL o el procedimiento almacenado. La tarea puede usar los tipos de conexión mostrados en la tabla siguiente.

Crear instrucciones SQL

El origen de las instrucciones SQL usadas por esta tarea puede ser una propiedad de tarea que contiene una instrucción, una conexión con un archivo que contiene una o varias instrucciones, o el nombre de una variable que contiene una instrucción. Debe escribirlas en el dialecto del sistema de administración de bases de datos (DBMS) de origen.

Si las instrucciones SQL se almacenan en un archivo, la tarea utiliza un administrador de conexión de archivos para conectar con el archivo. Para obtener más información, vea Administrador de conexión de archivos.

En el Diseñador SSIS, puede utilizar el cuadro de diálogo Editor de la tarea Ejecutar SQL para escribir instrucciones SQL o puede utilizar el Generador de consultas, una interfaz gráfica de usuario para crear consultas SQL.

[!NOTA] Es posible que la tarea Ejecutar SQL no pueda analizar correctamente instrucciones SQL válidas escritas fuera de dicha tarea.

Enviar varias instrucciones en un lote

Si incluye varias instrucciones en una tarea Ejecutar SQL, puede agruparlas y ejecutarlas como un lote. Para indicar el final de un lote, utilice el comando GO. Todas las instrucciones SQL entre dos comandos GO se envían en un lote al proveedor OLE DB para que se ejecuten. El comando SQL puede incluir varios lotes separados por comandos GO.

Hay restricciones respecto a los tipos de instrucciones SQL que se pueden agrupar en un lote. Para obtener más información, vea Batches of Statements.

Si la tarea Ejecutar SQL ejecuta un lote de instrucciones SQL, se aplicarán las reglas siguientes al lote:

• Sólo una instrucción puede devolver un conjunto de resultados y debe ser la primera instrucción del lote.
• Si el conjunto de resultados usa enlaces de resultados, las consultas deben devolver el mismo número de columnas. Si las consultas devuelven un número distinto de columnas, la tarea no se completará correctamente. Sin embargo, aunque la tarea no se complete correctamente, se completarán correctamente las consultas que ejecute, como DELETE o INSERT.
• Si los enlaces de resultados usan nombres de columna, la consulta debe devolver columnas que tengan los mismos nombres que los nombres de los conjuntos de resultados que se utilizan en la tarea. Si faltan columnas, la tarea no se completa correctamente.
• Si la tarea utiliza enlace de parámetros, todas las consultas del lote deberán tener el mismo número de parámetros y estos deberán ser del mismo tipo.

Ejecutar comandos SQL con parámetros

Las instrucciones SQL y los procedimientos almacenados suelen usar parámetros de entrada, parámetros de salida y códigos de retorno. La tarea Ejecutar SQL admite los tipos de parámetros Input, Output y ReturnValue. Utilice el tipo Input para parámetros de entrada, Output para parámetros de salida y ReturnValue para códigos de retorno.

[!NOTA] Sólo puede usar parámetros en una tarea Ejecutar SQL si el proveedor de datos los admite.

Los parámetros de los comandos SQL, incluidas las consultas y los procedimientos almacenados, se asignan a variables definidas por el usuario que se crean dentro del ámbito de la tarea Ejecutar SQL, un contenedor primario o dentro del ámbito del paquete. Los valores de las variables pueden establecerse en tiempo de diseño o llenarse dinámicamente en tiempo de ejecución. También puede asignar parámetros a variables del sistema. Para obtener más información, vea Variables de Integration Services y Variables del sistema.

En función del tipo de conexión que utiliza la tarea Ejecutar SQL, la sintaxis del comando SQL utiliza diferentes marcadores de parámetros. Por ejemplo, el tipo de administrador de conexión ADO.NET requiere que el comando SQL utilice un marcador de parámetro con el formato @varParameter, mientras que el tipo de conexión OLE DB requiere el signo de interrogación (?) como marcador de parámetro.

Los nombres que puede utilizar como nombres de parámetros en las asignaciones entre variables y parámetros también varían por tipo de administrador de conexión. Por ejemplo, el tipo de administrador de conexión ADO.NET utiliza un nombre definido por el usuario con un prefijo @, mientras que el tipo de administrador de conexión OLE DB requiere que utilice el valor numérico de un ordinal basado en 0 como nombre de parámetro.

La tabla siguiente resume los requisitos de los comandos SQL para los tipos de administrador de conexión que puede utilizar la tarea Ejecutar SQL.

Parámetros con administradores de conexión ADO.NET

Los administradores de conexión ADO.NET requieren que el comando SQL utilice nombres de parámetros como marcadores de parámetros. Esto significa que las variables se pueden asignar directamente a los parámetros. Por ejemplo, la variable @varName se asigna al parámetro denominado @parName y proporciona un valor al parámetro @parName.

Parámetros con administradores de conexión con EXCEL, ODBC y OLE DB

Los administradores de conexión con Excel, ODBC y OLE DB requieren que el comando SQL utilice signos de interrogación (?) como marcadores de parámetros y valores numéricos basados en 0 o en 1 como nombres de parámetros. Si la tarea Ejecutar SQL utiliza el administrador de conexión ODBC, el nombre del parámetro que se asigna al primer parámetro de la consulta se denomina 1; de lo contrario, el parámetro se denomina 0. Para los demás parámetros, el valor numérico del nombre del parámetro indica el parámetro en el comando SQL al que se asigna el nombre del parámetro. Por ejemplo, el parámetro denominado 3 se asigna al tercer parámetro, que se representa con un signo de interrogación (?) en el comando SQL.

Para proporcionar valores a los parámetros, las variables se asignan a nombres de parámetros y la tarea Ejecutar SQL utiliza el valor ordinal del nombre del parámetro para cargar valores de variables a parámetros.

Si la tarea Ejecutar utiliza el tipo de conexión de OLE DB, está disponible la propiedad BypassPrepare de la tarea. Debe establecer esta propiedad en true si la tarea Ejecutar SQL utiliza instrucciones SQL con parámetros.

Al utilizar un Administrador de conexión OLE DB, no puede utilizar las subconsultas con parámetros porque la tarea Ejecutar SQL no puede obtener información de parámetros a través del proveedor OLE DB. Sin embargo, puede utilizar una expresión para concatenar los valores de parámetro en la cadena de consulta y para establecer la propiedad SqlStatementSource de la tarea.

En función del proveedor que utiliza el administrador de conexión, es posible que no se acepten algunos tipos de datos OLE DB. Por ejemplo, el controlador de Excel reconoce sólo un conjunto limitado de tipos de datos. Para obtener más información sobre el comportamiento del proveedor Jet con el controlador de Excel, vea Origen de Excel.

Parámetros con administradores de conexión ADO

Los administradores de conexión ADO requieren que el comando SQL utilice signos de interrogación (?) como marcadores de parámetros, pero puede utilizar cualquier nombre definido por el usuario, a excepción de valores enteros, como nombres de parámetros.

Para proporcionar valores a los parámetros, las variables se asignan a nombres de parámetros y la tarea Ejecutar SQL utiliza el valor ordinal del nombre del parámetro en la lista de parámetros para cargar valores de variables a parámetros.

Usar parámetros con cláusulas WHERE

Los comandos SELECT, INSERT, UPDATE y DELETE frecuentemente incluyen cláusulas WHERE para especificar filtros que definen las condiciones que debe cumplir cada fila de las tablas de origen para satisfacer los requisitos de un comando SQL. Los parámetros proporcionan los valores de filtro en las cláusulas WHERE.

Puede utilizar marcadores de parámetros para proporcionar valores de parámetros de forma dinámica. Las reglas para los marcadores y nombres de parámetros que se pueden utilizar en la instrucción SQL dependen del tipo de administrador de conexión que utiliza la tarea Ejecutar SQL.

La tabla siguiente enumera ejemplos del comando SELECT por tipo de administrador de conexión. Las instrucciones INSERT, UPDATE y DELETE son similares. Los ejemplos utilizan el comando SELECT para devolver productos de la tabla Product de AdventureWorks que tienen un valor para ProductID superior e inferior a los valores que especifican los dos parámetros.

Los ejemplos necesitarán parámetros que tengan los siguientes nombres:

• Los administradores de conexión con EXCEL y OLE DB utilizan los nombres de parámetros 0 y 1. El tipo de conexión ODBC utiliza 1 y 2.
• El tipo de conexión ADO podría utilizar cualquier par de nombres de parámetros, como Param1 y Param2, pero los parámetros deben asignarse por su posición ordinal en la lista de parámetros.
• El tipo de conexión ADO.NET utiliza los nombres de parámetros @parmMinProductID y @parmMaxProductID.

Usar parámetros con procedimientos almacenados

Los comandos SQL que ejecutan procedimientos almacenados también pueden usar la asignación de parámetros. Las reglas sobre el uso de marcadores y nombres de parámetros dependen del tipo de administrador de conexión que utiliza la tarea Ejecutar SQL, del mismo modo que sucede con las consultas con parámetros.

La tabla siguiente enumera ejemplos del comando EXEC por tipo de administrador de conexión. Los ejemplos ejecutan el procedimiento almacenado uspGetBillOfMaterials en AdventureWorks. El procedimiento almacenado utiliza los parámetros de entrada @StartProductID y @CheckDate.

Para utilizar parámetros de salida, la sintaxis requiere que la palabra clave OUTPUT siga a cada marcador de parámetro. Por ejemplo, EXEC myStoredProcedure ? OUTPUT.

Para obtener más información sobre el uso de parámetros de entrada y salida con procedimientos almacenados de Transact-SQL, vea Parámetros (motor de base de datos), Devolver datos mediante parámetros OUTPUT y EXECUTE (Transact-SQL).

Obtener valores de códigos de retorno

Un procedimiento almacenado puede devolver un valor entero, denominado código de retorno, para indicar el estado de ejecución de un procedimiento. Para implementar códigos de retorno en la tarea Ejecutar SQL, debe utilizar los parámetros del tipo ReturnValue.

La tabla siguiente enumera, por tipo de conexión, algunos ejemplos de comandos EXEC que implementan códigos de retorno. Todos los ejemplos utilizan un parámetro de entrada. Las reglas sobre el uso de marcadores y nombres de parámetros con ReturnValue son las mismas reglas que se aplican a los tipos de parámetros Input y Output.

Algunas sintaxis no admiten literales en los parámetros. En tal caso, debe proporcionar el valor del parámetro mediante una variable.

Para obtener más información sobre el uso de códigos de retorno con procedimientos almacenados de Transact-SQL, vea Devolver datos mediante un código de retorno y RETURN (Transact-SQL).

Especificar un tipo de conjunto de resultados

En función del tipo de comando SQL, la tarea Ejecutar SQL puede devolver o no un conjunto de resultados. Por ejemplo, una instrucción SELECT suele devolver un conjunto de resultados; en cambio, una instrucción INSERT no lo devuelve. El conjunto de resultados de una instrucción SELECT puede contener cero filas, una fila o muchas filas. Los procedimientos almacenados también pueden devolver un valor entero, denominado código de retorno, que indica el estado de ejecución del procedimiento. En tal caso, el conjunto de resultados constará de una fila individual.

La tarea Ejecutar SQL admite los siguientes tipos de conjuntos de resultados:

• El conjunto de resultados Ninguno se usa cuando la consulta no devuelve ningún resultado. Por ejemplo, este conjunto de resultados se utiliza para consultas que agregan, cambian y eliminan registros de una tabla.
• El conjunto de resultados Fila única se usa cuando la consulta devuelve una sola fila. Por ejemplo, este conjunto de resultados se usa para un procedimiento almacenado que devuelve un código de retorno, o para una instrucción SELECT que devuelve un recuento o una suma.
• El conjunto de resultados Conjunto de resultados completo se usa cuando la consulta devuelve múltiples filas. Por ejemplo, este conjunto de resultados se usa para una instrucción SELECT que recupera todas las filas de una tabla.
• El conjunto de resultados XML se usa cuando la consulta devuelve un conjunto de resultados en formato XML. Por ejemplo, este conjunto de resultados se usa para una instrucción SELECT que incluya una cláusula FOR XML.

Si la tarea Ejecutar SQL utiliza el conjunto de resultados Conjunto de resultados completo y la consulta devuelve varios conjuntos de filas, la tarea sólo devuelve el primero de ellos. Si el primer conjunto de filas genera un error, la tarea lo notifica. Si otros conjuntos de filas generan errores, la tarea no los notifica.

La tarea Ejecutar SQL convierte en cadenas los valores devueltos por la instrucción SQL que no sean cadenas. Por ejemplo, los valores que tengan los tipos de datos uniqueidentifier, bigint, decimal o numeric de SQL Server se convertirán en cadenas.

Llenar una variable con un conjunto de resultados

Puede enlazar el conjunto de resultados devuelto por una consulta con una variable definida por el usuario si el tipo del conjunto de resultados es una fila individual, un conjunto de filas o XML.

Si el tipo de conjunto de resultados es Fila única, puede enlazar una columna del resultado devuelto a una variable utilizando el nombre de la columna como nombre del conjunto de resultados, o puede utilizar la posición ordinal de la columna en la lista de columnas como dicho nombre. Por ejemplo, el nombre del conjunto de resultados para la consulta SELECT Color FROM Production.Product WHERE ProductID = ? puede ser Color o 0. Si la consulta devuelve varias columnas y desea obtener acceso a los valores de todas ellas, debe enlazar cada columna a una variable distinta. Si asigna columnas a variables utilizando números como nombre de los conjuntos de resultados, los números reflejan el orden de aparición de las columnas en la lista de columnas de la consulta. Por ejemplo, en la consulta SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?, puede utilizar 0 para la columna Color y 1 para la columna ListPrice. La capacidad de utilizar un nombre de columna como nombre del conjunto de resultados dependerá del proveedor para el que se haya configurado la tarea. No todos los proveedores ponen los nombres de columna a disposición.

Es posible que algunas consultas que devuelven un valor único no incluyan nombres de columnas. Por ejemplo, la instrucción SELECT COUNT (*) FROM Production.Product no devuelve un nombre de columna. Puede tener acceso al resultado devuelto utilizando la posición ordinal 0 como nombre del resultado. Para tener acceso al resultado devuelto por nombre de columna, la consulta debe incluir una cláusula AS <nombre de alias> para proporcionar un nombre de columna. La instrucción SELECT COUNT (*)AS CountOfProduct FROM Production.Product proporciona la columna CountOfProduct. Luego puede tener acceso al resultado devuelto utilizando el nombre de la columna CountOfProduct o la posición ordinal 0.

Si el tipo de conjunto de resultados es Conjunto de resultados completo o XML, debe utilizar 0 como nombre del conjunto de resultados.

Cuando se asigna una variable a un conjunto de resultados con el tipo de conjunto de resultados Fila única, la variable debe tener un tipo de datos que sea compatible con el tipo de datos de la columna contenida en el conjunto de resultados. Por ejemplo, un conjunto de resultados que contiene una columna con un tipo de datos String no se puede asignar a una variable con un tipo de datos numérico. Un conjunto de resultados XML sólo se puede asignar a una variable con el tipo de datos String o Object. Si la variable tiene el tipo de datos String, la tarea Ejecutar SQL devuelve una cadena y el origen XML puede consumir los datos XML. Si la variable tiene el tipo de datos Object, la tarea Ejecutar SQL devuelve un objeto DOM (modelo de objetos de documento). Un Conjunto de resultados completo se debe asignar a una variable del tipo de datos Object. El resultado devuelto es un objeto de conjunto de filas. Puede escribir tareas personalizadas que se desplacen por el objeto de conjunto de filas y tengan acceso a información sobre las columnas y los datos del conjunto de filas.

En la tabla siguiente se resumen los tipos de datos de variables que se pueden asignar a conjuntos de resultados.

La variable puede definirse en el ámbito de la tarea Ejecutar SQL o en el ámbito del paquete. Si la variable tiene ámbito de paquete, el conjunto de resultados estará disponible para otras tareas y otros contenedores del paquete, así como para cualquier paquete ejecutado por las tareas Ejecutar paquete o Ejecutar paquete DTS 2000.

Al asignar una variable a un conjunto de resultados de fila única, es posible que se conviertan en cadenas los valores que devuelve la instrucción SQL y que aún no son cadenas. Que se produzca esta conversión, o que esta conversión sea implícita o explícita, depende del tipo de administrador de conexión utilizado:

• Con un administrador de conexión ADO.NET, no se producirá la conversión.
• Con un administrador de conexión ODBC o ADO, la conversión se producirá implícitamente.
• Con un administrador de conexión OLE DB o Excel, el administrador de conexión convierte explícitamente a cadenas los valores de los tipos siguientes: DBTYPE_I8, DBTYPE_UI8, DBTYPE_NUMERIC, DBTYPE_GUID y DBTYPE_BYTES.

Para obtener información sobre cómo cargar un conjunto de resultados en una variable, vea Cómo asignar conjuntos de resultados a variables en una tarea Ejecutar SQL.

Entradas del registro personalizadas disponibles en la tarea Ejecutar SQL

La siguiente tabla contiene la entrada del registro personalizada para la tarea Ejecutar SQL. Para obtener más información, vea Implementar inicios de sesión en paquetes y Mensajes personalizados para registro.

Solucionar problemas de la tarea Ejecutar SQL

A partir del Service Pack 2 (SP2) de Microsoft SQL Server 2005, puede registrar las llamadas que la tarea Ejecutar SQL realiza a proveedores de datos externos. Puede utilizar esta nueva capacidad de registro para solucionar problemas relacionados con los comandos SQL ejecutados por la tarea Ejecutar SQL. Para registrar las llamadas que la tarea Ejecutar SQL realiza a un proveedor de datos externo, habilite el registro de paquetes y seleccione el evento Diagnostic en el nivel de paquete. Para obtener más información, vea Solucionar problemas de ejecución de paquetes.

A veces, un comando o un procedimiento almacenado SQL devuelven varios conjuntos de resultados. Estos conjuntos de resultados incluyen no sólo los conjuntos de filas que son el resultado de consultas SELECT, sino los valores únicos que son el resultado de instrucciones RAISERROR o PRINT. Excepto para el administrador de conexión ODBC, el resto de administradores de conexión omiten los conjuntos de resultados que se producen después del primer conjunto de resultados. Por consiguiente, estos administradores de conexión omitirán un error devuelto por un procedimiento almacenado o un comando SQL si el error no forma parte del primer conjunto de resultados.

Configurar la tarea Ejecutar SQL

Puede establecer propiedades mediante programación o a través del Diseñador SSIS.

Para obtener más información acerca de las propiedades que puede establecer en el Diseñador SSIS, haga clic en uno de los temas siguientes:

• Editor de la tarea Ejecutar SQL (página General)
• Editor de la tarea Ejecutar SQL (página Asignación de parámetros)
• Editor de la tarea Ejecutar SQL (página Conjunto de resultados)
• Página Expresiones

Para obtener más información sobre cómo establecer estas propiedades en el Diseñador SSIS, haga clic en el siguiente tema:

• Cómo establecer las propiedades de las tareas

Configurar la tarea Ejecutar SQL mediante programación

Para obtener más información sobre cómo establecer estas propiedades mediante programación, haga clic en el tema siguiente:

• ExecuteSQLTask

Vea también

Tareas

Cómo asignar parámetros de consulta a variables en una tarea Ejecutar SQL

Conceptos

Contenedor Foreach Loop
Contenedor de bucles For
Tareas de Integration Services
Crear un flujo de control de paquetes

Otros recursos

Preparar instrucciones SQL

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios