Lucas the strategy depends on the details.

I'm not anti -SSIS, but if it can be done via TSQL, then it can be done a lot faster.

if the destination tables already exist, then you can easily use BULK insert and some dir commands via xp_cmdShell;

if the tables don't exist yet, you could use a linked server, which points to a folder full of files, and do a SELECT * INTO NEWTABLE FROM MyTextLinkedServer...TableName.

I have script examples of both, and the one i'm posting below is assuming "tables don't exist yet, create on the fly."

a couple of prerequisites if they are not in place already:

install the AccessDatabaseEngine_x64.exe from microsoft:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive

command line flag;

this will force the install of the drivers, even if you have 32 bit office installed;

otherwise you get some error about 32 bit Office preventing the install.

After that is isntalled:

--Required settings for the provider to work correctly as a linked server

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

and then the code for the linked server:

--#################################################################################################

--Linked server Syntax for Folder Full Of Text Files

--#################################################################################################

--add a folder as a linked server to access all .txt and .csv files in the folder

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

SET @server = N'myFolderFullOfFiles'

SET @srvproduct = N'OLE DB Provider for ACE'

SET @provider = N'Microsoft.ACE.OLEDB.12.0'

SET @datasrc = N'C:\Data\'

SET @provstr ='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\;Extended Properties="text;HDR=YES;FMT=Delimited" '

set @provstr = 'Text'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,@provstr,@provstr

--===== Create a linked server to the drive and path you desire.

--EXEC dbo.sp_AddLinkedServer myFolderFullOfFiles,

-- 'MSDASQL',

-- 'Microsoft.ACE.OLEDB.12.0',

-- 'C:\',

-- NULL,

-- 'Text'

GO

--===== Set up login mappings.

EXEC dbo.sp_AddLinkedSrvLogin myFolderFullOfFiles, FALSE, NULL, Admin, NULL

GO

and here's the core workload: get all the tables available in the text linked server, and loop through them all.

--===== List the tables in the linked server which is really a list of

-- file names in the directory. Note that the "#" sign in the

-- Table_Name is where the period in the filename actually goes.

CREATE TABLE [#Results] (

[ResultsId] INT IDENTITY(1,1) NOT NULL,

[TABLE_CAT] VARCHAR(128) NULL,

[TABLE_SCHEM] VARCHAR(128) NULL,

[TABLE_NAME] VARCHAR(128) NULL,

[TABLE_TYPE] VARCHAR(128) NULL,

[TABLE_REMARKS] VARCHAR(128) NULL,

CONSTRAINT [PK__Results_ID] PRIMARY KEY CLUSTERED ([ResultsId]) )

INSERT INTO [#Results](TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,TABLE_REMARKS)

EXEC dbo.sp_Tables_Ex myFolderFullOfFiles

--now a cursor to get all the table sinto the current database.

--note this assumes the table doesn't exist at all, maybe add drop if exists?

declare

@isql varchar(2000),

@tbname varchar(64)

declare c1 cursor for select TABLE_NAME from [#Results]

open c1

fetch next from c1 into @tbname

While @@fetch_status <> -1

begin

select @isql = 'SELECT * INTO ' + quotename(@tbname) + 'FROM myFolderFullOfFiles...' + quotename(@tbname)+';'

print @isql

exec(@isql)

fetch next from c1 into @tbname

end

close c1

deallocate c1

--===== Drop the text server

-- EXEC dbo.sp_DropServer 'myFolderFullOfFiles', 'DropLogins'

I just tested this exact scenario, and create 12 tables on the fly, based on the 12 txt/csv files that happen to exist in my C:\Data folder.