SSIS Best Practices – Part 1

You can avoid SCR component in SSIS for formatting if SQL functions are used while fetching source data.

Below is the example where I need to fetch decimal 3428.00 and format required for submission is 00342800.

 select ISNULL(right(REPLICATE(0,8) + replace(convert(nvarchar(8),NULL),’.’,”),8),’00000000′)

 Also SSIS best practice suggest that fetch as much data as possible while retriving from source and perform  operation on T-SQL statement

Thanks,

Prashant

Read Full Post »

recordset issue in SSIS

I came across an interesting  finding while working with recordset in SSIS Script Component. Here it goes :

Populating a data table from SSIS variable (recordset) within a script component/Task works fine first time but produces no result subsequently.

Below code works fine while we are loading data table from objCDHArrays for the first time.

DataTable dtCDHArrays = new DataTable();

OleDbDataAdapter oleCDHArraysDA = new OleDbDataAdapter();

oleCDHArraysDA.Fill(dtCDHArrays, Variables.objCDHArrays);

If we load again from the same variable , then it returns nothing :

DataTable dtCDHArrays1 = new DataTable();

OleDbDataAdapter oleCDHArraysDA1 = new OleDbDataAdapter();

oleCDHArraysDA1.Fill(dtCDHArrays1, Variables.objCDHArrays);

Solution:

Cast the SSIS recordset to ADODB.Recordset object

Recordset rsPayBase = new Recordset();

rsEECON = (Recordset)Variables.objCDHArrays;

refer to the below links for more info :

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/7b7e6c2f-ca84-4d56-9751-97c8c2becaed/

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2bb13fe7-6241-4344-b96f-ca6053072e2c

Happy Reading

Prashant

Read Full Post »