SQL Server Tacklebox- P12: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 2 The SQL Server landscape Figure Connection Manager property with blank Server name. Notice that the Server name property value is blank. This is because this value is populated at runtime from the SRV_Conn string variable from every Foreach Loop Container object in the SSIS package. So how do you populate the server name property with the values stored in the SRV_Conn string variable That is easy. SSIS along with other Microsoft technologies like Reporting Services allows you to use Expressions which are very useful in controlling object properties including in this case the server name. Figure shows the Properties window for the Multiserver object. 55 2 The SQL Server landscape Properties x MultiServer Connection - n Mise ConnectionManagerType Connectionstring DataSourcelD OLEDB Data Source Initial Catalog msdb Provider SQLN Delayvalidation Description False Expressions ServerName ta User 5RV_Conn ID Initialcatalog 1F A55FA7-89C7-4304-8CD9-1C35769E6E56 -msdb Name Password MultiServer RetainSameConnection ServerName False SupportsDTCTransactions UserName True Figure Assigning the User SRV_Conn variable to the ServerName property. Notice that the expression @ User SRV_Conn variable is assigned to the ServerName property. At runtime the ServerName Expression which is part of the Connection Manager is populated with the current value of the SRV_Conn variable. As the Foreach Loop container iterates through the server list that was derived from the Populate_ADO Variable From ServerList_SSIS task the value for the expression changes dynamically and the next server in the list is assigned to variable and queried. This continues until there are no more servers in the list. All that is required is that you know that they are in the ServerList_SSIS table and have the Connect field set to 1. Executing the package Now that we know how to populate DBA_Rep via the SSIS Populate_DBA_Rep package let s execute the package and review the results. Figure shows .
