SSIS 2012SSIS 2005SSISVisual Studio Visual Studio 2010Package SSISSSIS Integration Services SSIS /2008/ R2 SSIS D Integration Serv

02 SQL Server Data Tools Integration Services

SSIS 2012SSIS 2005SSISVisual Studio Visual Studio 2010Package SSISSSIS Integration Services SSIS 20122005/2008/2008 2 R2 SSIS D 02-01 Integration Services SSISSQL SQL Server Data ToolsSSDT 1 SSISWPF S Visual Studio 2010SQL Server Data ToolsSSDT Business Intelligence Development StudioBIDSSSDT Visual Studio 2010SSISAnalysis ServicesReporting Services SSDTVisual Studio 2010 Visual Studio 2010.NET Framework 4.0Ctrl SSISSSDT 200 1. Analysis ServicesCubeReporting Services Integration ServicesWebSQL Server 32 SQL Server 2012 SSIS

Microsoft SQL Server 2012SQL Server Data Tools Microsoft Visual Studio 2010Microsoft Visual PART Ⅰ 01 02-0102 Studio 2010SSDTC:\Program Files (x86)\microsoft Visual Studio 10.0\Common7\IDE\devenv.exeSSDT Visual Studio 2010 Integration Services 03 04 05 06 07 08 09 10 2-1SSDTSSIS SSI Integration Integ Services Integration Services 2 2. CHAPTER 02 SQL Server Data Tools 33

Integration Services ispac 3 SQL ServerIntegration ServicesSSIS SSIS2-2 2-2Integration Services es 2-1.sln.sln.dtprojIntegration Services SSIS Package.dtsx SSISVisual Studio 2010SSIS 2-3 3. ispacis 12SSIS 34 SQL Server 2012 SSIS

SSIS PART Ⅰ 01 02-0102 Integration Services 03 04 05 06 07 08 09 SSIS 2-3SSIS 10 1. SSISIntegration Service Visual Studio SSIS 2. SSIS Project.params 3. SSIS 2-4SSIS SSIS 2-4 SSIS CHAPTER 02 SQL Server Data Tools 35

4. SSIS 5. Ctrl 510%2-5 2-5SSIS SIS 6. undo 4 RedoCtrl-Z/Ctrl-Y/Ctrl-Z/ trl-y / / 2-6 2-6/ 4. SSIS 36 SQL Server 2012 SSIS

7. SSIS 20122-7 Enter PART Ⅰ 01 02-0102 Ctrl + EnterEnter Bug Integration Services 03 04 05 06 07 08 09 2-7 10 8. 5 / SSIS 2012 2-8 2-8 5. CHAPTER 02 SQL Server Data Tools 37

2-8Ctrl / Visual StudioSSIS SSIS ETL 2-2step 2-2 by step 2-1-1 SSDTSSD Integration ServicesVisual Visual Studio XMLSQL XM ServerMSDBSSIS SSIS 2012SQL ServerIntegration Integration Services Ctrl+Alt+L Integration Services Project.params SSIS 2012 SSIS.dtsxIS IS SSIS2-9 38 SQL Server 2012 SSIS

PART Ⅰ 01 02-0102 Integration Services 03 04 05 06 2-9Integration Services.dtsx 2-10 07 08 09 10 2-10 *.dtsx *.dtsx CHAPTER 02 SQL Server Data Tools 39

SSIS 2012SSIS SSIS 2012 2-9 SSIS 2005/2008/2008 R2 2-1-2 SSIS 2012SSISSSIS SSISSSISVisual SSIS Studio.NET SSISWorkflow Workflow Component SSIS2-11 SSIS SIS SSIS 2012 SSIS SSIS 2012SSIS 40 SQL Server 2012 SSIS

PART Ⅰ 01 02-0102 Integration Services 03 04 05 06 07 08 09 2-11SSISSSIS SSIS 10 SSISSSISAttunityS ODBC SourceODBC DestinationCDCCDCnC CDCDQS ActiveX ScriptExecute cute DTS 2000 Package Task SQL 2000 2-11 URL http://code.msdn.microsoft.com/site/search?f[0].type=topic&f[0]. Value=SsisDataflowTask URL CHAPTER 02 SQL Server Data Tools 41

Integration Services2-12 2-12 2-1-3 SSIS SSIS 2012 2-13 2-13 42 SQL Server 2012 SSIS

