如何以编程方式管理 SSIS 脚本组件输出列及其属性 [英] How to manage SSIS script component output columns and its properties programmatically
问题描述
我正在 SSIS VS 2017 中开发一个源，它使用脚本组件调用 REST API 并序列化 JSON 并提供我存储在 SQL 表中的记录.在设计时，我需要手动添加 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包的传统方式，互联网上有很多链接可以参考，例如:
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:
(b) 使用 EzApi – 替代包创建 API
EzAPI 是一个 .NET 库，由 SSIS 团队的测试人员之一 Evgeny Koblov 用 C# 编写，用于抽象掉许多繁琐的低级编码，这些代码需要直接用编程语言创建 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
互联网上有很多链接可以参考，例如:
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、text、csv、html、json 和 xml 导入到具有不同架构定义的 SQL 服务器表中.在以下位置查看:
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屋！
