locked
config file RRS feed

  • Question

  • Ive created a package that uses a config file.  In the design wizard Ive specified the location of the config file to be  C:\ConfigFiles

    How can I programmatically specify the location of the file at runtime ?  Id want the config file to be in the same directory as the package so that in production only the SSIS package and config file need to be deployed.  So what Id need is some code that specifies the config location as the current directory the SSIS package is in.  Anyone help ?
    Tuesday, January 5, 2010 11:00 AM

Answers

  • Good i understand, i am doing the same thing in my script as you can see, my customer will define the folder (where all the ETLs are) and i just simply put that folder in a variable @MainFolderPath and copy my ETL packages and cofing file to that folder (i do make a sub folder for that \PackageAndConfigFile\)
    so finally when the script is been deployed (run in SSIS) it will know where the package and the config, (and in my case sample source files (for excel) and error folders , documents and backup) folders are

    i think that you are on the right path

    You mentioned ....
    Q: Id like to make the package as 'self contained' as possible so ideally I'd like to  programmatically specify the location of the config file in the package itself
    A: can you give a an example


    Q: So what I need is some code that would determine the full path of the directory that the package is in, then use this path to load up the config file
    A: As far as i know you can't do this in the package, specially at run time

    Q: and finally tell the package to use this config file
    A: if you have the package set as a job you dont need to do that , just fire off the job, because in the Job you have set the config files and etc....

    Q: That way, all I have to do to deploy to production is copy the package and config file to the server and create the job.
    A: if you have them in your Job script, it will do the job for you , and you can have a junior DBA or programmer do the deployment for you.

    What i do is ...
    1- we have standards in folder names , file names , how a package is called , Jobs , Job scripts , security as in the user, or job owner, access to folders and etc...
    2- make your self a standard of deployments, ( my case copy all fole and folders to the production ETL folder, run one or few scripts, that one of them is to set the job for that ETL.
    3- make the deployment steps small and easy, so that some junior programmer can take over the deployments for you , on one production server i have over 100 packages i only deployed 3 or 4 of them

    good luck


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Wednesday, January 6, 2010 2:38 PM

All replies

  • I always use a environment variable when using config file like described here: http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.html

    Jorg Klein's Microsoft Business Intelligence Blog

    • Proposed as answer by Justin Lezon Tuesday, January 5, 2010 1:47 PM
    Tuesday, January 5, 2010 12:08 PM
  • Hi,

    You could use the dtexec windows command to specify the config file at run time , the command would look something like:

    dtexec /f "packagepath.dtsx" /Config "configfilepath"

    However, while deployment of SSIS packages to production, if you are using manifest file then the wizard will prompt you for the location of the config file.
    Raju
    Tuesday, January 5, 2010 1:41 PM
  • is there no way to specify the config file location using a script task ?  Id delpoy the config file along with the package in the same directory (on production) The code could then just load up the config file from the same directory.  This must be possible, Im just not sure how to start
    Tuesday, January 5, 2010 4:42 PM
  • is there no way to specify the config file location using a script task ?  Id delpoy the config file along with the package in the same directory (on production) The code could then just load up the config file from the same directory.  This must be possible, Im just not sure how to start


    i am assuming that finally you will be setting your Package in a Job with in SQL Server
    what i do is that i have a script that sets the config file to the package, and the script basically makes mu job and sets the config file
    here is a part of the script

    SET @JobCommand = 'DTEXEC '
     SET @JobCommand = @JobCommand + N' /FILE ' + @MainFolderPath + '\PackageAndConfigFile\PkgETL-SSIS-PkgName.dtsx '
     SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlDestinationDBConfig-.dtsConfig '
     SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlFolderConfig.dtsConfig '
     SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlMailConfig.dtsConfig '
     SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlSourceDBConfig.dtsConfig '
    .
    .
    .
    .
    .

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run ETL Package IntSer_ETL', 
       .
       .
       @os_run_priority=0, @subsystem=N'CmdExec',   
       @command=@JobCommand,   


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Tuesday, January 5, 2010 6:45 PM
  • I will be running the package as a scheduled job within sql server.  Id like to make the package as 'self contained' as possible so ideally I'd like to  programmatically specify the location of the config file in the package itself .  So what I need is some code that would determine the full path of the directory that the package is in, then use this path to load up the config file, and finally tell the package to use this config file.  That way, all I have to do to deploy to production is copy the package and config file to the server and create the job.
    Wednesday, January 6, 2010 9:20 AM
  • Good i understand, i am doing the same thing in my script as you can see, my customer will define the folder (where all the ETLs are) and i just simply put that folder in a variable @MainFolderPath and copy my ETL packages and cofing file to that folder (i do make a sub folder for that \PackageAndConfigFile\)
    so finally when the script is been deployed (run in SSIS) it will know where the package and the config, (and in my case sample source files (for excel) and error folders , documents and backup) folders are

    i think that you are on the right path

    You mentioned ....
    Q: Id like to make the package as 'self contained' as possible so ideally I'd like to  programmatically specify the location of the config file in the package itself
    A: can you give a an example


    Q: So what I need is some code that would determine the full path of the directory that the package is in, then use this path to load up the config file
    A: As far as i know you can't do this in the package, specially at run time

    Q: and finally tell the package to use this config file
    A: if you have the package set as a job you dont need to do that , just fire off the job, because in the Job you have set the config files and etc....

    Q: That way, all I have to do to deploy to production is copy the package and config file to the server and create the job.
    A: if you have them in your Job script, it will do the job for you , and you can have a junior DBA or programmer do the deployment for you.

    What i do is ...
    1- we have standards in folder names , file names , how a package is called , Jobs , Job scripts , security as in the user, or job owner, access to folders and etc...
    2- make your self a standard of deployments, ( my case copy all fole and folders to the production ETL folder, run one or few scripts, that one of them is to set the job for that ETL.
    3- make the deployment steps small and easy, so that some junior programmer can take over the deployments for you , on one production server i have over 100 packages i only deployed 3 or 4 of them

    good luck


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Wednesday, January 6, 2010 2:38 PM