What are packages in SSIS?

 SSIS packages are organized collections of various connections and elements related to data flow, event handlers, variables, control flow, configurations, or parameters that would be used for assembling and programmatically building graphical design tools. They can also be used for populating data from different sources which can be later used for standardizing and administrative purposes. They are created in BIDS - Business Intelligent Development Studio.


Expand the SSIS Packages node under the SSMS-Samples project in Solution Explorer and you will see the following:

Double click on the SSIS package SSMS-Export-to-Excel.dtsx to open the package.  Let's walk through the following aspects of the SSIS package as displayed in BIDS:

  • Designer
  • Connection Managers
  • Toolbox
  • Properties Window

The designer is the large area in the middle of the window and is shown below:

The following tabs are available in the designer:

  • The Control Flow tab (shown above) contains the tasks that the SSIS package performs and the flow from one task to another.
  • The Data Flow tab (shown below) is another designer that contains the details for a given data flow task; e.g. retrieve data from some data source, optionally perform some transformations on the data, then write it to some other data source.
  • The Event Handlers tab is yet another designer where we can specify tasks to be performed when a particular event is raised.
  • The Package Explorer tab represents the entire package in a tree-view.

The following is the Data Flow designer for the Data Flow task in the Control Flow tab as shown above:

The Connection Managers window contains the various data sources and destinations that the package uses:

The Connection Managers are defined once then referenced in the various tasks such as the Execute SQL Task, an OLEDB Data Source, or a OLEDB Destination.

The Toolbox contains the tasks that are available to the Control Flow, Data Flow or Event Handlers designers.  To build a package you simply drag tasks from the Toolbox onto the designer and connect them in the order you want to execute.  The following Toolbox tasks are available in the Control Flow designer tab:

The following Toolbox tasks are available in the Data Flow designer:

The Properties Window is available for us to edit and update the properties of a task in the designer, or a connection in the Connection Managers area.  Click on the Preparation SQL Task in the Control Flow shown above and you will see the following in the Properties Window:

Comments

Post a Comment

Popular posts from this blog

Difference between ETL vs. ELT

Image
  Difference between ETL vs. ELT Basics ETL ELT Process Data is transferred to the ETL server and moved back to DB. High network bandwidth required. Data remains in the DB except for cross Database loads (e.g. source to object). Transformation Transformations are performed in ETL Server. Transformations are performed (in the source or) in the target. Code Usage Typically used for Source to target transfer Compute-intensive Transformations Small amount of data Typically used for High amounts of data Time-Maintenance It needs highs maintenance as you need to select data to load and transform. Low maintenance as data is always available. Calculations Overwrites existing column or Need to append the dataset and push to the target platform. Easily add the calculated column to the existing table. Analysis
Read more

What are the for each loop enumerators available in SSIS?

  Below are the lists of various types of enumerators provided by SSIS Foreach Loop Container: Foreach  File Enumerator : It enumerates files in a folder. The plus point here is it can traverse through subfolders also. Foreach  Item Enumerator : It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet. Foreach  ADO Enumerator : Useful for enumerating rows in tables. Foreach  ADO.NET Schema  Rowset  Enumerator : To enumerate through schema information about a data source. For example, to get list of tables in a database. Foreach   From  Variable Enumerator : Used to enumerate through the object contained in a variable. (if the object is enumerable) Foreach   NodeList  Enumerator : Used to enumerate the result set of an XML Path Language (XPath) expression. Foreach  SMO Enumerator : It enumerates through SQL Server Management Objects (SMO) objects.
Read more

What are the SSIS package protection levels?

  There are 6 different types of protection levels. Do not save sensitive – (When exporting using DTUTIL specify for protection- 0) Encrypt sensitive with user key – 1 Encrypt sensitive with password – 2 Encrypt all with password -3 Encrypt all with user key – 4 Rely on server storage Do not save sensitive:  makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information. Encrypt sensitive with user key:  Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. Encrypt sensitive with password:...
Read more