SSIS Config File Query

  • Hello everyone,

    I have created a SSIS package that runs daily and it downloads an XML file, reads its contents, modify some values  and import the data to MySQL database table.

    After that it exports some of this data into a CSV file. I have predefined the file path where this resulting file would be saved. It saves here: D:\CSV FILES\new.csv

    This process runs daily at a specified schedule as SQL server agent job.

    I am using Package deployment model and also using Package Configurations. I have created an xml config file (.dtsConfig). It carries all the necessary variables, which can be modified manually before executing the SSIS package or before Agent job's time.

    Now lets say I have deployed this SSIS package to a client's computer. The client says that he wants to select the resulting file location every time. One option is that I can explain client how to modify the config file daily and he can change the location daily in the file but this option can be considered not very user-friendly.

    I am looking for a solution, may be a HTML form that would let users select the destination path for the resulting file by browsing to a specific location, and after user clicks on submit or save button, that location will be saved in that config file and then this path will be used in the ssis package.

    Is this approach valid? Can there be any other better solution?

  • Assuming the package is deployed to SSISDB, it is possible to execute it by calling a stored procedure.

    Stored procedures can be called from a SQLCMD command line and parameters can be passed, one of which could be the target file location (which is then passed as a package parameter when creating the package execution).

    See https://learn.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-ver16

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Assuming the package is deployed to SSISDB, it is possible to execute it by calling a stored procedure.

    Stored procedures can be called from a SQLCMD command line and parameters can be passed, one of which could be the target file location (which is then passed as a package parameter when creating the package execution).

    See https://learn.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-ver16

    I am using File System Deployment method to deploy this ssis package.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply