[SSIS] Access is Denied com usuário não administrador

Fala Galera,

Mais uma dica rápida de SSIS:

Cenário: Ambiente com AD. Usuário não adm tentando acessar o Integration Services via SSMS.
Erro: Access is Denied.
By default, only administrators have access to the integration services.

Sem título.png

Motivo :”In previous versions of SQL Server, by default when you installed SQL Server all users in the Users group had access to the Integration Services service. When you install the current release of SQL Server, users do not have access to the Integration Services service. The service is secure by default. After SQL Server is installed, the administrator must grant access to the service.”

fonte: https://msdn.microsoft.com/en-us/library/hh213130(v=sql.110).aspx

Solução:

Adicionar o permissionamento necessário para o usuário

Abra o DCOMCNFG, um utilitário padrão do windows para alterar configurações do registro

Iniciar >  executar > dcomcnfg.exe

Capturar.PNG

 

Depois basta reiniciar o serviço do SSIS e testar novamente

Sem título.png

Done 🙂

[]’s

Piroto

 

[SSIS] Integração SQL Server e Oracle

Fala Galera,

Sei que estou sumido. As coisas tem estado um tanto quanto caóticas. Muitos projetos simultâneos e tecnologias novas para aprender; Eu destacaria Websphere MQ, AIX e Oracle Forms.

Mas hoje, aproveitando a madrugada do feriado do dia do trabalho, vou fazer post simples e objetivo sobre como fazer integração entre uma base de dados Oracle 11g (rodando em um red hat) e um SQL Server 2012, através do integration services. O assunto está dividido em duas partes;

  1. Preparando o ambiente [obtendo e instalando o driver Oracle ODBC e configurando o tnsnames.ora]
  2. Criando um pacote para transferência de dados entre as bases.

obs: Eu fiz o mesmo procedimento como o SQL 2008, a diferença é que para o 2008 só conseguimos usar um driver ODBC e para o 2012 conseguimos usar o OLEDB ou ODBC 🙂

Imagem

 

Vamos lá..

Preparando o ambiente

Primeira coisa: Precisamos fazer o download do provider ODBC 32bits da Oracle.

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zip

 

  • Descompactar o Client do Oracle Driver ODBC 32 bits
  • Execute o C:\comp\win32_11gR2_client\client\setup.exe

Selecione a instalação como administrador e vá em Next

Imagem

  • Reinicie o servidor
  • Configure o tnsnames.ora localizado em: [UNIDADE INSTALAÇÂO DO DRIVER ]:\app\Administrator\product\11.2.0\client_1\network\admin
    O tnsnames é um arquivo de configuração, onde todos os dados de acesso do seu banco deverão ser armazenados./
    mais informações podem ser encontradas em: http://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm#NETRF007

Linux1=

  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev1ora.devlin.com)
    )
  )

 

Após configurarmos nosso tnsnames.ora, iremos utilizar o comando “tnsping” para que a conexão entre os dois hosts (SQL e ORACLE) seja validada.

Imagem

 

Criando seu pacote de integração

Pré–requisito: Criação de sinônimos das tabelas que farão parte da integração.

Faça o login na sua base de dados oracle, encontre a tabela que você deseja que faça parte da integração e crie um sinonimo para ela; caso contrário ela não será listada como uma entidade válida para fazer parte da task.

Imagem

Abra o Visual Studio

  • Crie um novo projeto do integration services.
  • Adicione uma “DATA FLOW TASK”. Clique 2x nela
  • Adicione uma fonte de dados (ADO NET SOURCE)
  • configure a conexão com seu banco SQL Server

Imagem

  • Adicione uma “OLE DB Destination”. Clique 2x
  • Clique em “new” para configurar a nova fonte de dados. Uma nova janela será aberta “configure OLE DB connection manager”. Clique em new.

Imagem

  • No topo da tela, no list box “provider”; selecione o Oracle provider for OLE DB e clique OK

