0

Planning to import 1000s of text file to sql server tables. All these files are having different structures and it goes to corresponding new tables in SQl. Different methods coming in mind is using of Biml /creating ssis packages with number of data flows/using import wizard.

What is the ssis design pattern to achieve this in most time efficient way. This is a onetime load though.

How to handle the failure ? : I am not considering the checkpoint because ,when Control Flow tasks are run in parallel checkpoints act a little erratically.

user1254579
  • 3,901
  • 21
  • 65
  • 104
  • 2
    What have you tried so far to import the files? Are they delimited text files? Comma, Pipe, etc? Each one has a different structure? So there are 1000s of structures? – SS_DBA Oct 21 '16 at 12:15
  • yes there are 1000s of structures and these are a pipe delimited files – user1254579 Oct 21 '16 at 12:17
  • 1
    Did you ever get an answer to this? http://stackoverflow.com/questions/23059397/handling-files-with-different-structure-for-each-loop-ssis?rq=1 – SS_DBA Oct 21 '16 at 12:25
  • 1
    See http://stackoverflow.com/questions/2830711/create-a-new-table-and-import-data-from-csv-file-into-sql-server-2005 – SS_DBA Oct 21 '16 at 12:31
  • Thanks.The Import and Export tool in SQL Server would do.This is a one time load.So there is no need to create apackages for this 1:1 mapping – user1254579 Oct 21 '16 at 12:45
  • As a one time job, my advice would be to use whatever method you are most comfortable with. If you already proficient with BIML this would probably be the fastest method. – David Rushton Oct 21 '16 at 12:51

1 Answers1

1

This might give you a starting point if you want to see what each file has for data, without opening every one of them. The DefaultDir will reside on your SQL Server that you are running from.

SELECT * FROM  
OPENROWSET ('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\PathtoFiles',
'select * from FileName.csv');
SS_DBA
  • 2,403
  • 1
  • 11
  • 15