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
CodePudding user response：
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.
- Integration Services object model : Building SSIS packages programmatically using ManagedDTS, Building Packages Programmatically
- Ezapi: Building SSIS packages programmatically using EzAPI
To parse an existing SSIS package, you can refer to the following answer: