none
Managing connections and passwords in SSIS RRS feed

  • Question

  • How to manage the SSIS connections used in packages?

    Is it possible managing the connections between the environments like DEV to UAT and PROD.

    Thanks in advance.

    Friday, December 14, 2012 3:40 AM

Answers

All replies

  • You can define the package configuration:

    http://msdn.microsoft.com/en-us/library/cc895212.aspx

    http://toddchitt.wordpress.com/2008/06/27/ssis_config/

    http://sqlblog.com/blogs/eric_johnson/archive/2009/09/21/using-ssis-package-configurations.aspx

    Regards,Eshwar.


    --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Friday, December 14, 2012 4:14 AM
  • There are several ways to manage the connections of the SSIS package,

    simple one is you can use the XML configuration file to manage the connection managers, you can explicitly change the connection strings and connections from the help of XMl File.

    Just right click on the contro flow then select package configurations and then which ever you want say XML Configuration, configure the path and then you can configure which ever properties you want to configure.

    Please mark it as helpful if it helps.

    Thanks

    Sumit

    Friday, December 14, 2012 6:17 AM
  • Besides the XML configuration file solution, you could also use SQL Server configurations, but you have to store the connectionstring of that SQL Server connection manager in a windows Environment variable and use that to configure the connection string to your sql server.
    XML: http://microsoft-ssis.blogspot.com/2012/12/dtap-package-configurations-part-1.html
    SQL: http://microsoft-ssis.blogspot.com/2012/12/dtap-package-configurations-part-2.html


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    Friday, December 14, 2012 6:57 AM
    Moderator
  • adding to the above explanations. what i would suggest u is since u want to change connection string and username and password.For xml config

    select only connection string while configuring Ur package configurations.check the config file whether is having username and password,if not then configure it for username and password also as  u will end up having so many info on Ur config file.

    Also i have faced sometimes where config wont save passwords and thus failing Ur connection when u take ur package to new locations(say from Dev to test) In that case in Ur config file just  username=sa;password=abc; keep this and get the connection set properly.


    • Edited by Nishink Friday, December 14, 2012 7:20 AM
    Friday, December 14, 2012 6:58 AM