Resolved by using Script task with following code:

Option Strict Off

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dts.Variables("User::Count").Value...

Full details of package as follows:

Loop 1 (looks for Excel file)

loop2 (looks for Excel sheet using ado.net schema rowset)

DFT

End loop 1

End loop 2

...

Hi Phil,

I've already tried this and receive the same error

Chris

Tim,

I am working with SSIS 2005 and don't have access to 2008 yet. If possible could you send me a few screen shots?

Many thanks.

Chris

Hi Tim,

Many thanks, this sounds exactly the sort of solution I require.

I have tried to Open your example package, however I get an error 'CPackage:LoadFromXMLfails'.

Could you possibly re-send? I am...

Hi Erik,

This is just what I was looking for. Many Thanks for your suggestion.

Chris

Thanks sabyasm.

I have found the problem using the data reader.

The source flat file connection Row Delimiter had to be changed, as the source file had been generated on a Unix...

Hi sabyasm,

This is a great idea that I really like of using the Conditional Split. But unfortently it send all the data to the data destination object when I tried...

Hi hamzajosh,

Thanks for your suggestion. How would I delete the final two rows in VB?

Chris

Hi Piotr,

Yes they prevent the file from being loaded.

Regards,

Chris

Hi Eric.

Many thanks for your help!

I went for your 2nd suggestion of using a derived column transformation which worked fine.

Regards,

Chris

Hi Chris,

Many thanks for your suggestion. This solved my issue by changing the value from '6' to '16'.

Regards,

Chris

I solve this problem with the following CAST statement:

Select Cast(Stuff(Stuff(Stuff(Stuff(Stuff('20091102165659', 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':') as DateTime)

Many thanks all...

Hi Churi,

Many thanks for your suggestion, this works in SQL Server, but how would I implement within the SSIS data flow task.

Many Thanks,

Chris