Criando e Executando um assembly pelo SQL Server

Fala Galera,

Todos sabemos que a linguagem T-SQL possui limitações, alguma vez você já precisou fazer algum processo muito elaborado e acabou esbarrando em alguma delas?

Pois é, saiba que o SQL Server possuí uma integração com linguagens de programação, Visual Basic e C#, o que nos permite utilizar todas suas vantagens.

Vamos inciar criando uma nova base de dados para os exemplos:


CREATE DATABASE EX_ASSEMBLY

GO

USE EX_ASSEMBLY

GO

O Common Runtime Language, por default, não vem habilitada na instancia, portanto será necessário faze-lo antes de iniciarmos.


EXEC sp_configure 'ADVANCED OPTIONS',1

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'clr enabled',1

RECONFIGURE WITH OVERRIDE

Também habilitaremos o CMDShell para que possamos compilar a dll de dentro do SQL (poderia ser realizado pelo prompt de comando)


EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH OVERRIDE

Agora iremos criar uma função em C# bem simples, que irá concatenar uma string passada por parâmetro, a criação poderá ser realizada no notepad e salva com a extensão .cs .


using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class TesteBlogPiroto{

[SqlProcedure]

public static void ConcatenandoTexto(String parametro1){

String parametro = string.Concat("Blog do:", parametro1);

SqlContext.Pipe.Send(parametro);

}

};

Após criarmos o arquivo, que no meu exemplo está na raíz do diretório D:\ , nós precisaremos transforma-lo em uma DLL, para isso será necessário utilizar o comando xp_cmdshell, que nos permite executar comandos shell. Para compilar, precisamos do diretório que o nosso .net framework está instalado (no meu caso a versão usada é a 2).


declare @comando varchar(2000)

set @comando =
'C:\Windows\Microsoft.NET\Framework\v2.0.30319\csc
/target:library
/out:d:\BlogPiroto.dll d:\artigo.cs'

exec xp_cmdshell @comando

Depois de criarmos a dll será necessário importar seu conteúdo para o SQL Server e para isso iremos utilizar a instrução ‘create assembly’:


Create Assembly exBlogPiroto from  'D:\BlogPiroto.dll'
WITH PERMISSION_SET = SAFE

É de extrema importância atentarmos ao permissionamento concedido a um assembly criado, existem três opções:

Safe: O mais restritivo, além de ser a opção default e recomendado pela MS, restringe o acesso do assembly a somente o SQL Server.

UnSafe: Tem acesso irrestrito a todos os recursos da maquina, registro, rede, variaveis do sistema etc.

External_Access: Tem Acesso restrito aos recursos da maquina, pode usar código não gerenciado.

Nao esqueça de ser uma versão suportada pelo SQL, caso contrário o seguinte erro será apresentado

1

Depois de importar o assembly iremos criar uma procedure que fará referência ao método contido na dll.


CREATE PROCEDURE SP_CHAMAASSEMBLY

 (@PARAMETRO NVARCHAR(MAX))

AS EXTERNAL NAME
 EXBLOGPIROTO.TESTEBLOGPIROTO.CONCATENANDOTEXTO

O external name é formado da seguinte forma>  [Nome do Assembly].[Nome da Classe].[Nome do Método]

Agora faremos uma chamada a nossa recém criada procedure passando como parametro o texto a ser concatenado.


EXEC SP_CHAMAASSEMBLY ' DHIEGOPIROTO@GMAIL.COM '

Retorno:

2

Por hoje é isso pessoal!

[]’s

Anúncios

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

"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