Imagem

  • Preencha os dados de conexão, conforme no arquivo TNSNAMES.ORA. Depois clique em testar conexão.:

Imagem

  • Se tudo tiver configurado adequadamente, a seguinte mensagem será apresentada

Imagem

  • De OK nas próximas 2 janelas. Até que a seguinte janela seja apresentada. Ao clicar no list box “Name of the table or the view”, todas as tabelas do oracle serão apresentadas.(Lembrando que será necessário que exista um sinônimo da tabela.)

Imagem

  • Depois clique em Mapping e faça o apontamento de onde cada campo (fonte / destino) e clique em OK

Imagem

  • Ligue as duas tasks recém adicionadas (source > destination)
  1. Imagem
  • Altere o parâmetro Run64RunTime to false.
  1. Botao direito no nome do projeto (1)
  2. Vá em propriedades (2)
  3. Configuration Properties > Debugging > Debug Options > Run64BitRunTime e altere para “false” (3).

Imagem

  • Execute seu pacote.

Imagem

 

e é isso 🙂

 

[]’s

 

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.

SSIS – Operadores Condicionais (IF-THEN-ELSE)

Um recurso muito importante para qualquer desenvolvimento que envolva lógica é a utilização dos condicionais (case, IF, else). Hoje iremos ver como aplicar este tipo de lógica através do integration services.

Primeiro iremos criar uma procedure, que irá retornar uma valor aleatório entre 1 e 4. O retorno desta proc será utilizado para uma tomada de decisão em nossa dtsx.

Crie uma procedure com o seguinte código:


CREATE PROCEDURE DBO.usp_numeroAleatorio(

@retorno tinyint output)

AS BEGIN

DECLARE @NUMERO AS TABLE (NUMERO TINYINT)

INSERT INTO @NUMERO

VALUES (1),(2),(3),(4)

set @retorno = (select  TOP 1 NUMERO FROM @NUMERO ORDER BY NEWID())

END --BEGIN

Agora abra seu Microsoft Visual Studio e crie um novo projeto de integration services em um diretório de sua preferência.

A primeira coisa será criar uma nova conexão com sua base de dados. Clique com o botão direito do mouse na área “Connection Manager” e vá em “New OLE DB Connection” e aponte para o banco de dados onde você criou nossa procedure.

1

Feito isso, vamos criar uma variável que irá armazenar o retorno da nossa procedure. Caso sua aba de variáveis não esteja visível você poderá ir até o menu SSIS > Variables e habilita-la.

Clique em “add new variable” e nomeie-a como “retornoProc” do tipo Int32.

2

Agora adicione um componente “Execute SQL Task” e dê dois cliques sobre ele.

3

Altere a propriedade connection para a conexão que criamos a pouco, a propriedade “resultset” para “sigle row” e o SQL Statement para “


DECLARE @RETORNO int

EXEC usp_numeroAleatorio @RETORNO = @RETORNO OUTPUT

select @retorno as retornoproc

vá até a opção “resultset” e adicione um novo resultSet e na propriedade ResultName coloque “retornoproc” (este nome foi atribuído no retorno da variável contida na SQL Task ).

Image

Vamos adicionar agora 5 componentes script task, todos eles deverão estar configurados da mesma forma:

Image

E com o seguinte código aplicado:

</pre>
public void Main()

