Share via

Creating an SSIS .ispac from a .dtsx and a .dtsConfig

Dan Noar 6 Reputation points
2021-01-21T19:11:08.313+00:00

My system is currently on the package deployment model with deployment to the file system for our SSIS jobs, and we are looking to transition to a project deployment SSIS DB model. We have many packages that are all separate from each other (and differ in their variables/parameters), and each has at least one .dtsConfig file associated with it. (If they have multiple, the config files all have the same variables, but different values.)

They are not associated with projects/solutions at all. In the interest of reducing manual intervention as much as possible, I would like to be able to take a .dtsx and a .dtsConfig to create project files (.ispac and project.params) for each package, so that these can be automatically deployed to the Integration Services Catalog (using the procedure SSISDB.catalog.deploy_project). Currently I would have to use Visual Studio to create a new project, import the package, select the config, and convert to project deployment, all manually, so I'm hoping there is a way to automate this step.

I've opened an .ispac file and found it contains the package, project.params, and a project manifest, but I'm not sure which of those are completely necessary, and if there is a way to programmatically create either project.params or the manifest.

SQL Server Integration Services
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Noar 6 Reputation points
    2021-01-22T17:14:50.417+00:00

    I'm aware of that. That is the system that we have now. We want to transition to having the packages on the server in the Integration Services Catalog and run them that way. Since that needs to use the project deployment model, I need to get all of these packages into projects, and I'm hoping to get that done in a script rather than manually.

    Was this answer helpful?

    0 comments
  2. Monalv-MSFT 5,926 Reputation points
    2021-01-22T06:31:59.247+00:00

    Hi @Dan Noar ,

    We can execute ssis package with config file from a .dtsx file and a .dtsConfig file in sql agent job.

    Please refer to the following pictures:

    1. Choose ssis package from file system.
      59494-jobstep-packageformfilesystem.png
    2. Add .dtsConfig file.
      59501-jobstep-configurationsaddconfigfile.png
    3. Check if add config file successfully in Command line.
      59417-jobstep-commandline.png

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.