SSIS.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "## **SSIS** []()\n",
                "\n",
                "- [**SSIS - Data Flow Transformations**](#sqlssisdata)\n",
                "- [**SSIS - Control Flow**](#sqlssiscontrol)\n",
                "- [**SISS - Source and destinations**](#sqlssissource)\n",
                "- [**SSIS Interview Questions**](#questions)"
            ],
            "metadata": {
                "azdata_cell_guid": "8a89a3aa-5c45-4ac9-9e2e-76a1dbbd7414"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "**SSIS - Data Flow Transformations** - [**Back to Index**](vscode-file://vscode-app/c:/Program%20Files/Azure%20Data%20Studio/resources/app/out/vs/code/electron-browser/workbench/workbench.html#index)\n",
                "\n",
                "* * *\n",
                "\n",
                "**Aggregate** -- Aggregates input fields : **Group By, Sum, Average, Count, Count distinct, Min, Max**  \n",
                "**Audit --** Allows data flow to include information about the environment, user, and information of the package running.  \n",
                "**Balanced Data Distributor** -- BDD Transformation distributes input across separate CPU threads for each output.  \n",
                "**Character Map --** Apply string transformation functions example Upper/Lower case to input column  \n",
                "**Conditional Spilt --** Applies Case condition an expression to the input to when evaluates to true change the output.  \n",
                "**Copy Column --** Duplicate a column allows you to have multiple copies of the same column.  \n",
                "**Data Conversion --** Change input data like int to string, date, varchar, or field size.  \n",
                "**Data Mining Query** -- DMX lets you create custom statements against Analysis Connection Manager.  \n",
                "**DQS Cleansing --** Select Input columns to send to Data Quality Service to clean the input data.  \n",
                "**Derived Column --** Create new columns by applying expressions concatenation of columns. Rounding to column.  \n",
                "**Export Column --** Sends column to files, example image binary column to be save as jpg.  \n",
                "**Fuzzy Grouping --** Data cleaning by looking for duplicates based on column choice and scores unique rows.  \n",
                "**Fuzzy Lookup --** Data cleaning correct data with missing values that matches a reference table.  \n",
                "**Import Column --** Reads data from files and adds data columns in a data flow. Example Add Image column from file.  \n",
                "**Lookup --** Can cache a reference dataset to preform column lookups on key field. Splits the output from match or not.  \n",
                "**Merge --** Merge combines two \"sorts\" input by the selected column key sort direction. \"Union\"  \n",
                "**Merge Join --** Join two \"sorts\" inputs based on inner, left, full joins by the selected key.  \n",
                "**Multicast --** Send the output to multiple destinations.  \n",
                "**OLE DB Command --** Run SQL Command for each row in the data flow.  \n",
                "**Pivot --** Pivot a row into columns.  \n",
                "**UnPivot** -- Pivot columns into row.  \n",
                "**Row Count --** Counts rows as they flow in, into a variable.  \n",
                "**Script Component --** C# or VB.NETcodingof data  \n",
                "**Row Sampling --** Create Random lottery row selection provides two outputs one that is sampled and other that is not.  \n",
                "**Slowly Changing Dimension --** Updating and Inserting records in DW dim tables type 1,2,3 \"Change, History, Fixed\" based on identifying the business key.  \n",
                "**Sort --** Sorts the input columns ASC or Desc.  \n",
                "**Union All --** Multiple inputs into a single output  \n",
                "**Term Extraction --** Extracts the nouns words from column for search engine optimization and its noun frequency.  \n",
                "**Term Lookup --** Lookups terms from a table and shows how much the source is using words from the lookup"
            ],
            "metadata": {
                "azdata_cell_guid": "793baa70-2582-43e7-8b90-66f016f467eb"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "**SSIS - Control Flow**   - [**Back to Index**](#index)[]()  <a id='sqlssiscontrol'></a>\r\n",
                "\r\n",
                "--------\r\n",
                "\r\n",
                "**Pointer --** Select objects.<br>\r\n",
                "**Data Flow Task --** Allows you to transform data from source to destination.   <br>\r\n",
                "**Execute SQL Task --** Run/Execute SQL statement.   <br>\r\n",
                "**Analysis Services Processing Task --** Process SSAS object updates, recalculate, defrag. <br>\r\n",
                "**Bulk Insert Task --** Copy larges amounts of rows without transaction roll backs.<br>\r\n",
                "**Data Profiling Task --** Check source data more effectively and prevent DQ problems.<br>\r\n",
                "**Execute Package Task --** Run other SSIS packages within a SSIS package.<br>\r\n",
                "**Expression Task --** Evaluates expression of set variables at runtime. <br>\r\n",
                "**File System Task --** Copy, Move, Delete files on the server.<br>\r\n",
                "**FTP Task --** Download or upload data to FTP, however FTPS is not supported.<br>\r\n",
                "**Script Task --** C\\# / Vb.net data or custom transforming of data from other sources like AD.<br>\r\n",
                "**Send Email Task --** Creates and send an email.  <br>\r\n",
                "**Web Service Task --** Writing values to web service WSDL<br>\r\n",
                "**XML Task --** Get XML documents and apply operations with X-path expression merge, validate, compare.<br>\r\n",
                "**-For Loop Container --** Loops until a variable expression or validates to false.  <br>\r\n",
                "**-For Each Loop Container --** Loops an ADO record for each record. <br>\r\n",
                "**-Sequence Container --** Grouping of one to many control flows.<br>"
            ],
            "metadata": {
                "azdata_cell_guid": "99f92899-8874-4e48-8181-29911c0abb0e"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "**SSIS - Source and destinations**   - [**Back to Index**](#index)[]()  <a id='sqlssissource'></a>\r\n",
                "\r\n",
                "--------\r\n",
                "\r\n",
                "**ADO Net source --** Consume ADO.NET from .net provider data.  <br>\r\n",
                "**ADO Net Destination --** Write ADO.NET from .net provider data.  <br>\r\n",
                "**Flat file Source** -- Read Text/CSV file  <br>\r\n",
                "**Flat File Destination --** Write Text/CSV file<br>\r\n",
                "**OLE DB Source --** Read Complaint relational database read tables, views, SQL Commands with parameterization. <br>\r\n",
                "**OLE DB Destination --** Write Complaint relational database read tables, views, SQL Commands with parameterization.  <br>\r\n",
                "**HDFS File Destination --** Write to Azure Hadoop file.(Text, Avro, ORC)**  <br>\r\n",
                "**HDFS file Source --** Read from Azure Hadoop file  <br>\r\n",
                "**Destination Assistant** -- Wizard to create a destination connection.  <br>\r\n",
                "**Source Assistant** -- Wizard to create a source connection.  <br>\r\n",
                "**XML Source** -- Read XML source  <br>\r\n",
                "**XML Destination** -- Write XML sources  <br>\r\n",
                "**OData Source --** Allows to you to grab web svcdata service collections as an input source like from SharePoint.<br>"
            ],
            "metadata": {
                "azdata_cell_guid": "e2d563d6-5c1d-423b-89f8-dd374421a50b"
            },
            "attachments": {}
        }
    ]
}