{

try

{

MessageBox.Show(Dts.Variables["retornoProc"].Value.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

}

catch (Exception)

{

Dts.TaskResult = (int)ScriptResults.Failure;

throw;

}

}
<pre> 

Obs: este código apenas serve para exibir o valor atual da nossa variável passada como parâmetro de entrada (ReadOnlyVariables)

Faça a ligação entre nosso SQL Task e cada um dos Scripts Task. Agora sim faremos a validação propriamente dita. Dê dois cliques na precedence constraint e altere a propriedade “Evaluation Operation” para “Expression” e na propriedade expression você deverá preencher com o valor adequado a cata possibilidade de retorno da proc.

Image

Em sua tela deverá havel algo parecido com isso agora:

Image

Vamos fazer um teste? Execute a DTS e veja o resultado:

Image

Mais uma vez:

Image

Espero que a dica tenha sido útil.

Abraços.

Dhiego Piroto

SSIS – Utilizando o componente Derived Column

Imagine que temos que realizar a importação de um arquivo de texto com contenha informações como : Nome, Sobrenome e Idade. Porém, quando a idade da pessoa for inferior a 18 anos um campo boolean deverá ser preenchido como True.

Para fazer esse tipo de tarefa pelo SSIS utilizaremos o componente “Derived Column”.

Primeiro vamos criar uma estrutura tabular para armazenar os dados a serem inseridos

<br />use ImportaArquivos<br /><br />go<br /><br />CREATE TABLE tb_derivedColumn(<br /><br />id int identity(1,1) primary key,<br /><br />ch_nome varchar(100),<br /><br />ch_sobrenome varchar(100),<br /><br />bMenorde18 bit<br /><br />)<br /><br />

Agora iremos criar um arquivo de texto que contenha as seguintes informações.

Nome|Sobrenome|Idade

Dhiego|Piroto|23

Joana|D’arc|99

João|Paulo|17

Rodrigo|Dias|10

Salvaremos esse arquivo com o nome de “DerivedColumnImp.txt” em um diretório de sua preferencia.

Agora clique com o botão direito do mouse na área “Connection Managers”  e selecione a opção “new flat file connection…”. Após isso, faça uma referencia ao arquivo que criamos anteriormente, selecionando a primeira linha como cabeçalho.

Image

Clique em “OK” e depois adicione um componente “data flow task” ao projeto, de um duplo clique nele e adicione o componente  “Flat data source” e clique duas vezes nele.

Preencha listbox com a conexão que acabamos de criar (txtCnn), clique em columns e depois “OK”.

Agora adicionaremos o componente que irá atender a nossa regra, e validará a idade da pessoa, insira no projeto um componente “derived column” e faça a ligação entre o flat file e ele.; Após isso, clique 2x no novo componente.

Na aba esquerda, maximize o item “columns” e arraste o campo “idade” para a grid inferior. Após isso, preencha o campo expression com o seguinte código:

Idade > “18” ? 0:1 através esta expressão será validada a idade da pessoa e um valor booleano será retornado.

Image

Adicione um componente OLE DB Destination ao seu projeto, crie uma conexão com o DB onde criamos a tabela (passo 1) .

Image

Clique na aba “columns” e faça o apontamento das colunas e clique em OK.

Image

Execute a DTS.

Image

E confira o resultado no Management Studio.

Image

É isso galera!

Abraços

Dhiego PIroto

SSIS – Importando um arquivo via Script Task

Fala Galera,

Este será o primeiro artigo de uma série que irei fazer sobre dicas do SSIS.

Pretendo toda quarta feita disponibilizar um ou dois pequenos artigos.

Bem, hoje irei mostrar como importar um arquivo para uma tabela usando arquivos de configuração.

Primeiramente iremos criar a estrutura de tabelas que irá armazenar os dados do arquivo importado.


Create database ImportaArquivos

go

use ImportaArquivos

go

Create Table ImportaArquivoDinamico(

nm_id int identity(1,1) primary key,

ch_Nome varchar(100),

ch_sobreNome varchar(100),

dt_DataImportacao datetime default(getdate())

)

Abriremos o SSIS, iremos em New > NewProjetc > Integration Services Project  para criar um novo projeto:

1

Criaremos uma variável que irá contem o caminho do arquivo a ser importado:

2

Adicione uma Script task (aba control flow)

3

De um dois cliques na task e escolha como linguagem o C# 2008 e selecione a variável que criamos como no campo “readOnlyVariables”. Após isso de um clique em “Edit Script” para que possamos adicionar o código.

4

Iremos inserir o seguinte código (comentado)


using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

&nbsp;

/*NameSpace que permite fazer a conex�o com a base SQL*/

using System.Data.SqlClient;

&nbsp;

/*NameSpace que permite fazer importações*/

using System.IO;

&nbsp;

namespace ST_65067a882bb04492b6e7ae064b642b3d.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

&nbsp;

#region VSTA generated code

enum ScriptResults

{

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

};

#endregion

&nbsp;

public void Main()

{

/*Armazena o caminho do arquivo a ser importado em uma vari�vel*/

String Arquivo = Dts.Variables["CaminhoArquivo"].Value.ToString();

&nbsp;

/*String de Conex�o ao DB*/

String SERVIDOR      = ".,1432";

String BANCO_INICIAL = "ImportaArquivos";

String USUARIO       = "usuario";

String SENHA         = "123";

&nbsp;

String connectionString = "Initial Catalog=" + BANCO_INICIAL + ";Data Source=" + SERVIDOR + ";Integrated Security=False;User ID=" + USUARIO + ";Password=" + SENHA + ";";

&nbsp;

try

{

/*Estabelece conexão com o servidor*/

SqlConnection cnn = new SqlConnection();

cnn.ConnectionString = connectionString;

cnn.Open();

/*Percorre cada linha do arquivo*/

foreach (String linha in File.ReadAllLines(Arquivo))

{

&nbsp;

/*Informa com que caracter é realizada a separação de colunas no arquivo*/

String[] inserir = linha.Split('|');

&nbsp;

/*Sobe o arquivo para memória antes de inserir*/

String comando = " insert into ImportaArquivoDinamico (ch_nome,ch_sobrenome) values ";

comando = comando + "('" + inserir[0].ToString() + "' , '" + inserir[1].ToString() +"')";

&nbsp;

/*Executa insert do comando*/

SqlCommand sqlCMD = new SqlCommand(comando, cnn);

sqlCMD.ExecuteNonQuery();

}

/*Fecha conexão*/

cnn.Close();

&nbsp;

/*Finaliza a DTS com sucesso.*/

Dts.TaskResult = (int)ScriptResults.Success;

}

catch(Exception e)

{

/*Finaliza a DTS com falha.*/

Dts.TaskResult = (int)ScriptResults.Failure;

}

}

}

}

