Usando Foreach Loop Container e Variáveis para extrair informações de sites/listas diferentes do SharePoint usando Integration Services

Autor:	Thiago Silva
Publicação:	09/Nov/10

Overview

Recentemente escrevi um artigo de como podemos extrair informações do SharePoint e inserindo em tabelas no SQL Server, usando a API do próprio SharePoint (https://thiagottss.wordpress.com/2010/10/28/cs21013-extraindo-informaes-de-listas-no-sharepoint-e-carregando-em-tabelas-no-sql-server-usando-integration-services/).

Agora, o cenário outro. Queremos extrair informações de vários sites e/ou listas dinamicamente usando variáveis. No meu caso, vou alimentar essas variáveis com valores de outra tabela no SQL Server, por exemplo, uma tabela de URLs. Assim, tenho diversas listas replicadas em vários sites, e quero, para cada URL encontrada na tabela de URLs, trocar a URL do componente SharePoint List Source em runtime e extrair as informações das várias encontradas em cada site.

Solução

Para essa solução vou trabalhar com os seguintes produtos e tecnologias:

· Windows 7 Enterprise;

· Microsoft SharePoint 2010 Server;

· SQL Server 2008 Enterprise;

· SQL Server 2008 Integration Services – SSIS;

· SQL Server Business Intelligence Development Studio;

· SharePoint List Source and Destination (http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652).

A lógica não tem muito segredo. Só precisamos entender um pouco como podemos utilizar as ferramentas presentes no SSIS. Para isso, vamos utilizar um Foreach Loop Container.

Este artigo assume que você já tem um DataFlow Task para extrair informações do SharePoint. Caso ainda não o tenha, siga os passos deste post para alcançar este ponto: https://thiagottss.wordpress.com/2010/10/28/cs21013-extraindo-informaes-de-listas-no-sharepoint-e-carregando-em-tabelas-no-sql-server-usando-integration-services/.

Estrutura:

· Crie uma tabela que terá as informações que você quer trocar em runtime. No meu caso, vou utilizar uma tabela simples, contendo URLs.

1. Lendo as informações da tabela de URLs e armazenando em uma variável

1.1. No Control Flow, no ambiente de desenvolvimento do pacote SSIS, clique e arraste do Toolbox um Execute SQL Task.

1.2. Clique duas vezes sobre a task recém-criada. O Editor irá abrir, mude o ResultSet para Full result set, crie ou utilize uma conexão existe para acesso ao banco que possui a tabela de URLs, em Connection.

1.2.1. A mudança do ResultSet faz com que possamos utilizar o resultado e armazenar em uma variável. Caso contrário, não seria possível.

1.3. Em SQLStatement, digite o comando para trazer as URLs contidas na tabela. No meu caso “select url from dbo.URLs”.

1.4. No menu da esquerda, selecione Result Set, depois clique em Add.

1.5. Em Result Name, digite 0 (zero). Em Variable Name, crie uma nova variável do tipo Object.

1.6. Clique Ok para fechar o Editor.

Neste ponto configuramos a operação que irá buscar as URLs contida na tabela de URLs no SQL Server. Agora iremos criar um repetidor para utilizar essa variável para contar iterações serão necessárias, ou seja, quantas vezes irá rodar alguma operação específica, e além disso, manter o valor atual de cada iteração em outra variável.

2. Criando a estrutura de repetição utilizando a variável recém-criada

2.1. Clique e arraste, do Toolbox, um Foreach Loop Container para o design do pacote em Control Flow.

2.2. Clique duas vezes sobre a estrutura de repetição Foreach, e clique em Collection.

2.3. Em Enumarator, selecione Foreach ADO Enumerator.

2.4. Em Enumator configuration, selecione a variável que criamos no passo anterior em ADO object source variable, e mantenha Enumeration mode em Rows in the first table.

2.4.1. Essas configurações servem para indicarmos que iremos utilizar a variável que criamos, que contém todas URLs que iremos trabalhar, e que o número de iterações será igual ao número de linhas contidas na tabela armazena na variável (resultado de “select url from dbo.URLs”).

2.5. No menu da esquerda, clique em Varialbe Mappings, clique em Variables e crie uma nova variável do tipo String para armazenar a url atual de iteração.

2.5.1. Deixe um valor padrão na variável, para evitar um erro de validação do componente SharePoint List Source.

2.6. Clique Ok, para fechar o Editor.

2.7. Clique na tarefa criada anteriormente, e arrasta a seta verde para ligar os dois passos.

2.8. Agora, adicione um Data Flow Task dentro do Container. Não vou seguir os passos para criar um, reveja o post mencionado no topo deste artigo. Mas, a estrutura é similar a esta, como a imagem abaixo.

2.9. Volte para Control Flow. Vamos trocar o valor de SiteUrl, em runtime, usando a variável atual de cada iteração.

2.10. Nas propriedades de Data Flow Task, clique em Expressions, clique em (…), em Property, selecione [SharePoint List Source].[SiteUrl].

2.11. Em Expression, clique (…), expanda Variables, localize a variável User::URLatual, e arraste para Expression. Clique Ok para fechar.

Pronto. Configuramos todos os passos. Clique F5, para testar!

Conclusão

Nesse artigo mostrei como automatizar e atualizar dinamicamente valores usando variáveis para extrair informações do SharePoint.

Extraindo informações de listas no SharePoint e carregando em tabelas no SQL Server usando Integration Services

Autor:	Thiago Silva
Publicação:	28/Out/10

Overview

Constantemente encontramos situações onde precisamos extrair as informações do SharePoint para tratar, analisar e mostrar algo.

Já vi situações que usuários acessam os dados do SharePoint diretamente no Banco de Dados. Esse procedimento não é recomendado, pois a Microsoft não garante suporte ao produto se acessamos os dados diretamente na base de dados do SharePoint. Para isso, existem as APIs e afins.

Recentemente, montei um projeto de BI, usando as informações das listas do SharePoint, para montar indicadores, dashboards e tudo mais. Mas, como fazer isso usando as listas do SharePoint?

Utilizei os recursos do SQL Server, mais precisamente o Integration Services (SSIS), para extrair as informações do SharePoint e jogar em tabelas criadas no SQL Server, em uma instância e um Banco de Dados separado para isso. Assim, também ajudamos para que a performance das outras aplicações do SharePoint não sofram tanto.

Para utilizar o SSIS e extrair as informações do SharePoint, precisaremos de um add-in disponível no CodePlex (http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652). Faça o download, instale e adicione o add-in no Toolbox como sugerido a seguir.

Solução

Para essa solução vou trabalhar com os seguintes produtos e tecnologias:

· Windows 7 Enterprise;

· Microsoft SharePoint 2010 Server Enterprise;

· SQL Server 2008 Enterprise;

· SQL Server 2008 Integration Services;

· SQL Server Business Intelligence Development Studio;

· SharePoint List Source and Destination (http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652)

1. Instalando o SSIS SharePoint List Adapters

a. Faça download do executável no link mencionado acima.

b. Rode o instalador e prossiga com a instalação.

2. Adicione o List Source (puxar informação do SharePoint) e o List Destination (inserir informação no SharePoint) ao Toolbox do Business Intelligence Development Studio.

a. Abra o Business Intelligence Development Studio e crie um novo Projeto do Integration Services.

b. Clique em DataFlow, depois clique no link no meio da tela para criar um Data Flow Task.

c. No menu Tools, selecione Choose Toolbox Items para adicionar os novos itens.

d. Na tab SSIS Data Flow Items, selecione SharePoint List Destination e SharePoint List Source marcando os checkbox’s.

e. Para organizar melhor, arraste os controles que estão em General para Data Flow sources e Data Flow Destinations respectivamente.

3. Criando uma lista customizada no SharePoint para testes com a integração do SSIS.

a. Vá até o site desejado, clique em All Site Contents, e depois em Create para criar uma nova lista. Escolha uma Custom List para facilitar a criação de colunas. Clique Ok, após dar um nome para sua lista.

b. Insira alguns itens na lista para que algo seja retornado quando rodarmos o pacote no SSIS.

4. De volta ao Business Intelligence Development Studio.

a. Em Data Flow, selecione SharePoint List Source no Toolbox e arraste para a area de Design do Data Flow.

b. Clique duas vezes para editar as configurações, e apontarmos para a lista desejada.

i. A janela do Advanced Editor for SharePoint List Source deve aparecer para editarmos as configurações.

ii. Em Custom Properties, localize SiteUrl e SiteListName. Insira as informações de acordo com seu ambiente. No meu caso, http://notebook01/ e Lista Teste SSIS respectivamente.

NOTA: Perceba que podemos customizar muitas outras opções, como por exemplo, inserir uma Query CAML para trazer apenas itens baseados em certo filtro e/ou ordenado por alguma coluna específica.

iii. Nas tabs Column Mappings e Input and Output Properties, você pode customizar quais colunas deseja trazer da lista e também criar próprias colunas e mapeá-las de acordo com a necessidade.

5. Criando uma tabela no SQL Server para receber os dados da lista.

a. Clique no menu View, depois em Server Explorer.

b. Clique com o botão direito em Data Connections, em seguida em Add Connection.

c. Insira o nome do seu servidor de banco de dados (caso ele possua instâncias diferentes, como no meu caso, insira também o nome da instância). E mais abaixo, selecione o nome do seu banco de dados onde iremos criar a tabela que irá receber as informações da lista.

d. Clique em Test Connection para testar sua conexão com o Banco de Dados, e depois clique em Ok.

e. Expanda a conexão que você acabou de criar. Expanda Tables, clique com o botão direito e selecione Add New Table.

f. Crie duas colunas: uma “ID”, do tipo int, para armazenar o ID do item do SharePoint, e outra coluna “Nome”, do tipo nvarchar(255), para armazenar o campo Title do item do SharePoint.

g. Em Propierties, da tabela, coloque um nome para sua tabela. No meu caso, coloquei algo similar ao nome da Lista no SharePoint. Então, ficou assim: “Table_Teste_SSIS”.

6. Inserindo as informações extraídas da Lista do SharePoint para a tabela no SQL Server.

a. No Toolbox, arraste o item OLE DB Destination para o Design do Data Flow task.

b. Clique no SharePoint List Source, e arraste o conector (output) para o OLE DB Destination (input).

c. Clique duas vezes sobre OLE DB Destination para configurar os mapeamentos.

i. O OLE DB Destination Editor deve aparecer. Clique em New no OLE DB connection manager para criar uma nova conexão, que será utilizada por este controle.

ii. Em Configure OLE DB Connection Manager, clique em New.

iii. Configure a conexão com o seu Banco de Dados, como feito anteriormente.

iv. Clique Ok, e Ok novamente para voltar ao OLE Destination Editor. De volta ao Editor, escolha a tabela que acabamos de criar.

v. Depois clique em Mappings, para mapear as colunas que queremos.

vi. Veja que a coluna ID já teve seu mapeamento feito automaticamente por que elas tem o mesmo nome na Lista do SharePoint e na Tabela no SQL. Mas a coluna Title e Nome, não foram mapeadas. Por tanto, vamos fazê-lo agora.

vii. Clique no Input Column abaixo de ID, e seleciona Title. Depois, clique em OK.

d. Agora, clique duas vezes sobre o conector (linha verde) para adicionar um “viewer” para visualizar o que está vindo do SharePoint.

i. O Data Flow Path Editor irá aparecer. Clique Data Viewers, clique em Add. Depois clique em Ok, e Ok novamente pare fechar todos os Editor’s.

e. Pronto! Agora vamos pressionar F5 para iniciar a extração dos dados.

f. O Data Viewer vai aparecer para visualizarmos as informações que vieram da lista do SharePoint. Pressione o “play” para continuar e jogar as informações na tabela do SQL.

g. Sucesso!

h. No Server Explorer clique com o botão direito na tabela, e selecione Show Table Data.

i. As informações da nossa lista estão agora na tabela no SQL Server!

Conclusão

Nesse artigo mostrei como podemos usar funcionalidades do SLQ Server para extrair dados do SharePoint de forma estruturada e aproveitando-se das API’s do SharePoint para isso, já que não é recomendado acessar diretamente as informações do SharePoint no seu banco de dados.

Benefícios na utilização do SharePoint List Source and Destination:

· Somente os campos que você quer são retornados do SharePoint.

· Listas grandes não são transferidas de uma única vez. Elas são paginadas em batches, com a configuração de tamanho dos batches. Por padrão, são 1000 itens por vez.

· Informação dos tipos de coluna do SharePoint é usada para o mapeamento nos tipos de dados dentro do Integration Services.

· Queries CAML podem ser adicionadas para realizar filtros nas linhas que serão retornadas da lista.