如何以编程方式管理SSIS脚本组件输出列及其属性 [英] How to manage SSIS script component output columns and its properties programmatically
问题描述
我正在SSIS VS 2017中开发一个源，该源使用脚本Component调用REST API并序列化JSON，并提供我存储在SQL Table中的记录. 在设计时，我需要手动添加200多个输出列，并为每个列手动设置属性(如数据类型和长度)，这非常繁琐. 有谁能建议我如何使用程序添加所有这些列并使用代码设置属性，或者我只是创建一个excel并导入它，然后在一秒钟内完成就可以了. 请帮助集思广益，并提出一些想法VSTA是一种选择，但我不知道该怎么做?从哪里开始！ 谢谢， 库纳尔
I am developing a source in SSIS VS 2017 which calls REST API using script Component and serialises JSON and provides records which I am storing in SQL Table. While designing I need add output columns which are 200+ manually and set properties like data type and length manually for each and every column which is very tedious. Can any please suggest how can I add all those columns using a program and set properties using code or may be I just create one excel and just import it and it’s done in one second. Please help brainstorm and suggest ideas VSTA is one option but I don’t know how to do it? Where to start from!! Thank you, Kunal
推荐答案
我完全同意@Arthur的评论，您应该以编程方式构建整个程序包.您可以通过两种方法以编程方式创建程序包:
如果您熟悉C#，则有两种选择:
If you are familiar with C# you have two choices:
(a)使用SQL Server客户端SDK程序集
这是通过编程方式创建ssis软件包的官方传统方法，您可以参考Internet上的许多链接，例如:
This is the official traditional way to create ssis packages programmatically, there are many link on the internet that you can refer to such as:
- Creating a Package Programmatically
- Samples for creating SSIS packages programmatically
- Creating and Executing an SSIS Package Programmatically
- Building Packages Programmatically
(b)使用EzApi –替代软件包创建API
EzAPI是一个由Evgeny Koblov用C#编写的.NET库，它是SSIS团队的测试人员之一，它抽象了许多直接用编程语言创建SSIS包XML所需的繁琐的低级编码
EzAPI is a .NET library written in C# by Evgeny Koblov one of the testers on the SSIS team to abstracts away a lot of the cumbersome low-level coding needed to create SSIS packages XML directly in a programming language
- GitHub-EzApi2016
- NuGet Gallery-EzApi
- GitHub - EzApi2016
- NuGet Gallery - EzApi
- EzAPI – Alternative package creation API
- EzAPI Overview
- EzApi a C# library for creating SSIS packages programatically
- Getting Started with EzAPI
- One Learning Path for Biml
- Introduction to Business Intelligence Markup Language (BIML) for SSIS
您可以参考互联网上的许多链接，例如:
There are many link on the internet that you can refer to such as:
如果您熟悉XML，则应使用BIML(商业智能标记语言)，这是Varigence开发的一种标记语言，可以将其集成到Visual Studio中以创建软件包，而无需编码经验.
If you are familiar with XML, then you should use BIML (Business Intelligence Markup Language) which is a markup language developed by Varigence and can be integrated within Visual Studio to create packages without the need of coding experience.
您可以参考许多链接来学习BIML:
There are many links that you can refer to learn BIML:
最近，我在Git-Hub上启动了一个新项目，该项目是使用C#开发的类库.您可以使用它来使用架构映射方法将Excel，Word，PowerPoint，文本，CSV，HTML，JSON和xml中的表格数据导入具有不同架构定义的SQL Server表中.在以下位置查看:
Recently i started a new project on Git-Hub, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into SQL server table with a different schema definition using schema mapping approach. check it out at:
您可以按照以下Wiki页面获取逐步指南:
You can follow this Wiki page for a step-by-step guide:
您可以使用此库从json读取并导入到SQL (无需更改架构)中，只需几行代码.
You can use this library to read from json and import into SQL (without changing the schema) with a few lines of code.
这篇关于如何以编程方式管理SSIS脚本组件输出列及其属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
