Com base em minha análise, um modelo dimensional completo para nosso data warehouse exigirá a extração de mais de 200 tabelas de origem. Algumas dessas tabelas serão extraídas como parte de um carregamento incremental e outras serão um carregamento completo.
Para observar, temos cerca de 225 bancos de dados de origem, todos com o mesmo esquema.
Pelo que vi, criar um fluxo de dados simples no SSIS com uma origem OLE DB e um destino OLE DB requer que as colunas e os tipos de dados sejam determinados em tempo de design. Isso significa que acabarei com mais de 200 fluxos de dados apenas para a extração.
De uma perspectiva de manutenção, isso me parece um grande problema. Se eu precisasse fazer algum tipo de alteração abrangente no código de extração, teria que modificar 200 fluxos de dados diferentes.
Como opção alternativa, escrevi um pequeno script que lê os bancos de dados de origem, nomes de tabelas e colunas que desejo extrair de um conjunto de tabelas de metadados. O código é executado em vários loops e usa SQL dinâmico para extrair das tabelas de origem por meio de um servidor vinculado e OPENQUERY.
Com base em meus testes, isso ainda não é tão rápido quanto usar um fluxo de dados SSIS com origem e destino OLEDB. Então, estou me perguntando que tipo de alternativas eu tenho. Os pensamentos até agora incluem:
- Usando EZAPI para gerar pacotes SSIS programaticamente com fluxo de dados simples. As tabelas e colunas a serem extraídas viriam das mesmas tabelas de metadados mencionadas anteriormente.
- Adquira software de terceiros (componente de fluxo de dados dinâmico)
Qual é a melhor maneira de abordar isso? Quando se trata de programação .NET, sou iniciante, portanto, o tempo necessário para aprimorar apenas o básico também é uma preocupação.
Eu não gostaria de ter 200 fluxos de dados em um único pacote. O tempo que levaria apenas para abrir e validar faria você envelhecer antes do tempo.
EzAPI é divertido, mas se você é novo em .NET e SSIS, oh inferno não, você não quer isso. Acho que você gastará muito mais tempo aprendendo sobre o modelo de objeto SSIS e possivelmente lidando com COM do que realmente trabalhando.
Como sou preguiçoso, incluirei o BIML como uma opção gratuita que você não listou. De uma resposta em SO https://stackoverflow.com/questions/13809491/generating-several-similar-ssis-packages-file-data-source-to-db/13809604#13809604
Acho que pode ser uma abordagem para você também. Você define seu BIML que descreve como seus pacotes devem se comportar e depois os gera. No cenário que você descreve onde você faz uma alteração e precisa corrigir N pacotes, não, você corrige sua definição do problema e regenera os pacotes.
Ou, se você ganhou familiaridade suficiente com a estrutura, use algo como EzAPI para consertar todas as coisas quebradas. Caramba, já que você marcou isso como 2005, você também pode experimentar o PacMan se precisar fazer modificações em massa nos pacotes existentes.
Considerações de design do SSIS
De um modo geral, tento fazer com que meus pacotes se concentrem em resolver uma única tarefa (carregar dados de vendas). Se isso requer 2 fluxos de dados, que assim seja. O que eu odeio herdar é um pacote do assistente de importação e exportação com muitos fluxos de dados não relacionados em um único pacote. Decomponha-os em algo que resolva um problema muito específico. Isso torna os aprimoramentos futuros menos arriscados, pois a área de superfície é reduzida. Um benefício adicional é que posso trabalhar no carregamento
DimProductsenquanto meu lacaio está lidando com o carregamento doSnowflakeFromHellpacote.Em seguida, use o(s) pacote(s) mestre(s) para orquestrar os fluxos de trabalho filhos. Eu sei que você está em 2005, mas o lançamento do SQL Server 2012 do SSIS é o pijama do gato. Adoro o modelo de implantação do projeto e a forte integração que ele permite entre os pacotes.
TSQL vs SSIS (minha história)
Quanto à abordagem TSQL pura, em um trabalho anterior, eles usaram um trabalho de 73 etapas para replicar todos os dados do Informix no SQL Server. Geralmente levava cerca de 9 horas, mas podia chegar a 12 ou mais. Depois que eles compraram um novo SAN, ele caiu para cerca de 7+ horas. O mesmo processo lógico, reescrito no SSIS, foi consistente em menos de 2 horas. Facilmente, o maior fator para reduzir esse tempo foi a paralelização "gratuita" que obtivemos usando o SSIS. O trabalho do Agente executou todas essas tarefas em série. O pacote mestre basicamente dividiu as tabelas em unidades de processamento (5 conjuntos paralelos de tarefas serializadas de "executar replicar tabela 1", tabela 2, etc.) onde tentei dividir os baldes em unidades de trabalho de tamanho quase igual. Isso permitiu que as cerca de 60 tabelas de referência de pesquisa fossem preenchidas rapidamente e, em seguida, o processamento desacelerou quando entrou no "
Outras vantagens para mim ao usar o SSIS é que recebo configuração "gratuita", registro e acesso às bibliotecas .NET para dados quadrados que preciso colocar em um buraco redondo. Acho que pode ser mais fácil manter (passar pela manutenção) um pacote SSIS do que uma abordagem TSQL pura em virtude da natureza gráfica da besta.
Como sempre, sua milhagem pode variar.
Você mencionou que tem 200 tabelas de origem e 225 bancos de dados. Estou assumindo que as 200 tabelas de origem são uma contagem de todas as tabelas de todos os 225 bancos de dados (porque se você tivesse 200 tabelas em cada banco de dados, isso colocaria sua contagem total de tabelas em 45.000). Você também mencionou que o esquema do banco de dados é o mesmo para os 225 bancos de dados.
Você pode criar os pacotes SSIS para apenas 1 banco de dados primeiro e, quando agendar seus trabalhos, pode apenas alterar a string de conexão do banco de dados usando a configuração do pacote (se o seu SQL 2005, você usará o modelo de implantação do pacote). Conforme mencionado nas respostas anteriores, o SQL 2012 tem novas maneiras de configurar seus parâmetros usando o modelo de implantação do projeto.
Você pode obter mais informações sobre configuração de pacotes com SSIS aqui http://www.sql-server-performance.com/2007/package-configuration-2005/
Você pode obter mais informações sobre como usar os parâmetros do projeto aqui, https://stackoverflow.com/questions/15206184/how-to-configure-ssis-2012-project-to-run-under-different-environment-configurat