Você já precisou consumir dados XML no SQL Server Integration Services (SSIS), mas os dados não estavam armazenados em um arquivo? Bem, há uma solução para isso – XML de variável de dados. Com esse recurso, você pode facilmente usar dados XML armazenados na memória, como se fossem escritos em um arquivo. Isso pode ser particularmente útil quando você está carregando dados de uma API na web usando uma solicitação HTTP GET.

Antes de entrarmos em detalhes, vamos revisar algumas limitações. O item XML Source no SSIS só aceita variáveis de string ao usar a opção XML de variável de dados. As variáveis de string têm um tamanho máximo de 2GB, portanto, se o seu documento XML for maior que isso, essa abordagem não funcionará.

Agora, vamos começar. Neste exemplo, carregaremos dados em uma tabela. Primeiro, precisamos criar a tabela executando o seguinte script SQL:

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'countries'))
CREATE TABLE dbo.countries
(
 id nvarchar(255) NULL,
 name nvarchar(255) NULL,
 capitalCity nvarchar(255) NULL
);

Em seguida, abra o Visual Studio Shell 2010 (ou SQL Server Data Tools) como administrador. Clique em “Novo Projeto” e selecione “Integration Services” em “Business Intelligence” no painel “Modelos Instalados”. Dê um duplo clique em “Projeto de Integration Services” para criar um novo projeto.

Agora, vamos criar duas variáveis. A primeira variável irá armazenar o endereço da API que está sendo chamada, e a segunda variável irá armazenar o documento XML. Aqui estão os detalhes:

• Variável 1:
  • Nome: xmlDoc
  • Tipo: string
  • Valor: “”
• Variável 2:
  • Nome: URI
  • Tipo: string
  • Valor: http://api.worldbank.org/countries

Certifique-se de inicializar a variável xmlDoc com um valor, caso contrário, o pacote não será construído.

Em seguida, vamos configurar uma tarefa de script que irá obter o documento XML e armazená-lo na variável xmlDoc. Arraste e solte uma tarefa de script da SSIS Toolbox para a guia Fluxo de Controle. Dê um duplo clique nela para abrir o Editor de Tarefa de Script. Defina a propriedade ReadOnlyVariables como a variável URI e a propriedade ReadWriteVariables como a variável xmlDoc.

Agora, vamos escrever o código C# que fará a solicitação HTTP e armazenará o XML na variável. Aqui está um exemplo:

#region Namespaces
using System;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
#endregion

namespace ST_3831315ea9bb45e9b1c2f2e9addad540
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string xmlDoc = String.Empty;

            try
            {
                string url = Dts.Variables["User::URI"].Value.ToString();

                using (WebClient client = new WebClient())
                {
                    xmlDoc = client.DownloadString(url);
                }

                Dts.Variables["User::xmlDoc"].Value = xmlDoc;

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

Após copiar o código, certifique-se de alterar o nome do assembly no código para corresponder ao nome do seu projeto. Depois de fazer as alterações necessárias, clique em “Build” no menu para construir a tarefa de script.

Agora, volte para a guia Fluxo de Controle e arraste e solte uma Tarefa de Fluxo de Dados. Conecte a Tarefa de Script à Tarefa de Fluxo de Dados usando uma seta verde.

Antes de continuarmos, precisamos de um arquivo XSD para definir a estrutura XML que vem da API. Baixe o arquivo XSD e dê um duplo clique na Tarefa de Fluxo de Dados para ir para a guia Fluxo de Dados. Arraste e solte uma Tarefa de Origem XML da SSIS Toolbox para a guia Fluxo de Dados.

No Editor de Origem XML, defina o modo de acesso aos dados como “Dados XML de variável” e o nome da variável como “User::xmlDoc”. Procure o arquivo XSD que você baixou e clique em OK.

Em seguida, arraste e solte um Destino SQL Server para a guia Fluxo de Dados. Conecte a Tarefa de Origem XML ao Destino SQL Server usando uma seta azul. No Editor de Destino SQL, selecione o servidor ao qual você deseja se conectar e escolha a tabela “countries” que você criou anteriormente. Clique em “Mapeamentos” para verificar se os valores no documento XML estão corretamente mapeados para as colunas na tabela. Clique em OK.

Por fim, execute o pacote e verifique a tabela “countries” para verificar se os dados da API do WorldBank foram carregados com sucesso.

Parabéns! Você usou com sucesso a opção XML de variável de dados no item XML Source para carregar dados XML na memória e gravá-los diretamente em uma tabela do SQL Server sem salvá-los em um arquivo. Feliz codificação!