0

we need to refresh a table from the csv file.The source file columns are of text type.instead of using a derived column to convert the data type we use flat file source editor to change the data type as per the target.

The idea is to avoid un necessary processing by derived column. if there are errors in flat file data reading then we divert it via flow2 Is this a good ETL design pattern!?

enter image description here

user1254579
  • 3,901
  • 21
  • 65
  • 104

1 Answers1

2

Derived Column/Data Coversion transformations are non-blocking and would not add any overhead to the processing. However I feel the best way to achieve what you are doing is to define the data types of the column in the Flat File Connection Manager itself and not via the error path.

VKarthik
  • 1,379
  • 2
  • 15
  • 30
  • Thanks.But ,script component is also non blocking.In this scenario we can avoid the un necessary processing right? – user1254579 Nov 08 '16 at 09:54
  • 1
    As Nick mentioned, it's an unnecessary complication of using a Script task which is also not easy to maintain. The only use case I can think of where you are not wanting to define the Data Types on the column is when there is a case of source data type varying. In such cases, it would be best to go with BIML. In a general scenario, it is always best to define at connection itself. – VKarthik Nov 08 '16 at 10:45
  • There are two issues with script task: 1. The Visual Studio Editor is unreliable and it's easy to corrupt the visual studio install so that you can't edit scripts anymore; 2. Scripts require special heightened permissions in temp on C drive, which infrastructure are often reluctant to implement on production boxes. – Nick.Mc Nov 09 '16 at 03:27