fx 2-14 PART Ⅰ 01 02-0102 2-14fxExpression EvaluatAsExpressionTrue2-15 Integration Services 03 04 05 06 07 08 09 10 2-15EvaluatAsExpressionTrue SSIS Integration Services SSIS 2-16 CHAPTER 02 SQL Server Data Tools 43

2-16 SSISIgnoreConfigurationsOnLoadSIgnoreConfiguratio ProductVersionLastModifiedProductVersionServerExecutionIDonLastM ionserv ionid 2-1-4 Parameters SSIS Encapsulation SSIS 20122 6 6. SSISDB 12SSIS 44 SQL Server 2012 SSIS

SSIS 7 SSDT Project.params PART Ⅰ 01 02-0102 2-17 Integration Services 03 04 05 06 07 08 2-17 SSDT 09 10... 8 FalseTrueFalse e T-SQLSSMSNULL XMLXML 7. By ValueBy Reference 8. Sensitive CHAPTER 02 SQL Server Data Tools 45

<DTS:PackageParameter DTS:CreationName="" DTS:DataType="8" DTS:DTSID="{8F5258B6-DD80-4D84-9CD4-DBC17DB5593D}" DTS:ObjectName="strFile" DTS:Required="True" DTS:Sensitive="True"> <DTS:Property DTS:Name="ParameterValue"> <DTS:Property DTS:DataType="8" DTS:Name="ParameterValue" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAQFqscUqX4UadKBjAkvk</DTS:Property> ERjHoAwE/C </DTS:Property> </DTS:PackageParameter> Required 2-18 2-18 2-18 SQL SererIntegration ServicesSSMS 2-19 12SSIS 46 SQL Server 2012 SSIS

PART Ⅰ 01 02-0102 Integration Services 03 04 05 06 2-19SSMSSSMS SSIS Expression $Project/Package:: ProjectPackageProjectPackage @ 07 08 09 10 @[$Project/Package::] Package::] ForLoop.dtsx ForLoop.dtsx intcountfor 9 2-2020 2-20For 9. For5 CHAPTER 02 SQL Server Data Tools 47

2-20iFor EvalExpressionSSISExpression@ IintCount @i<=@[$package::intcount] 2-20ReadOnlyVariables.NET MessageBox.Show("i=" & Dts.Variables("i").Value.ToString() & _ ", intcount=" & Dts.Variables("intCount").Value.ToString()) For ReferenceTypeReferenceTy 10 2-21 2-21 2-21 10. SSIS 2012 ReferenceType 48 SQL Server 2012 SSIS

dtexecagent Job SQL Server Integration ServicesManagement Studio blog http://blogs.msdn.com/b/mattm/archive/2011/07/16/configuring-projects-andpackages-using-parameters.aspx SSDS 2-172-18 Integration Services 2-19 01 02-0102 Integration Services PART Ⅰ 03 04 05 06 07 08 09 10 2-1 SSDT sqlcmd -S localhost -E -i C:\BookSamples\SSIS2012\CH02_Introduction\ExecuteSQL\DoSomething. sql -o C:\BookSamples\SSIS2012\CH02_Introduction\ExecuteSQL\Result2.log 2-22 CHAPTER 02 SQL Server Data Tools 49

2-22 ArgumentsArgu 2-23 2-23 Fx 50 SQL Server 2012 SSIS

PART Ⅰ 01 02-0102 Integration Services 03 04 05 06 07 08 2-24 09 10 ExpressionArguments Argu 12SSIS 12 2-1-5 SSIS 2012 *.conmgr SSIS 2-25 CHAPTER 02 SQL Server Data Tools 51

2-25.. Name Name ()() 2-26 2-26 52 SQL Server 2012 SSIS

SSIS Expression fx 2-27 01 02-0102 Integration Services PART Ⅰ 03 04 05 06 07 08 09 2-27Expression 10 SSIS SSIS SSIS 2012 SSDT 2-28 2-28 CHAPTER 02 SQL Server Data Tools 53

2-28 *.commer 2-1-6 2-29 Name 2-29 54 SQL Server 2012 SSIS

PART Ⅰ 01 02-0102 Integration Services 03 04 05 06 07 08 09 2-30 10 Name NameIDName GUIDIDGUID ID 11 <> VersionBuild VersionBuild 12 2-1-7 SSIS Package.dtsx 11. ID ID 12. VersionBuild VersionBuild CHAPTER 02 SQL Server Data Tools 55

