Thursday 31 December 2015

Naming Convention for SSIS Components


Naming conventions are good as it makes it easier for us to read the existing packages. It is a way to give the name of the package or component on proper format or stander format. When somebody sees the package or components they will understand easily the purpose of the package. 

Prefix-based Naming Convention for SSIS Components

SSIS package

Type
Description
Naming Prefix
Package File Name
It is the physical DTSX file.

Descriptive name as per organization convention
Name (in Properties window)
It is the package identification name in the project. Can be different from the physical file name. This name shows up in the logging.

SSIS-

Connection Managers

Type
Description
Naming Prefix
ADO
Connects to ActiveX Data Objects (ADO) objects.
ADO_CONN-
ADO.NET
Connects to a data source by using a .NET provider.
ADONET_CONN-
CACHE
Reads data from the data flow or from a cache file (.caw), and can save data to the cache file.
CACHE_CONN-
EXCEL
Connects to an Excel workbook file.
EXCEL_CONN-
FILE
Connects to a file or a folder.
FILE_CONN-
FLATFILE
Connect to data in a single flat file.
FLATFILE_CONN-
FTP
Connect to an FTP server.
FTP_CONN-
HTTP
Connects to a Web server.
HTTP_CONN-
MSMQ
Connects to a message queue.
MSMQ_CONN-
MSOLAP100
Connects to an instance of SQL Server Analysis Services or an Analysis Services project.
MSOLAP100_CONN-
MULTIFILE
Connects to multiple files and folders.
MULTIFILE_CONN-
MULTIFLATFILE
Connects to multiple data files and folders.
MULTIFLATFILE_CONN-
OLEDB
Connects to a data source by using an OLE DB provider.
OLEDB_CONN-
ODBC
Connects to a data source by using ODBC.
ODBC_CONN-
SMOServer
Connects to a SQL Server Management Objects (SMO) server.
SMOServer_CONN-
SMTP
Connects to an SMTP mail server.
SMTP_CONN-
SQLMOBILE
Connects to a SQL Server Compact database.
SQLMOBILE_CONN-
WMI
Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.
WMI_CONN-
ORACLE
Connects to an Oracle <version info> server.
ORACLE_CONN-
SAPBI
Connects to an SAP NetWeaver BI version 7 system.
SAPBI_CONN-
TERADATA
Connects to a Teradata <version info> server.
TERADATA_CONN-
Any custom connection
Connects to a custom source.
<Custom Source>_CONN-

Integration Services Containers

Container
Description
Naming Prefix
Runs a control flow repeatedly by using an enumerator.
FELC-
Runs a control flow repeatedly by testing a condition.
FLC-
Group’s tasks and containers into control flows that are subsets of the package control flow.
SEQC-
Provides services to a single task.
<Not applicable>
Group Container
Group’s tasks and containers into control flows that are subsets of the package control flow.
<Not applicable>

Integration Services Tasks

Data Flow Task

Type
Description
Naming Prefix
Data Flow Task
The task that runs data flows to extract data, apply column level transformations, and load data.
DFT-

Data Preparation Tasks

Type
Description
Naming Prefix
File System Task
These tasks do the following processes: copy files and directories; download files and data; run Web methods; apply operations to XML documents; and profile data for cleansing.
FST-
FTP Task
FTPT-
Web Service Task
WEBSVCT-
XML Task
XMLT-
Data Profiling Task
DPROFT-

Workflow Tasks

Type
Description
Naming Prefix
Execute Package Task
The tasks that communicate with other processes to run packages, run programs or batch files, send and receive messages between packages, send e-mail messages, read Windows Management Instrumentation (WMI) data, and watch for WMI events.
EPKGT-
Execute Process Task
EPRCST-
Message Queue Task
MSGQT-
Send Mail Task
SMAILT-
WMI Data Reader Task
WMIDRT-
WMI Event Watcher Task
WMIEWT-

SQL Server Tasks

Type
Description
Naming Prefix
Bulk Insert Task
The tasks that access, copy, insert, delete, and modify SQL Server objects and data.
BULKINST-
SSIS Execute SQL Task
ESQLT-
Transfer Database Task
TFRDBT-
Transfer Error Messages Task
TFRERRMSGT-
Transfer Jobs Task
TFRJOBT-
Transfer Logins Task
TFRLGNT-
Transfer Master Stored Procedures Task
TFRMSPROCT-
Transfer SQL Server Objects Task
TFRSQLOBJT-

Scripting Tasks

Type
Description
Naming Prefix
Script Task
The tasks that extend package functionality by using scripts.
SCRIPT-

Analysis Services Tasks

Type
Description
Naming Prefix
Analysis Services Execute DDL Task
The tasks that create, modify, delete, and process Analysis Services objects.
SSASEDDLT-
Analysis Services Processing Task
SSASPRCST-
Data Mining Query Task
SSASDMQRYT-

Maintenance Tasks

Any other custom tasks not shipped with the BIDS.

Type
Description
Naming Prefix
Back Up Database Task
The tasks that perform administrative functions such as backing up and shrinking SQL Server databases, rebuilding and reorganizing indexes, and running SQL Server Agent jobs.
BKUPDBT-
Check Database Integrity Task
CHKDBINT-
Execute SQL Server Agent Job Task
EAGNTJOBT-
Execute T-SQL Statement Task
ETSQLSTMT-
History Cleanup Task
HISTCLT-
Maintenance Cleanup Task
MAINTCLT-
Notify Operator Task
NTFYOPT-
Rebuild Index Task
RBLDIDXT-
Reorganize Index Task
RORGIDXT-
Shrink Database Task
SHRINKDBT-
Update Statistics Task
UPDST-
Custom Tasks
<Custom>T-

