Skip to main content

SSIS_Some Important Settings in Data Flow Tasks & Transformations.

Topics :  (Flat File Source, Conditional Split, Character Map Transformation, Expression Task/Derived Column Transformation, Multicast Transformation, Raw File Source, Lookup Transformation)

 Flat File Source 


The data type can also be changed in the flat file editor(in Advance Mode) for each column in the Flat file.

Conditional Split 


We can have Matched and Unmatched rows based on a particular condition.

Different list of drivers in OLEDB Destination.



If you dont have the Drivers in the above list , then you have to download the drivers.( for ex. download DB2 drivers , Download Netezza drivers ) .

Table or view fast load :

In the OLEDB Destination .This option does , batching operation , which make the process faster.

Sample Example using Conditional Split Transformation



Character Map Transformation for UpperCase : 



Or use UPPER keyword in Conditional Split Transformation for uppercase in the selected columns.


Derived Column  Transformation ( Writing Expression for Concartination and addition of new column) 




Changing the Datatype in  Derived Column Transformation in Advance Editor.


We perform Trimming Operation to avoid mis-matched rows (using Derived Column Transformation ) 



Just Another Sample Example 



IMP : Excel File Destination has DTWSTR data where as the Flat File Data in DTSTR data. So we use the following Data Conversion Tranformation in middle.






Using Multicast to keep one copy of data to different destinations.

Raw File Source to SQL Server Destination 



LookUp Transformation




Error Handling :




 1. Fail Component ( Default setting in SSIS )
      Once Error is occurred the data flow execution is stopped.
 
 2.Ignore Failure 
    Data Flow execution is continued with next steps, by ignoring the errors.

 3. Redirect Rows( Using Red Pipeline ) 
    It Redirects the Mismatched rows to some other destination and the Data flow execution is continued.

Ex.
Fuzzy lookup: This Transformation doesnt correct the data, it only identifies the close matches.


In the below example Fuzzy lookup added 3 more columns and then the following output was given.

After Identifying you can update the data in such manner in SQL.































Labels:

Popular posts from this blog

How to remove special characters in the Data(using Script component Task and Regex)

Start with the Below screen shot ( Before Script Task) and you will come to know about the senario. Note : The Script Component also works for the special characters within the Data as well . for ex "A$hish" Hover over the below Regex. it will show > Show potential fixes > in that Select System.Text.RegularExpressions. The Below will disappear.  Use the Below Script.  public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.CustomerID = RemoveSpecialCharacters(Row.CustomerID); Row.NameStyle = RemoveSpecialCharacters(Row.NameStyle); Row.Title = RemoveSpecialCharacters(Row.Title); Row.FirstName = RemoveSpecialCharacters(Row.FirstName); Row.LastName = RemoveSpecialCharacters(Row.LastName); Row.CompanyName = RemoveSpecialCharacters(Row.CompanyName); Row.SalesPerson = RemoveSpecialCharacters(Row.SalesPerson); Row.EmailAddress = RemoveSpecialCharacters(Row.EmailAddress); Row.Phone = RemoveSpecialCharacters(Row.Phone); } public static string ...
Read more

DBMS

Database : database can be defined as a collection of information stored at a particular place written in a predetermined manner, belongs to a particular topic.  DBMS: (DATA BASE MANAGEMENT SYSTEM):  it is defined as software which is present inside the database which is used o maintain or manage the data within the database .                                 In the above diagram, if the user needs any info from the database. He has to contact with  dbms , then the dbms goes to the database, takes the information from the  database and gives it back to the user. Database models :  these models are broadly divided into the following types: 1.         FMS/FDMS 2.         HMS/HDMS 3.         NDBMS 4.         RDBMS 5.      ...
Read more

Different Schemas in DWH

  SCHEMAS:   Star Schema: -       Star Schema means Dimension Tables are directly linked to the Fact Table. Snow Flake Schema: -       Dimension Tables are indirectly linked to the Fact Table OR -       Dimension table is linking to another dimension table .       Star Schema   Snow Flake Schema 1 Has redundant data and hence tough to maintain/change 1 No redundancy and hence more easy to maintain/change 2 Less complex queries and hence easy to understand 2 More complex queries and hence less easy to understand 3 Less foreign keys so faster execution time of cube. 3 More foreign keys so take more execution of cube 4 Has De-Normalized Tables 4 Has Normalized Tabl...
Read more