I have a DTS package which I am planning to re-write in SSIS. One of the
tasks in dts package is a "Execute SQL Task" which has the following code:

SELECT
a.Column1,
LTRIM(a.column2) as column2,
a.column3,
ISNULL(a.column4, '001') as column4,
ISNULL(b.lkp_column5,'46') as column5,
ISNULL(c.lkp_column6, 4) as column6,
a.Column1 as column7
INTO Table_D
FROM Table_A A
LEFT OUTER JOIN Table_B B
ON A.Column1 = B.Column1
LEFT OUTER JOIN Table_C C
ON A.Column8 = C.column8


In SSIS, should I continue using the same code in Execute SQL Task or will
using Transformations in Data Flow task be more beneficial? Table_D will be
used in the same package to further create reporting tables with aggregations.

If I should be using Transformations then probably I'll need to use 2 Lookup
Transformations for Tables B and C, right? How will I then take care of the
NULLS (columns 4-6) and the leading spaces (column2)?


Your response shall be highly appreciated.