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.

Anúncios

Sobre dhiegopiroto
Graduando em Sistemas de Informação, apaixonado por tecnologia, literatura e musica. Possui experiencia com desenvolvimento em T-SQL, XML, XSD e administração de bancos de dados SQL Server; Detém os titulos de MCP e MCTS SQL Server 2008.

3 Responses to SSIS – trabalhando com variáveis do tipo objeto

  1. Agnaldo says:

    Show, legal

  2. Leonardo Mqs says:

    Legal, mas depois que você populou o objeto do tipo ‘variável’ existe uma forma de deletar os dados contidos nesta variável posteriormente?

  3. Welington Marques says:

    Dhiego,

    Li seus artigos, no blog e gostaria de tirar uma duvida, há como extrair dados de 10 bases em uma mesma instância sql com o looping foreach? Já tentei diversas vezes, poderia criar um artigo sobre isso?

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Alex Souza

"Aprendendor a Aprender e Aprendendo a Ensinar (inclusive Máquinas)!"

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

%d blogueiros gostam disto: