Thank you Wayne
This was exactly what I needed to solve an issue I had with an SSIS package that had dynamic columns in a csv file.
I needed to check for a columns existence and route accordingly.
Every other solution I found involved complicated script tasks that didn't solve my problem directly.
I used your sp_oa code in a execute sql task to see if a column existed in a csv file.
Then i used the precedence constraint to route the package to use a different data flow with a different connection.
Here is the code I used to check for the columns existence
declare @FileName varchar(100)
set @FileName = 'C:\myfile.csv'
declare @oFSO int,
@oTSO int,
@line varchar(500)
-- get the first line from the file
execute sp_OACreate 'Scripting.FileSystemObject', @oFSO OUTPUT
execute sp_OAMethod @oFSO, 'OpenTextFile', @oTSO OUTPUT, @FileName
execute sp_OAMethod @oTSO, 'ReadLine', @line OUTPUT
execute sp_OAMethod @oTSO, 'Close'
execute sp_OADestroy @oTSO
execute sp_OADestroy @oFSO
--SELECT @line
IF PATINDEX('%ExtraColumn%',@line)> 0
SELECT CONVERT(BIT,1) AS ExtraColumnExists
ELSE
SELECT CONVERT(BIT,0)AS ExtraColumnExists