OepnRowSet Example

DECLARE @WorkingTempTbl Varchar(20)
SET @WorkingTempTbl = 'FL_HUM_Census';

DECLARE @SourceFileName Varchar(200)
SET @SourceFileName = 'D:\JSAOBDSL_2015-06-01'

DECLARE @SqlTempTbl varchar(MAX)

SET @SqlTempTbl = '
Insert into ' + @WorkingTempTbl + '(
RECORDTYPE)
Select RECORDTYPE from OPENROWSET( BULK ''' + @SourceFileName + ''' ,FORMATFILE = ''D:\GetRowCount.xml'')'

Exec @SqlTempTbl


BULK INSERT FL_HUM_Census
 select *  FROM 'D:\JSAOBDSL_2015-06-01.csv'
   WITH (FORMATFILE = 'D:\JSAOBDSL_19cols.xml');
GO
SELECT * FROM FL_HUM_Census;
truncate Table FL_HUM_Census
GO

INSERT into FL_HUM_Census
SELECT * FROM   OPENROWSET
( BULK 'D:\Sample_OpenRowSet\JSAOBDSL_2015-06-18.csv',
FORMATFILE = 'D:\JSAOBDSL_19cols.xml',FIRSTROW = 2)t;

Select * from ETLTableMetaData
Select * from ETLTables