locked
DataSources vs ConnectionString - SSIS RRS feed

  • Question

  • What are the advantages of DataSources over ConnectionString? Which is most preferable way to develop packages?
    • Edited by Kongathi Tuesday, August 28, 2012 8:33 AM
    Tuesday, August 28, 2012 8:33 AM

Answers

  • Actually from a development standpoint it depends on how many environments and the level of separation your company requires to move packages from dev,qa,prod,dr enviroments

    We actually use data sources and a set of config files set on each server in a central location. The data sources are referenced in the config files and the config files are referenced in environment variables that allow you, dba, etc between environments with no connection hassles. 

    Best regards,

    Timothy A Vanover

    • Proposed as answer by TVanover Thursday, August 30, 2012 11:06 AM
    • Marked as answer by Eileen Zhao Monday, September 3, 2012 8:49 AM
    Tuesday, August 28, 2012 3:53 PM

All replies

  • You will find your answers in here. In a nutshell, data sources are powerful when it comes to re-use of same connection across multiple packages/ databases but where it falls is its usability in mutli-team setup and operations. Data sources can make development frustrating when the same data source is updated by different developers when working simultaneously on different databases. Operations will find it dfficult to dynamically change database connection without manually changing the packages.

    Connection manager with Package configuration driven connection string should be the preferable way to develop packages.


    http://btsbee.wordpress.com/

    Tuesday, August 28, 2012 8:57 AM
  • You use Connection string within a data source.... Sure  you can create a data source with the same connection string for every new package but instead if it is used the same database  you can use shared data source for every package...

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Tuesday, August 28, 2012 8:59 AM
  • Connection String is one of properties of "DataSource".

    Sometimes, we have a develop server which is used for developing our packages or extracting data, so the data source is dev server which is configured in ConnectionString. When all task finished and test passed, we will deploy packages on production server, then we will configure the connection string of data source to make it point to production server. Different server has the same database structure that wouldn't impact SSIS package, we only need to configure the connectionString.

    You also can share the same data source with other SSIS packages.


    Please vote if it's helpful and mark it as an answer!

    Tuesday, August 28, 2012 9:06 AM
  • What are the advantages of DataSources over ConnectionString? Which is most preferable way to develop packages?

    There are none. Don't use them.

    *Presumably by "ConnectionString" you meant "Connection Manager".


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Tuesday, August 28, 2012 9:22 AM
  • Actually from a development standpoint it depends on how many environments and the level of separation your company requires to move packages from dev,qa,prod,dr enviroments

    We actually use data sources and a set of config files set on each server in a central location. The data sources are referenced in the config files and the config files are referenced in environment variables that allow you, dba, etc between environments with no connection hassles. 

    Best regards,

    Timothy A Vanover

    • Proposed as answer by TVanover Thursday, August 30, 2012 11:06 AM
    • Marked as answer by Eileen Zhao Monday, September 3, 2012 8:49 AM
    Tuesday, August 28, 2012 3:53 PM