Another ETL developer or administrator familiar with SSIS won't anticipate your decision to implement a connection that way.Īdditionally, since it's a brand new object, none of the regular design time facilities are available to you Package Configurations, Property Expressions, the TransactionOption property, and runtime validation don't work. You could pass the connection string in to the script using an SSIS variable, or read it from a file - but these are both non-standard practices. Since it's an entirely new connection, you have to set up the connection string. Unfortunately, using that syntax, you're creating a new one, not using one of the managed connections you've already defined in the package. This is the correct type of object - it's a managed connection. The comment in the Script Task counsels you to use to get a reference to a ConnectionManager object. The connection is tested by default when the package is loaded, before any (potentially unrecoverable) work is started.īut perhaps most importantly, using a managed connection within a script (properly) informs someone maintaining the package that a connection to an external resource is being used.They can participate in SSIS (MSDTC) transactions.Their properties (again, the connection string) can be manipulated with Property Expressions.Their properties (primarily the connection string) can be manipulated with Package Configurations.Managed connections have the following benefits: It is simply a connection that's defined and controlled by an entry in the "Connection Managers" window at the bottom of the design surface in BIDS. Lets start with describing what a "managed" connection is (in the context of SQL Server Integration Services). Net data access! And to add insult to injury, even if you did know where to go from here, the example provided will only cause an error, and will never work. How do you actually use this "ConnectionManager" object anyway? There's no "Open" method, or Command object - just weird methods and properties that would be entirely unfamiliar to you even if you know.
ODBC EXCEL AUTOTRANSLATE CODE
In fact, if you attempt to use code in the comment as is - you're only halfway there. The poor advice boils down to the fact that it appears to create a "managed" connection, but doesn't truly do so. They're supposed to be writing documentation. (And seriously, "use something like the following"? Hey - I write the vague blog posts here. As presented, the connection won't participate in MSDTC transactions.The connection string (and name) imply that OLEDB connections can be used.The connection can't be used with Package Configurations.The connection string is hardcoded inside the script.Why is this advice bad? Let me count the ways: To use the connections collection use something like the following:ĬonnectionManager cm = ("OLEDB") Ĭm.ConnectionString = "Data Source=localhost Initial Catalog=AdventureWorks Provider=SQLNCLI10 Integrated Security=SSPI Auto Translate=False " Unfortunately, the closest help documentation - the comments inside the Script Task itself - give you exceptionally poor advice: There are lots of scenarios in SSIS where you'd want to use a connection within a Script Task.