&nbsp;

Muito bom! Agora já temos o código de importação pronto iremos configurar o SSIS para receber o nome do arquivo através de um txt.

Clique com o botão direto do mouse em qualquer área  do projeto e vá em propriedades.

Feito isso, localiza o menu abaixou e clique nas reticências.
5

Clique no check Box “Enable package configurations” e depois clique em ADD. Uma nova tela irá se abrir e você poderá escolher qual o tipo de arquivo de configuração você deseja, no nosso caso será um arquivo XML e depois explicite o diretório e nome do arquivo.

Clique em Next.

6

Depois disso uma nova tela irá se abrir e todas as variáveis serão exibidas. Você deverá selecionar a variável que criamos para o nome do arquivo e selecionar a opção “value” e depois clicar em “Finish” e no final “Close”.

7

Agora vá até o diretório em que você mapeou o arquivo de configuração e veja se ele já está na pasta. Se você fez tudo corretamente um arquivo similar a este deverá aparecer:
8

Agora para que possamos testar, você irá alterar a conteúdo da tag  <configuredValue> para um outro arquivo (que obedeça a mesma estrutura).

Após alterar o XML com o arquivo que você deseja, altere a propriedade “value” que está na variável no SSIS para 0 (zero) e execute o programa.

Por hoje é isso galera.

Abraços

Dhiego Piroto

Integration Services – Tips

Fala Galera,

Ultimamente tenho estado bastante envolvido em projetos de importações de dados em massa, sendo para atividades pontuais ou recorrentes através de Jobs. Como eu não tinha muita experiência com a ferramenta de Integration Services da suite de BI do SQL, fora os cursos oficiais de BI que fiz, vou começar a postar algumas dicas do que eu achar que vale a pena durante os dias em que estou envolvido em projetos de integração de dados.

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