SSIS Factory Lunch Presentation sponsored by Dobler Consulting

SSIS Factory Lunch Presentation sponsored by Dobler Consulting Presented By: Jemini Joseph | June 18, 2016 8270 Woodland Center Blvd. Tampa, Florida 33614 | Phone: +1 813 322 3240 | www. doblerconsulting. com

About Dobler Consulting! Leading Database Specialists since 2008 • Team of the top Database Consultants in the US and Canada • Headquartered in Tampa, FL with offices in Toronto, Charlotte and Los Angeles • 50 employees and growing every month! • Microsoft Silver partner – Focus on SQL Server, SSRS, SSIS • Clients throughout the USA, Canada and Europe! Types of work we do • Remote Database Administration Support • Database Health Checks • Performance Optimization • Consolidations • Migrations • Upgrades • Data warehouse and BI Development • License sales We become your data management partner! 2

About me Working in Microsoft BI field since 2003. Mostly consulting in SSIS Worked as programmer in Visual Basic before moving to BI jeminijoseph@bi-datasolutions. com https: //www. linkedin. com/in/jeminijoseph When I’m not working… 3

What’s it? ➤ Create new SSIS 2012/2014 package and deploy using new project deployment model from Text/Excel files ➤ Create packages to transfer data between servers (SQL) ➤ Convert 2008 packages into 2012/2014 and deploy ➤ Read SSIS packages programmatically to report details using C# script 4

Why ➤ Need to create 100’s of packages from different sources ➤ Need to find the package that affects a table ➤ Need to convert 100’s of packages to 2012/2014 (end of server life) 5

What do we need ➤ ➤ ➤ SQL Server 2008 installed including SDK SQL Server 2012/2014 installed including SDK SQL Server Data tools Knowledge of C# (or VB. net) scripting. Don’t have to be an expert Visual Studio Community edition 6

Controls in a package (Control flow) Task. Host (Executable) Containers Precedenc e. Constraint s 7

Controls in Data Flow Source Paths Transformations Destination 8

Deployment difference ➤ 2005/2008 used package deployment ➤ Configure each package ➤ 2012/2014 use project deployment model ➤ Can share configuration (Environment) ➤ Uses new SSISDB database and Integration Services Catalogs 9

Libraries ➤ References ➤ C: Program Files (x 86)Reference AssembliesMicrosoftFramework. NETFrameworkv 4. 0Microsoft. CSharp. dll ➤ C: Program Files (x 86)Microsoft SQL Server120SDKAssembliesMicrosoft. Sql. Server. Connection. Info. dll ➤ C: Program Files (x 86)Microsoft SQL Server120SDKAssembliesMicrosoft. Sql. Server. DTSPipeline. Wrap. dll ➤ C: Program Files (x 86)Microsoft SQL Server120SDKAssembliesMicrosoft. SQLServer. DTSRuntime. Wrap. dll ➤ C: Program Files (x 86)Microsoft SQL Server120SDKAssembliesMicrosoft. Sql. Server. Managed. DTS. dll ➤ C: WindowsassemblyGAC_MSILMicrosoft. Sql. Server. Management. Integration. Services12. 0. 0. 0__89845 dc d 8080 cc 91Microsoft. Sql. Server. Management. Integration. Services. dll ➤ C: WindowsassemblyGAC_MSILMicrosoft. Sql. Server. Management. Sdk. Sfc12. 0. 0. 0__89845 dcd 8080 cc 91 Microsoft. Sql. Server. Management. Sdk. Sfc. dll ➤ C: Program Files (x 86)Microsoft SQL Server120DTSTasksMicrosoft. Sql. Server. Script. Task. dll ➤ C: WindowsassemblyGAC_MSILMicrosoft. Sql. Server. Smo12. 0. 0. 0__89845 dcd 8080 cc 91Microsoft. Sql. Ser ver. Smo. dll ➤ C: Program Files (x 86)Microsoft SQL Server120DTSTasksMicrosoft. Sql. Server. SQLTask. dll 100 = SQL 2008, 110=2012, 120 = 2014 GAC – 11= 2012 and 12=2014 10

Namespaces ➤ ➤ ➤ ➤ using System; using System. Data; using Microsoft. Sql. Server. Dts. Runtime; using System. Windows. Forms; using System. Collections. Generic; using System. Text; using System. IO; using Microsoft. Sql. Server; using Microsoft. Sql. Server. Dts. Pipeline. Wrapper; using Microsoft. Sql. Server. Dts. Runtime; using Microsoft. Sql. Server. Management. Integration. Services; using Microsoft. Sql. Server. Management. Smo; using System. Collections; using System. Data. Sql. Client; using Microsoft. Sql. Server. Management. Common; 11

Demo ➤ Create new 2014 package and project ➤ Copy 2008 to 2014 ➤ Read and report on 2008 Package 12

