MSBI # 40 – SSIS # 17 – Data Flow Task # 3 – All Source and Destination Overview {Part – II}

Hi folks ,

I just apologize to my reader for not posting from last week !!

Rather than throwing lots of reason I will just say I am lazy .

So here onwards I will try to be regular !!

Continuing from my last post on SSIS Data Flow Task

• MSBI # 38 – SSIS # 16 – Data Flow Task # 2 – Introduction {Part – I}
• MSBI # 36 – SSIS # 15 – Control Flow Tasks # 8 – Data Flow Task # 1

In this article we have cover following Source And Destination

ADO NET source and Destination

Excel source and Destination

Flat File Source and Destination

OLE DB Source and Destination

Row File Destination and Source

XML Source

Recordset Destination

SQL Server Compact Destination

SQL Server Destination

Lets discuss one by one in basic level of understanding !!

ADO NET source and Destination

• ADO NET source fetches and usage data from a .NET provider and makes the data available to the data flow.
• We configure the ADO NET source by providing the SQL statement that defines the result set.
• The ADO NET source uses an ADO.NET connection manager to connect to a data source, and the connection manager specifies the .NET provider.
• The ADO NET source has one regular output and one error output.
• The ADO NET destination loads data into a variety of ADO.NET-compliant databases that use a database table or view. You have the option of loading this data into an existing table or view, or you can create a new table and load the data into the new table.
• ADO NET destination uses an ADO.NET connection manager to connect to a data source and the connection manager specifies the ADO.NET provider to use.
• An ADO NET destination includes mappings between input columns and columns in the destination data source.
• We do not have to map input columns to all destination columns. However, the properties of some destination columns can require the mapping of input columns.

Excel source and Destination

This is most commonly use source in all ETL solution as most of worlds usage Excel in there daily analysis and communication

The Excel destination loads data into worksheets or ranges in Microsoft Excel workbooks.

The Excel destination provides three different data access modes for loading data: A table or view, table or view specified in a variable, The results of an SQL statement. The query can be a parameterized query.

Flat File Source and Destination

The Flat File source reads data from a text file. The text file can be in delimited, fixed width, or mixed format.

• Delimited format uses column and row delimiters to define columns and rows.

• Fixed width format uses width to define columns and rows. This format also includes a character for padding fields to their maximum width.

• Ragged right format uses width to define all columns, except for the last column, which is delimited by the row delimiter.

  The Flat File destination writes data to a text file. The text file can be in delimited, fixed width, fixed width with row delimiter, or ragged right format.

  You can configure the Flat File destination in the following ways:

• Provide a block of text that is inserted in the file before any data is written. The text can provide information such as column headings.
• Specify whether to overwrite a data in a destination file that has the same name
• This destination has one output. It does not support an error output.

OLE DB Source and Destination

The OLE DB source provides four different data access modes for extracting data:

• A table or view.
• A table or view specified in a variable.
• The results of an SQL statement. The query can be a parameterized query.
• The results of an SQL statement stored in a variable.

The OLE DB destination provides five different data access modes for loading data:

• A table or view. You can specify an existing table or view, or you create a new table.
• A table or view using fast-load options. You can specify an existing table or create a new table.
• A table or view specified in a variable.
• A table or view specified in a variable using fast-load options.
• The results of an SQL statement.

Row File Destination and Source
• The Raw File source reads raw data from a file. Because the representation of the data is native to the source, the data requires no translation and almost no parsing.
• This means that the Raw File source can read data more quickly than other sources such as the Flat File and the OLE DB sources.
• The Raw File source is used to retrieve raw data that was previously written by the Raw File destination.
• We can configure the Raw File by specifying the name of the name of the file that the Raw File source reads.

The Raw File destination writes raw data to a file. Because the format of the data is native to the destination, the data requires no translation and little parsing. This means that the Raw File destination can write data more quickly than other destinations such as the Flat File and the OLE DB destinations.

We can configure the Raw File destination in the following ways:

• Specify an access mode which is either the name of the file or a variable that contains the name of the file to which the Raw File destination writes.

• Indicate whether the Raw File destination appends data to an existing file that has the same name or creates a new file.

XML Source

• The XML source reads an XML data file and populates the columns in the source output with the data.
• The data in XML files frequently includes hierarchical relationships. For example, an XML data file can represent catalogs and items in catalogs.
• Before the data can enter the data flow, the relationship of the elements in XML data file must be determined, and an output must be generated for each element in the file.
• The XML source uses a schema to interpret the XML data.
• The XML source supports use of a XML Schema Definition (XSD) file or inline schemas to translate the XML data into a tabular format.
• If you configure the XML source by using the XML Source Editor dialog box, the user interface can generate an XSD from the specified XML data file.

Recordset Destination

• The Recordset destination does not save data to an external data source.
• Recordset destination saves data in memory in a recordset that is stored in an Integration Services package variable of the Object data type.
• After the Recordset destination saves the data, you typically use a Foreach Loop container with the Foreach ADO enumerator to process one row of the recordset at a time.
• The Foreach ADO enumerator saves the value from each column of the current row into a separate package variable. Then, the tasks that you configure inside the Foreach Loop container read those values from the variables and perform some action with them.

SQL Server Destination

The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. We cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination

SQL Server Compact Destination

The SQL Server Compact destination writes data to SQL Server Compact databases.

We can configure the SQL Server Compact destination by specifying the name of the table into which the SQL Server Compact destination inserts the data.

The custom property TableName of the SQL Server Compact destination contains the table name.

Dear readers ,we will surely have elaborative post on each of this task as one new article soon .

Hope this helps !!

Hope you have understood basic aspect of Data flow Destination and Sources and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 24: Sept 08–Sept 12 « Dactylonomy of Web Resource