DYNAMIC DATA FLOWS IN SSIS WITHOUT PROGRAMMING Powered

DYNAMIC DATA FLOWS IN SSIS WITHOUT PROGRAMMING Powered by SSIS+ www. cozyroc. com Presented by Diane Schuster, COZYROC Technical Marketing

Who is COZYROC? ➢ Software company based in Raleigh, NC, USA o First product was released in 2007 o Customers in more than 140 countries o Microsoft partner ➢ Principal product: SSIS+ Suite of Tasks, Components and Scripts o SSIS+ provides third-party plug-ins for SSIS o Enhances data integration performance, connectivity, and automation o Supports Microsoft SSIS and SQL Server 2005, 2008 R 2, 2014, 2016, 2017 and 2019 o Currently consists of 200+ tasks, components and scripts ➢ COZYROC has sponsored 220+ community events in the past 9 years

Components and Tasks from COZYROC ➢ REST adapters (One. Drive, Mail. Chimp, Marketo, Twitter, Zoho CRM, Shopify, Google Sheets, many more) ➢ Data Flow Task Plus ➢ Excel adapters and Task ➢ File Transfer Task ➢ Dynamics CRM/365/AX/NAV adapters ➢ Share. Point adapters ➢ Mongo. DB adapters ➢ LDAP adapters ➢ Salesforce adapters ➢ Amazon S 3 Task ➢ Netsuite adapters ➢ Sort Plus component ➢ Receive/Send Mail Tasks ➢ Java. Script Task/Component ➢ Parallel Loop Task ➢ Table Difference Task ➢ Many more See www. cozyroc. com for a complete list

What are the Limitations of the standard Data Flow Task? ➢ Only uses static metadata which is defined when the package is initially designed. ➢ The mapping of columns is configured and remains unchanged until the package is re-opened and design changes are made. ➢ If a new column is added or an existing column is modified, the existing package must be opened and modified, thus costing maintenance time and effort.

How is Data Flow Task Plus different? ➢ Dynamically maps columns between the source and the destination at runtime. ➢ Changes to the Metadata are accommodated at runtime. ➢ Dynamic setup is done prior to the data flow execution so there is no performance penalty. ➢ Provides the ability to export the data flow logic for use in multiple packages. This functionality is not found in any other competitive product on the market

When Would it be Beneficial to use Data Flow Task Plus? ➢ Many files provide source data that must be backed up into a database on a regular basis. o According to one happy customer, if your scenario involves backing up many files and tables, the use of COZYROC’s Data Flow Task Plus “turns days of work into mere minutes of configuration. ” o This customer also stated that by using COZYROC’s Data Flow Task Plus and Parallel Loop Task, “we could replace hundreds of packages with just two packages. ” ➢ There are many destination files that must be created from a database on a regular basis. ➢ The data configuration changes frequently. ➢ To migrate data from on-premises to in-the-cloud. ➢ To implement a common data flow process, which can be used for multiple sources/destinations.

Other Features of Data Flow Task Plus ➢ DBA-Friendly ➢ Any standard SSIS transformations may be used with DFT+ ➢ The sources and destinations may consist of any other adapters supported by SSIS or SSIS+

Checklist for Data Flow Task Plus Configuration ❑ Use DFT+ task in place of the standard Data Flow Task ❑ In components/connection managers: delete all statically-defined columns and replace them with THUNK_COLUMN ❑ In the DFT+ Properties: set “Delay Validation” to “True” ❑ In Source/Destination Component Properties: set “Validate. External. Metadata” to “False” ❑ Edit components using the Advanced Editor o Using the regular editor can cause all columns to be refreshed ❑ Enable the dynamic capability for the source and destination in DFT+

Use Data Flow Task Plus in place of Data Flow Task

Delete Static Columns – replace with THUNK_COLUMN

Set Delay. Validation to “True” in Data Flow Task Plus Properties

Set Validate. External. Metadata to “False”

Use Advanced Editor

Enable “Dynamic” capabilities for Source and Destination

Other Configuration Considerations ➢ To maximize control from outside the package, use variables and expressions for names of tables/files/entities/worksheets ➢ Use Foreach Loop Container to process any number of files/tables/entities… ➢ Use a table to specify how to match up columns/fields (if the names don’t match)

Use variables and expressions to maximize control from outside the package

Use For. Each Loop to process multiple entities/files

If column names do not match, use a Table to specify mapping

More Demonstrations on our Website ➢ Video 1 (Basics): o Multiple CSV files copied to multiple SQL Server Tables o No CSV file has all columns in common with other CSV files ➢ Video 2 (Excel import): o Multiple Excel files copied to one SQL Server Table o Different columns in the Excel files and different numbers of columns o Worksheet in each Excel file has a different name ➢ Video 3 (Non-matching columns import): o Column names do not match between Excel and Table o Excel columns with the same name map to different columns in Table o Excel columns with different names map to same column in Table

Licensing Model ➢ Try out the complete SSIS+ Suite for free ➢ You only need to purchase a license once you’re scheduling packages o No license key needed to develop and test in Visual Studio o No license key needed for a one-time migration ➢ Ultimate Subscription: $699/year ➢ Perpetual License: $4, 999 ➢ A license key is required per physical machine ➢ Includes all SSIS+ components, tasks, and scripts

Follow us on youtube. com/c/cozyroc facebook. com/cozyroc twitter. com/cozyroc