ScriptLanguageMicrosoft Visual Basic 2010 Dim Path As String = Dts.Variables("Path").Value.ToString() Dim FileName As String = Dts.Variables("FileName").Value.ToString() MessageBox.Show("" & Path + FileName) MessageBox.Show("Target:" & Dts.Connections("OLEDB Target").Connection- String) 2-31MessageBox 2-31 2-31X 56 SQL Server 2012 SSIS

F5 01 02-0102 Integration Services PART Ⅰ 03 04 05 06 07 08 09 10 2-32SSIS SSIS SSIS 2012 //2-33 2-33SSDT CHAPTER 02 SQL Server Data Tools 57

2-1 13 2-1SSIS 2-1-8 XML SSISSSIS SS 2012 // containertaskprecedence task precedence constraint Data Flow Component nt SSISXMLDesignXML XML (*.dtsx)(*.dtsx) XML2-34 XMLSSIS 2012 XML 13. 58 SQL Server 2012 SSIS

PART Ⅰ 01 02-0102 2-34XMLSSIS SSISXML SSISXMLXML SSIS S 2012XML Integration Services 03 04 05 06 07 08 09 10 DelayValidationFalse True <DTS:ConnectionManagernager DTS:refId="Package.ConnectionManagers[local_Northwind]" DTS:CreationName="ODBC" DTS:DelayValidation="True" SSISemuser <DTS:ConnectionManager> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> dtsx CHAPTER 02 SQL Server Data Tools 59

<DTS:ConnectionManagers> <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[LocalHost.tempdb1]" DTS:CreationName="ODBC" DTS:DTSID="{B220B9AF-C427-4D9E-8023-5094C5B7132C}" DTS:ObjectName="LocalHost.tempdb1"> <DTS:ObjectData> <DTS:ConnectionManager DTS:ConnectionString="server=.;Driver={SQL Server Native Client 11.0};trusted_connection=Yes;app=MicrosoftR Visual StudioR 2010;wsid=SQL2012;d atabase=tempdb;" /> </DTS:ObjectData> </DTS:ConnectionManager> <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[local_Northwind]" ctionmana DTS:CreationName="ODBC" DTS:DelayValidation="True" e" DTS:DTSID="{0A6A8C30-6FAE-45EE-BB0E-9CD313047F8E}" -9CD313047F8E}" DTS:ObjectName="local_Northwind"> l_n <DTS:ObjectData> <DTS:ConnectionManager DTS:ConnectionString="Dsn=local_Northwind;" l_northwi /> </DTS:ObjectData> </DTS:ConnectionManager> </DTS:ConnectionManagers> onmanagers> <DTS:ConnectionManager> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:Property DTS:Name="ObjectName">exec.log</DTS:Property> <DTS:Property DTS:Name="DTSID">{8893C900-1846-427E-998D-8386B2B352BC} </DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName">FILE</DTS:Property><DTS:ObjectData><DTS: ConnectionManager> <DTS:Property DTS:Name="FileUsageType">1</DTS:Property> <DTS:Property DTS:Name="ConnectionString">C:\201003TechNet_SSIS\Samples\ Log\exec.log</DTS:Property></DTS:ConnectionManager></DTS:ObjectData> </DTS:ConnectionManager> <DTS:ConnectionManager> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:Property DTS:Name="ObjectName">LocalHost.AdventureWorks</DTS:Property> 60 SQL Server 2012 SSIS

PART Ⅰ <DTS:Property DTS:Name="DTSID">{E9077A77-111D-475F-92FE-81E5E9FE638F} </DTS:Property> <DTS:Property DTS:Name="Description"></DTS:Property> <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS :ConnectionManager> <DTS:Property DTS:Name="Retain">0</DTS:Property> <DTS:Property DTS:Name="ConnectionString">Data Source=.;Initial Catalog=Adven tureworks;provider=sqlncli10;integrated Security=SSPI;Auto Translate=False;</ DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager> XMLattribute XMLelementeleme SSDT 02-02 SQL 7.0/2000 DTS Parallel Data Pump TaskSSIS... pipelinessisdtstemp SSIS table/ssis CPU 01 02-0202 03 04 05 06 07 08 09 10 SSIS 2012 SSIS CHAPTER 02 SQL Server Data Tools 61