3

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
user11060917
  • 37
  • 1
  • 8
  • 1
    Hi Kunal, you should be generating the entire package rather programmatically. One easy (in my view) approach would be [BIML](http://microsoft-ssis.blogspot.com/2015/02/creating-biml-script-component.html) – Arthur Mar 06 '19 at 18:30
  • since you tagged the question with c#, i think you may follow the first section of the provided answer – Yahfoufi Mar 07 '19 at 11:38

1 Answers1

0

I totally agree with @Arthur comment, you should build the whole package programmatically. You have two approaches to create packages programatically:

(1) Build SSIS using C#

If you are familiar with C# you have two choices:

(a) Using SQL Server Client SDK assemblies

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) Using EzApi – Alternative package creation API

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

There are many link on the internet that you can refer to such as:

(2) Build SSIS using BIML

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.

There are many links that you can refer to learn BIML:

Update 1 - C# approach

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:

You can follow this Wiki page for a step-by-step guide:

You can use this library to read from json and import into SQL (without changing the schema) with a few lines of code.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • @Kunal at the end if you agreed with this answer accept it or if you find another solution write it as your own answer – Hadi Mar 07 '19 at 18:17
  • No, this is not the pin point answer I need answer which direct addresses the issue. Generating Cade based component is not the answer I am ok with. There had to be a better way that’s what I believe. – user11060917 Mar 08 '19 at 10:57
  • @Kunal it will be very interesting if you find another approach. Don't hesitate to share such information. Hope you will find a better solution. Good luck – Hadi Mar 08 '19 at 16:50
  • @Kunal not that i am not asking to accept my answer if it doesn't fits your need, i leaved this comment since you have recently registered in SO. – Hadi Mar 08 '19 at 18:07
  • Well I believe Microsoft should think about it, solution should be simple writing whole lot of code for just this is not justified that is a simple requirement and should be available built in by Microsoft other components of SSIS have it where it allows to copy and paste from visual studio to excel and vice-versa. – user11060917 Mar 09 '19 at 07:37
  • Hadi and everyone answering me I do respect all your suggestions and want to find out best possible way of doing it so will update this post in sometime. If I get anything. – user11060917 Mar 09 '19 at 09:04
  • @Kunal are you familiar with c#? – Hadi Mar 09 '19 at 09:16
  • I am a bit new to C# not very proficient as of now. – user11060917 Mar 09 '19 at 12:27
  • sure let me check. Thank you. – user11060917 Mar 09 '19 at 12:29