The program flow create new ➤ ➤ ➤ Set the SSISDB and folder on 2014 box Create a 2014 project and package Create Parameters for the project Deploy project to SSISDB Create Environment (Get data from each package) Configure the project using new environment 13

Program Flow copy/read 2008 Copy connections to project connection managers Read through control flow If the control is Task. Host (Executable) copy it If the control is container ➤ Read through controls in container ➤ If the control is executable copy it ➤ Copy Precedence constraints to link controls ➤ If the control is DFT ➤ Copy controls ➤Copy paths between objects ➤Refresh metadata to get columns ➤Copy the mapping for destination controls ➤ ➤ 14

Some code samples Opening and reading a package object obj ssis. App = new Microsoft. Sql. Server. Dts. Runtime. Application(); Microsoft. Sql. Server. Dts. Runtime. Package Srcpkg = ssis. App. Load. Package(Src. Pkg. File, null); ; for (int i = 0; i < Srcpkg. Executables. Count; i++) { obj = Src. Package. Executables[i]; //Execute. SQL and Data Flow Task if (obj. To. String() == "Microsoft. Sql. Server. Dts. Runtime. Task. Host") (Data flow task and execute SQL) if (obj. To. String() == "Microsoft. Sql. Server. Dts. Runtime. Sequence") 15

Reading Task. Host Sourcetask. Host = (Task. Host)th; if (th. Inner. Object is Main. Pipe) Data flow if (th. Inner. Object. To. String() == "Microsoft. Sql. Server. Dts. Tasks. Execute. SQLTask" ) Execute SQL 16

Creating controls Executable Exec. SQL = Dst. Package. Executables. Add("STOCK: SQLTask"); Executable data. Flow. Task = null; data. Flow. Task = Dst. Package. Executables. Add("STOCK: Pipeline. Task"); 17

Creating controls in DFT Main. Pipe Dst. Pipeline = data. Flow. Task. Inner. Object as Main. Pipe; IDTSComponent. Meta. Data 100 Targetcomponent = Dst. Pipeline. Component. Meta. Data. Collection. New(); Targetcomponent. Class. ID = "DTSAdapter. Ole. Db. Source"; Targetcomponent. Runtime. Connection. Collection[0]. Connection. Manager = Dts. Convert. Get. Extended. Interface(Src. CM); Targetcomponent. Runtime. Connection. Collection[0]. Connection. Manager. ID = Src. CM. ID; CManaged. Component. Wrapper src. Design. Time = Targetcomponent. Instantiate(); src. Design. Time. Provide. Component. Properties(); src. Design. Time. Set. Component. Property("Access. Mode", 0); src. Design. Time. Set. Component. Property("Open. Rowset", Table. Name); src. Design. Time. Acquire. Connections(null); src. Design. Time. Reinitialize. Meta. Data(); src. Design. Time. Release. Connections(); 18

Some properties of OLE source and destination 19

Some points ➤ Consider Sequence as a package ➤ The order of the object is unknown. So when you create path, need to find the control without any input (OLE Source) and start from there. ➤ The order of executables in package is unknown. Need to creating precedenceconstraints in right order when you copy existing package ➤ Need to add input columns to Derived column ➤ May need to fix the Lineage. ID between Derived column and OLE Destination ➤ BIML can create new packages. Can it work with flat files or excel? 20

Some important classes ➤ ➤ ➤ ➤ ➤ ➤ IDTSComponent. Meta. Data 100 – Data flow controls IDTSComponent. Meta. Data. Collection 100 IDTSOutput 100 – Output of a control (the pipe) IDTSOutput. Collection 100 IDTSOutput. Column. Collection 100 – Generally one, multicast will have multiple IDTSInput 100 IDTSInput. Collection 100 IDTSInput. Column. Collection 100 Generally one. Union all will have multiple IDTSCustom. Property 100 – Custom property of a control like expression IDTSCustom. Property. Collection 100 IDTSPath. Collection 100 – Collection of path. My example have two paths. Precedence. Constraints 21

Some links ➤ https: //msdn. microsoft. com/en-us/library/ms 136025. aspx (MSDN Developers guide) ➤ http: //www. codeproject. com/Articles/18853/Digging-SSIS-object-model (SSIS Object Model) ➤ http: //www. codeproject. com/Articles/547311/Programmaticallyplus. Createplu sandplus. Deployplus. SSI (Creating and deploying 2012 package) ➤ http: //blogs. msdn. com/b/dataaccesstechnologies/ (Search for SSIS 2012 automation) ➤ http: //blogs. msdn. com/b/dataaccesstechnologies/archive/2013/11/26/ssispackage-implementation-programmatically. aspx (Creating 2008 package) ➤ https: //msdn. microsoft. com/enus/library/microsoft. sqlserver. dts. pipeline. wrapper. idtscomponentmetadata 1 00. aspx (This is Data Flow control) 22

Questions? ➤ Can send questions to Ray Rannala rrannala@doblerllc. com Download this presentation at http: //www. doblerconsulting. com/dobler-ssis -factory-presentation/ 23