1 / 23

SSIS Factory Lunch Presentation sponsored by Dobler Consulting

Dobler Consulting, a leading database specialist since 2008, offers various services such as remote DB administration, performance optimization, and BI development. Learn about SSIS packages and deployment models in SQL Server 2012/2014 from expert Jemini Joseph.

mherbert
Download Presentation

SSIS Factory Lunch Presentation sponsored by Dobler Consulting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SSIS FactoryLunch 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

  2. 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!

  3. 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…

  4. 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

  5. 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)

  6. 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

  7. Controls in a package (Control flow) Containers TaskHost (Executable) PrecedenceConstraints

  8. Controls in Data Flow Source Transformations Paths Destination

  9. 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

  10. Libraries • References • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Microsoft.CSharp.dll • C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll • C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll • C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll • C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ManagedDTS.dll • C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll • C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll • C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Tasks\Microsoft.SqlServer.ScriptTask.dll • C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll • C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Tasks\Microsoft.SqlServer.SQLTask.dll 100 = SQL 2008, 110=2012, 120 = 2014 GAC – 11= 2012 and 12=2014

  11. Namespaces • using System; • using System.Data; • using Microsoft.SqlServer.Dts.Runtime; • using System.Windows.Forms; • using System.Collections.Generic; • using System.Text; • using System.IO; • using Microsoft.SqlServer.Server; • using Microsoft.SqlServer.Dts.Pipeline.Wrapper; • using Microsoft.SqlServer.Dts.Runtime; • using Microsoft.SqlServer.Management.IntegrationServices; • using Microsoft.SqlServer.Management.Smo; • using System.Collections; • using System.Data.SqlClient; • using Microsoft.SqlServer.Management.Common;

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

  13. 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

  14. Program Flow copy/read 2008 • Copy connections to project connection managers • Read through control flow • If the control is TaskHost (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

  15. Some code samples Opening and reading a package object obj ssisApp = new Microsoft.SqlServer.Dts.Runtime.Application(); Microsoft.SqlServer.Dts.Runtime.PackageSrcpkg = ssisApp.LoadPackage(SrcPkgFile, null);; for (inti = 0; i < Srcpkg.Executables.Count; i++) { obj = SrcPackage.Executables[i]; //ExecuteSQL and Data Flow Task if (obj.ToString() == "Microsoft.SqlServer.Dts.Runtime.TaskHost") (Data flow task and execute SQL) if (obj.ToString() == "Microsoft.SqlServer.Dts.Runtime.Sequence")

  16. Reading TaskHost TaskHost SourcetaskHost = (TaskHost)th; if (th.InnerObject is MainPipe) Data flow if (th.InnerObject.ToString() == "Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask") Execute SQL

  17. Creating controls Executable ExecSQL ExecSQL = DstPackage.Executables.Add("STOCK:SQLTask"); Executable dataFlowTask = null; dataFlowTask = DstPackage.Executables.Add("STOCK:PipelineTask");

  18. Creating controls in DFT MainPipe DstPipeline = dataFlowTask.InnerObject as MainPipe; IDTSComponentMetaData100 Targetcomponent = DstPipeline.ComponentMetaDataCollection.New(); Targetcomponent.ComponentClassID = "DTSAdapter.OleDbSource"; Targetcomponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SrcCM); Targetcomponent.RuntimeConnectionCollection[0].ConnectionManagerID = SrcCM.ID; CManagedComponentWrapper srcDesignTime = Targetcomponent.Instantiate(); srcDesignTime.ProvideComponentProperties(); srcDesignTime.SetComponentProperty("AccessMode", 0); srcDesignTime.SetComponentProperty("OpenRowset", TableName); srcDesignTime.AcquireConnections(null); srcDesignTime.ReinitializeMetaData(); srcDesignTime.ReleaseConnections();

  19. Some properties of OLE source and destination

  20. 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 LineageID between Derived column and OLE Destination • BIML can create new packages. Can it work with flat files or excel?

  21. Some important classes • IDTSComponentMetaData100 – Data flow controls • IDTSComponentMetaDataCollection100 • IDTSOutput100 – Output of a control (the pipe) • IDTSOutputCollection100 • IDTSOutputColumn100 • IDTSOutputColumnCollection100 – Generally one, multicast will have multiple • IDTSInput100 • IDTSInputCollection100 • IDTSInputColumn100 • IDTSInputColumnCollection100 Generally one. Union all will have multiple • IDTSCustomProperty100 – Custom property of a control like expression • IDTSCustomPropertyCollection100 • IDTSPath100 • IDTSPathCollection100 – Collection of path. My example have two paths. • PrecedenceConstraint • PrecedenceConstraints

  22. Some links • https://msdn.microsoft.com/en-us/library/ms136025.aspx (MSDN Developers guide) • http://www.codeproject.com/Articles/18853/Digging-SSIS-object-model (SSIS Object Model) • http://www.codeproject.com/Articles/547311/ProgrammaticallyplusCreateplusandplusDeployplusSSI (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/ssis-package-implementation-programmatically.aspx (Creating 2008 package) • https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper.idtscomponentmetadata100.aspx (This is Data Flow control)

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

More Related