Integration Services Sources

Built-in Connection Sources

Source
Description
naming prefix
Consumes data from a .NET Framework data provider.
ADONET_SRC-
Extracts data from an Excel file.
EXCEL_SRC-
Extracts data from a flat file.
FLATFILE_SRC-
Consumes data from an OLE DB provider.
OLEDB_SRC-
Extracts raw data from a file.
RAWFILE_SRC-
Uses script to extract, transform, or load data.
SCRIPT_SRC-
Extracts data from an XML file.
XML_SRC-
Oracle Source
The Oracle source is the source component of the Microsoft Connector for Oracle by Attunity.
ORACLE_SRC-
SAP BI Source
The SAP BI source is the source component of the Microsoft Connector for SAP BI.
SAPBI_SRC-
Teradata Source
The Teradata source is the source component of the Microsoft Connector for Teradata by Attunity.
TERADATA_SRC-
Any custom source
Custom source component.
<Custom Source>_SRC-

Integration Services Destinations

Destination
Destination
Description
Naming Prefix
Loads data into a variety of ADO.NET-compliant databases that use a database table or view.
ADONET_DEST-
Trains data mining models.
DMMODTRN_DEST-
Exposes the data in a data flow by using the ADO.NET DataReader interface.
DREADER_DEST-
Loads and processes an SQL Server Analysis Services dimension.
DIMPRCS_DEST-
Writes data to an Excel Workbook.
EXCEL_DEST-
Write data to a flat file.
FLATFILE_DEST-
Loads data using an OLE DB provider.
OLEDB_DEST-
Loads and processes an Analysis Services partition.
PTNPRCS_DEST-
Writes raw data to a file.
RAWFILE_DEST-
Creates an ADO recordset.
RECSET_DEST-
Uses script to extract, transform, or load data.
SCRIPT_DEST-
Inserts rows into a SQL Server Compact database.
SQLCE_DEST-
Bulk inserts data into a SQL Server table or view.
SQL_DEST-
Oracle Destination
The Oracle destination is the destination component of the Microsoft Connector for Oracle by Attunity.
ORACLE_DEST-
SAP BI Destination
The SAP BI destination is the destination component of the Microsoft Connector for SAP BI.
SAPBI_DEST-
Teradata Destination
The Teradata destination is the destination component of the Microsoft Connector for Teradata by Attunity.
TERADATA_DEST-
Any custom destination
Custom destination component.
<Custom Destination>_DEST-

Integration Services Transformations

Business Intelligence Transformations

Transformation
Description
Naming Prefix
The transformation that configures the updating of a slowly changing dimension.
SCD_TRFM-
The transformation that standardizes values in column data.
FZGRP_TRFM-
The transformation that looks up values in a reference table using a fuzzy match.
FZLKP_TRFM-
The transformation that extracts terms from text.
TMEXT_TRFM-
The transformation that looks up terms in a reference table and counts terms extracted from text.
TMLKP_TRFM-
The transformation that runs data mining prediction queries.
DMQRY_TRFM-

Row Transformations

Transformation
Description
Naming Prefix
The transformation that applies string functions to character data.
CHARMAP_TRFM-
The transformation that adds copies of input columns to the transformation output.
COPYCOL_TRFM-
The transformation that converts the data type of a column to a different data type.
DCONV_TRFM-
The transformation that populates columns with the results of expressions.
DRVCOL_TRFM-
The transformation that inserts data from a data flow into a file.
EXPCOL_TRFM-
The transformation that reads data from a file and adds it to a data flow.
IMPCOL_TRFM-
The transformation that uses script to extract, transforms, or load data.
SCRIPT_TRFM-
The transformation that runs SQL commands for each row in a data flow.
OLEDBCMD_TRFM-

Rowset Transformations

Transformation
Description
Naming Prefix
The transformation that performs aggregations such as AVERAGE, SUM, and COUNT.
AGG_TRFM-
The transformation that sorts data.
SORT_TRFM-
The transformation that creates a sample data set using a percentage to specify the sample size.
PRCNTSAM_TRFM-
The transformation that creates a sample data set by specifying the number of rows in the sample.
ROWSAM_TRFM-
The transformation that creates a less normalized version of a normalized table.
PIVOT_TRFM-
The transformation that creates a more normalized version of a nonnormalized table.
UNPIVOT_TRFM-

Split and Join Transformations

Transformation
Description
Naming Prefix
The transformation that routes data rows to different outputs.
CSPLIT_TRFM-
The transformation that distributes data sets to multiple outputs.
MLTCAST_TRFM-
The transformation that merges multiple data sets.
UNIONALL_TRFM-
The transformation that merges two sorted data sets.
MRG_TRFM-
The transformation that joins two data sets using a FULL, LEFT, or INNER join.
MRGJOIN_TRFM-
The transformation that looks up values in a reference table using an exact match.
LKP_TRFM-
The transformation that writes data from a connected data source in the data flow to a Cache connection manager that saves the data to a cache file. The Lookup transformation performs lookups on the data in the cache file.
CACHE_TRFM-
Balanced Data Distributor Transformation
The Balanced Data Distributor takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. It is available for download at Microsoft SQL Server Integration Services Balanced Data Distributor.
BALDIST_TRFM-
Any custom transformation
Custom transformation component.
<Custom Transformation>_TRFM-

Auditing Transformations

Transformation
Description
Naming Prefix
The transformation that makes information about the environment available to the data flow in a package.
AUDIT_TRFM-
The transformation that counts rows as they move through it and stores the final count in a variable.
ROWCNT_TRFM-

2 comments:

  1. I think there is a need to provide some more information about SSIS and other components as well.This tool is extremely useful and helpful.

    SSIS Postgresql Write

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts