SQL Server Integration Services设计模式：实战指南

5星 · 超过95%的资源需积分: 10 128 浏览量更新于2024-07-22 收藏 17.89MB PDF 举报

"《Apress.SQL.Server.Integration.Services.Design.Patterns.2nd.Edition.2014》是一本专注于SQL Server Integration Services (SSIS)设计模式的专业书籍，特别针对SQL Server 2014进行了更新。这本书为解决在创建数据集成解决方案时遇到的基本问题提供了一系列的设计模板。通过书中丰富的示例和方案样本，SSIS开发者可以提高自己的技能，不再需要每次面对新问题时从零开始设计和编码。由一群专家作者共同编著，本书深入讲解了多种设计模式，帮助读者理解背后的思考和技术细节，支持他们找到解决问题的最佳方案。书中的设计模式不仅解决了表面的问题，还探讨了为何特定问题应该用特定方式解决。读者将通过实践案例深入学习SSIS，并了解何时何地应使用替代设计方案。书中的亮点包括ETL（提取、转换、加载）仪表盘、SSIS框架、商业智能标记语言（BIML）和依赖服务等主题。全书章节结构清晰，涵盖了从元数据收集、执行模式、脚本模式到数据库源模式、数据校正与数据质量服务、XML模式、表达式语言模式、数据仓库模式等多个方面。此外，还有针对云加载、日志记录与报告模式、配置、部署以及业务智能的章节，全面覆盖了SSIS开发中的关键领域。这本书是提升SSIS开发者技能和效率的宝贵资源，无论你是初学者还是经验丰富的专业人士，都能从中获益。通过深入学习和实践，你将能够更好地理解和应用SSIS设计模式，从而在数据集成项目中实现更高效、更灵活的解决方案。"

CHAPTER 1 ■ METADATA COLLECTION

On the Variable Mappings page, map the SQLServerInstance variable to Index 0, as demonstrated in Figure 1-12.

Figure 1-11. The Foreach Loop Editor

Figure 1-12. Variable mappings

CHAPTER 1 ■ METADATA COLLECTION

Click the OK button to close the Foreach Loop Editor.

Let’s review what you’ve done so far. You now have a variable, SQLServerList, which contains a list of all the

SQL Server instances you inserted into the dba_monitor_SQLServerInstances table. The Foreach Loop container

then shreds this variable, walking through each value—each SQL Server instance, in this case—one at a time. At each

pass, it pushes the value of one of those SQL Server instances into another variable, SQLServerInstance.

Now it’s time to set up the dynamic connection that will be used to connect to each of the SQL Server instances

you’re monitoring. To do this, you need to create a dummy connection and configure it to use the server name stored

in SQLServerInstance.

Right-click in the Connection Managers window and select New OLE DB Connection. Create a new connection

using the same server and security credentials you used previously (Figure 1-7), but select Master as the database this

time. To be clear, you’re using the same server purely for convenience. In reality, the server specified in the dummy

connection is irrelevant as long as you have sufficient permissions to log onto it, because whatever value you enter will

be overwritten by the SQLServerInstance variable at runtime. The database value does matter, however, because the

database you select must exist on every server. Since Master is a system database, it is a natural choice.

Click OK to close the Connection Manager Properties window. But you’re not done with this connection

just yet. Right-click the newly created connection and select Properties. Change the Name property to

DynamicSQLServerInstance, then click the […] icon in the Expressions field. This will bring up the Property

Expressions Editor. Select the Property value you wish to dynamically populate—ServerName, in this case—and enter

@[User::SQLServerInstance] in the Expression field, as demonstrated in Figure 1-13. Optionally, you can also click

the […] icon in the Expression field to open the Expression Builder, which is helpful if you are not very familiar with

Expression syntax.

Figure 1-13. Property Expressions Editor

The properties of your connection should now resemble those shown in Figure 1-14.

CHAPTER 1 ■ METADATA COLLECTION

Listing 1-5. Example of T-SQL to Retrieve Current Data and Log File Sizes for All Databases on the Server

SELECT GETDATE() AS [captureDate]

, @@SERVERNAME AS [serverName]

, instance_name AS [databaseName]

, SUM(

CASE

WHEN counter_name = 'Data File(s) Size (KB)'

THEN cntr_value

END

) AS 'dataSizeInKB'

, SUM(

CASE

WHEN counter_name = 'Log File(s) Size (KB)'

THEN cntr_value

END

) AS 'logSizeInKB'

FROM sys.dm_os_performance_counters

WHERE counter_name IN ('Data File(s) Size (KB)'

,'Log File(s) Size (KB)')

/* optional: remove _Total to avoid accidentally

double-counting in queries */

AND instance_name <> '_Total'

GROUP BY instance_name;

This query will produce results similar to the following.

captureDate serverName databaseName dataSizeInKB logSizeInKB

----------------------- ---------- -------------------------- ------------ -----------

2014-06-29 19:52:21.543 LOCALHOST _Total 1320896 274288

2014-06-29 19:52:21.543 LOCALHOST AdventureWorks2012 193536 496

2014-06-29 19:52:21.543 LOCALHOST dbaCentralLogging 1048576 262136

2014-06-29 19:52:21.543 LOCALHOST master 4096 760

2014-06-29 19:52:21.543 LOCALHOST model 2112 760

2014-06-29 19:52:21.543 LOCALHOST msdb 14080 760

2014-06-29 19:52:21.543 LOCALHOST mssqlsystemresource 40960 504

2014-06-29 19:52:21.543 LOCALHOST ReportServer$SQL2012 5184 7032

2014-06-29 19:52:21.543 LOCALHOST ReportServer$SQL2012TempDB 4160 1080

2014-06-29 19:52:21.543 LOCALHOST tempdb 8192 760

(10 row(s) affected)

Your OLE DB Source Editor should now resemble the editor in Figure 1-15. Click Parse Query to ensure the

SQL syntax is correct, and then click Preview at the bottom of the editor to see a sample of the results. Click OK to exit

the OLE DB Source Editor.

剩余450页未读，继续阅读

DoomLord

粉丝: 114
资源: 1317

SQL Server Integration Services设计模式：实战指南

《Apress.Pro.Expression.Blend.4》：精通微软WPF与Silverlight开发的全面指南

精通Web前端：Apress.Pro JavaScript实战指南

PHP对象模式实战：第二版权威指南

Apress.Pro.SQL.Server.2005.Integration.Services.Dec.2007.pdf

Apress.Java.Design.Patterns.2nd.Edition.1484240774.zip

apress.Beginning.SQL.Server.2012.for.Developers.3rd.Edition.May.2012

Apress.Beginning.SQL.Server.for.Developers.2014

Apress.Pro.SQL.Server.2008

Apress.PHP.Objects.Patterns.and.Practice.2nd.Edition(2008)

Apress.Accelerated.SQL.Server.2008.May.2008

最新资源