SSIS Factory Jemini Joseph About me Working in

SSIS Factory Jemini Joseph

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…

What’s it �Read SSIS packages programmatically to report details using C# script �Create new SSIS 2012/2014 package and deploy using new project deployment model �Convert 2008 packages into 2012/2014 and deploy

Why �Need to find the package that affects a table �Need to convert 100’s of packages to 2012/2014 (end of server life) �Need to create package for unknown format files

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

Controls in a package (Control flow) Task. Host (Executable) Containers Precedence Constraints

Controls in Data Flow Source Paths Transformations Destination

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

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 dcd 80 80 cc 91Microsoft. Sql. Server. Management. Integration. Services. dll C: WindowsassemblyGAC_MSILMicrosoft. Sql. Server. Management. Sdk. Sfc12. 0. 0. 0__89845 dcd 8080 cc 91Micr osoft. 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. Server. S mo. 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

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;

Demo �Read 2008 Package �Create new 2012 package and project �Copy 2008 to 2012 and deploy

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

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

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")

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. Exec ute. SQLTask") Execute SQL

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");

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();

Some properties of OLE source and destination

What’s not done �Doesn’t work for multiple inputs or outputs (Union all merge join, multicast) �Reading packages from MSDB ([dbo]. [sysssispackages]) �Events

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?

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 100 IDTSPath. Collection 100 – Collection of path. My example have two paths. Precedence. Constraints

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. Createplus andplus. 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 10 0. aspx (This is Data Flow control)

Questions? �Can send questions to �jeminijoseph@bi-datasolutions. com