How to count columns of a Flat File in SSIS Script Task

Question

I'm looking for a solution to count the columns (Not rows) from a Flat File with a Script Task and store them to a variable.

Thanks in advance

btw, SQL Server 2008R2

Answer 1

I think you can achieve this with Execute SQL Task using OpenRowSet or OpenDataSource

select *
  into #temp
  from OpenRowSet(...)
 where 1=2

select count(*) 
  from tempdb.sys.columns 
 where object_id = object_id('tempdb..#temp')

drop table #temp

OpenRowSet will like

select * into #temp from openrowset('MSDASQL', 'driver={Microsoft Text Driver (*.txt; *.csv)}; defaultdir=C:\MyFolder;','select * from file.csv')

and then use sp_OA procedures to launching Scripting.FileSystemObject. Open the file, and read the first line. # columns = # of commas + 1.

opendatasource() function against text file:

  select * 
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\files";Extended properties="Text;hdr=no"')...clients#txt

sp_OA method:

declare @FileName varchar(100)
set @FileName = '<Put your filename here>'

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

;with CTE AS
(
select Col = substring(@line, Number, 1)
  from dbo.Numbers
 where Number <= len(@line)
)
select ColumnCount = count(*) + 1, @line
  from CTE
 where Col = ','

More about OPENDATASOURCE - ad-hoc-querying-text-files