Create table from file and then load the data

• g_demetriou

  Ten Centuries

  Points: 1168

  More actions

  October 15, 2013 at 2:46 am

  #278075

  Hi,

  is there a way via SSIS to create a table to match a file's headers (which can change), and then load that data into the table? I've been looking at BCP, batch load, & script task but can't find the best way.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  October 15, 2013 at 2:52 am

  #1658438

  g_demetriou (10/15/2013)

  ---

  Hi,

  is there a way via SSIS to create a table to match a file's headers (which can change), and then load that data into the table? I've been looking at BCP, batch load, & script task but can't find the best way.

  Nothing easy. So you want a new table every time? What would it be called? How would the column data types be determined?

  ---

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  October 15, 2013 at 2:57 am

  #1658439

  You'll need to read the first line of the file (easiest way is a script task), get the individual column names and create a dynamic CREATE TABLE statement using those columns. The easiest method is to give every column a fixed data type, such as varchar(250) NULL.

  After the table is created, you can use bcp or BULK INSERT to import the flat file into the table.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• g_demetriou

  Ten Centuries

  Points: 1168

  More actions

  October 15, 2013 at 4:43 am

  #1658456

  Thanks. I'll give that a go