1

I have some SSIS packages in the form of templates with some variables, and I would like to automate my ETL tables. I cannot use BIML because it is not in the VS marketplace, and I cannot download it from the internet.

My variables consist of destination table name and source table name etc. I use a PowerShell script to change the values of the variables and the CREATE TABLE statement to create the destination table.

My problem is that I need to press the "mapping" tab in the Data Flow Task. Is there a way to automate this without opening the solution?

I have looked at the XML for the .dtsx file using git to see what changes after pressing "mapping", but it seems too complicated to insert this extra XML in the middle of the file...

Also, I would like to use another tool, e.g., Python, to do ETL, but we are not allowed...

So I would like to have a ready package without opening it.

Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
xhr489
  • 1,957
  • 13
  • 39
  • 1
    If Biml is not available, I think the only way is to use a C# script. If it is allowed I can provide more information – Hadi Feb 18 '22 at 23:03
  • @Hadi: yes C# is allowed, so it would be great with some help in that direction. – xhr489 Feb 18 '22 at 23:09

1 Answers1

1

Since Biml is not allowed, the only way (as I know) to automate SSIS packages is to use C# libraries. You can simply use the Integration Services object model assemblies or an open-source project such as EzApi and Pegasus.

To parse an existing SSIS package, you can refer to the following answer:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks, well I will have lot of frustration ahead... ManagedDTS assembly: does that come with a SQL Server installation or SSDT? Also what do you recommend: ManagedDTS or EzAPI? – xhr489 Feb 18 '22 at 23:51
  • 1
    ManagedDTS assemblies are installed with SQL Server. I recommend using ManagedDTS since all open-source projects are built on top of it. Besides, it will give you a better understanding of SSIS packages. You can start from the following tutorial: https://learn.microsoft.com/en-us/sql/integration-services/building-packages-programmatically/building-packages-programmatically?view=sql-server-ver15 – Hadi Feb 19 '22 at 00:01
  • Thanks again. Irritating the examples in the documentation are using VB instead of C#... I don't have much experience with either. I guess one could also use ManagedDTS from PowerShell, right? – xhr489 Feb 19 '22 at 00:15
  • 1
    @xhr489 I am not sure if this can be done using PowerShell. I didn't try it previously – Hadi Feb 19 '22 at 00:17
  • In one of the articles to link to you write "In SQL Server 2019, they are located in the global assembly cache with the .NET framework assemblies." I cannot find this. Is there anything specific I have to check in the installation of SQL Server (in Installation Center - setup). I have added integration Services, but without "scale out master" and "Scale out worker"... So I am still trying to load the assembly... – xhr489 Feb 20 '22 at 11:06
  • Hi never mind, I found the answer. – xhr489 Feb 20 '22 at 12:17
  • 1
    @xhr489 I think they are stored in the SQL Server Installation directory (search for the `DTS` folder as I remember) – Hadi Feb 20 '22 at 12:24
  • Hi I have posted a new question [link](https://stackoverflow.com/questions/71397060/pressing-mappings-in-the-data-flow-task-using-net). Can you have a look? – xhr489 Mar 08 '22 at 17:05
  • 1
    @xhr489 I will check it in a while. – Hadi Mar 08 '22 at 17:39