0

There are hundreds of flat files

  • for each file i need to create a table in SQL-server and enter the file contents into the table

    example :
Flat_Files:             SQL-SERVER Table:
FileA.txt               fileA
FileB.txt               fileB
FileC.txt               fileC  
... and so on.

how to achieve this in SSIS.

Ps: i am beginner to all of this.

jpn
  • 43
  • 9
  • What have you tried so far, and where did you start to get stuck? – Thom A Feb 26 '19 at 10:57
  • ForEach Item Enumerator, some examples: - http://www.sqlerudition.com/example-of-ssis-foreach-loop-item-enumerator-and-dynamic-oledb-connection/ and another one - https://www.codeproject.com/Tips/803195/Foreach-File-Enumerator-in-SSIS – Alexander Volok Feb 26 '19 at 11:00
  • @Larnu i tried using ForEachLoop container – jpn Feb 26 '19 at 11:00
  • Could you show us what you tried? What were the problem you had? Why didn't it work? – Thom A Feb 26 '19 at 11:01
  • Also, i ***assume*** that these tables already exist on your SQL Server, correct? you just need the ETL process to get from the file to the table. – Thom A Feb 26 '19 at 11:01
  • @Larnu No they are not. I tried with few files as source files but i couldn't figure out how to send each file to a new table. i.e, create a new table according to file name – jpn Feb 26 '19 at 11:04
  • 1
    That's a problem. How do you propose that SSIS "works out" what datatype all of your columns are? CSV files are text files, they don't have datatypes, however, that doesn't mean that your data should all be stored as text. This sounds more like you need to design your database first, and then import the data afterwards. SSIS isn't going to help you with the design phase. – Thom A Feb 26 '19 at 11:06
  • @Larnu I tried also by creating few tables and but couldn't figure out how to send each file's data to corresponding table – jpn Feb 26 '19 at 11:07
  • Also, SSIS isn't going to help you if all the files have different definitions. SSIS requires static definitions, so if each file differs in the datatypes/columns it contains you would need to build a different dataflow task, or at least flow within the same task, for every file. – Thom A Feb 26 '19 at 11:08
  • @Larnu file's data have same datatype(string) no problem in that end. Can i excute this task if database tables are already created according to file names. – jpn Feb 26 '19 at 11:12
  • So every table has the same definition and every file does too? – Thom A Feb 26 '19 at 11:13
  • @Larnu datatype is same but column name is different for each file & table. so if this is the case can i move multiple files data to respective tables? – jpn Feb 26 '19 at 11:16
  • 1
    No, if the columns differ they don't have static definitions. You'll need to build a separate flow for each file if you want to use SSIS. Like i said, SSIS does not support dynamic definitions; they must be static. – Thom A Feb 26 '19 at 11:17
  • @Larnu can you please look at this and tell if this is possible http://www.techbrothersit.com/2016/04/how-to-load-flat-files-to-sql-server.html thank you in advance – jpn Feb 26 '19 at 11:20
  • https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/ – Yahfoufi Feb 26 '19 at 13:43

1 Answers1

1

Try using Dynamic SQL in a Script task. For each file(use for-each loop container) get the header and file name to create a table and then insert the data into that table using Dynamic SQL.

This should be your last option.

If all the files are .xlsx you can do this using ExecuteSQL Task.

Srikar mogaliraju
  • 215
  • 1
  • 10
  • While this is a good approach to the problem, you'll run into problems defining the data types for each column dynamically. – digital.aaron Feb 26 '19 at 16:27
  • I don't think there would be an easy solution to define the column datatypes for 100 files. The only option is to set all the column datatypes to nvarchar. – Srikar mogaliraju Feb 26 '19 at 17:24