SSIS – trabalhando com variáveis do tipo objeto

Fala Galera,

Algumas vezes precisamos, na linguagem T-SQL, utilizar os famigerados cursores. Sim, eu sei, o SQL não foi concebido para trabalhar bem linha a linha e sim com um resulset; mas, muitas vezes damos manutenção em sistemas legados e a lógica já está toda lá e não dispomos de tempo para fazer alterações.

Muito bem, digamos que precisaremos percorrer um resultset linha a linha como se fosse um cursor mas utilizando o Integration Services, é nessa hora que precisaremos utilizar as variáveis do tipo objeto. O exemplo será simples, e será dado de duas formas; a primeria utilizando 1 cursor e a segunda, representando a mesma tarefa, através de um componente “Foreach Loop Container”.

Mãos na massa:

A primeira coisa é criar a estrutura das tabelas, serão necessárias apenas duas; Uma

dbo.clientes e uma segunda chamada dbo.clientesCursor, que irá receber todos os registros que estão na dbo.clientes. Conforme o modelo:

Image


CREATE DATABASE exCursorBlog

GO

USE exCursorBlog

GO

CREATE TABLE dbo.Clientes

(

idCliente INT IDENTITY(1,1) PRIMARY KEY,

nome VARCHAR(100),

sexo BIT,

dtNascimento DATE,

dtInsercao DATETIME

)

GO

CREATE TABLE dbo.clientesCursor

(

idCliente INT PRIMARY KEY,

--Não deverá ser um campo auto-incromentado

nome VARCHAR(100),

sexo BIT,

dtNascimento DATE,

dtInsercao DATETIME

)

GO

INSERT INTO dbo.Clientes

( nome ,

sexo ,

dtNascimento ,

dtInsercao

)

VALUES

('Dhiego',   1 , '19890301' , GETDATE() ),

('Carlos',   1 , '19890301' , GETDATE() ),

('Henrique', 1 , '19890301' , GETDATE() )

Também será necessária uma procedure que irá inserir dos dados na dbo.clientesCursor através de um insert simples:


CREATE PROCEDURE usp_InsereClientesCursor(

@idCliente INT,

@nome VARCHAR(100),

@sexo BIT,

@dtNascimento DATE

) AS BEGIN

IF NOT EXISTS (SELECT TOP 1 * FROM dbo.clientesCursor

WHERE idCliente = @idCliente) BEGIN

INSERT INTO dbo.clientesCursor

( idCliente ,

nome ,

sexo ,

dtNascimento ,

dtInsercao

)

VALUES

( @idCliente , -- idCliente - int

@nome , -- nome - varchar(100)

@sexo , -- sexo - bit

@dtNascimento , -- dtNascimento - date

GETDATE()  -- dtInsercao - datetime

)

END

END

Agora iremos criar  um novo projeto no integration services e armazena-lo em um diretório de sua preferência. Vamos criar algumas variáveis que irão armazenar os dados contidos em nossa tabela ‘fonte’ e uma do tipo objeto.

Image

Crie uma nova conexão com sua base de dados:

Image

Adicione um novo componente SQL Task que irá popular nosso variável do tipo objeto. Após adicioná-lo vá em propriedades e  siga os seguintes passos:

Aba General

1° – Altere a opção resultSet para FULL RESULT SET

2° – Defina uma conexão na opção connection.

3° – Monte sua consulta:


SELECT

CONVERT(VARCHAR(100),IDCLIENTE) IDCLIENTE ,

CONVERT(VARCHAR(100),NOME) AS NOMECLIENTE,

CONVERT(VARCHAR(100),SEXO) SEXO,

CONVERT(VARCHAR(100),DTNASCIMENTO) DTNASCIMENTO

FROM CLIENTES

Image

Aba ResultSet

1° Clique em ADD e referencie, no campo variable name, sua variável do tipo objeto. É importante que o campo resultName seja o default.

Image

Adicione um componente foreach looping container e ligue-o ao nosso SQL Task.

Image

Após isso acesse as propriedades do foreach.

Aba Collection:

1° Altere o tipo do enummeraton para : Foreach ADO Enumerator

2° Referencia a variável do tipo objeto no listbox ADO Objetct Source Variable.

Image

Aba Variable Mappings

1° – Adicione todos os campos que o select do SQL Task retorna ( na mesma ordem).

Image

Dentro do componente foreach loop container adicione uma SQL Task, ela será a responsável por executar um insert a cada nova linha da variável  objClientes.

Image

Vá até as propriedades da nova SQL Task e configure a conexão, tipo do resultset e o comando a ser executado:


DECLARE

@idCliente int = ?,

@nome varchar(100) = ?,

@sexo bit = ?,

@dtNascimento datetime = ?

exec usp_InsereClientesCursor @idCliente ,@nome, @sexo,@dtNascimento

Image

Vá até a aba parameter Mapping, nela você irá declarar todas as variáveis que serão usadas no comando insert que definimos no passo anterior. Note que no comando nós atribuimos as variáveis um ‘?’, isto significa que a ordem das variáveis que você declarar no parameter mapping deverá ser a mesma da declaração das variáveis do comando insert.

Image

Execute a o pacote:

Image

Vamos conferir as tabelas:

Image

Por hoje é isso!

Se gostaram da dica, deixem um comentário!

Abraços.

Alex Souza

Banco de dados, Business Intelligence, Big Data, Governança de TI ... /* Um eterno aprendiz... */

Blog - Thiago Carlos de Alencar

Aprendendo SQL Server !

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Vitor Fava

SELECT (CrazyIdeas*2), (InsaneIdeas*100), MyExperience FROM MyBigHead WHERE InsaneLevel > 1000

Think Think SQL

DBCC DumpMemory 'TECH','ALL'

Gustavo Maia Aguiar

Artigos, dicas e algumas reflexões sobre o SQL Server

Kimberly L. Tripp

DBCC DumpMemory 'TECH','ALL'

Thiago Zavaschi R2

www.zavaschi.com

Blog do Luti

DBCC DumpMemory 'TECH','ALL'

Luan.Moreno a.k.a [SQL.Soul]

Lead Database Consultant at Pythian

Blog do Leka

let's make things better