1

I could not find the answer to this, and I think it's easy :)

Using SSIS, I want to import from excel into SQL table, independently of the number of columns, I do not care if the eclectic has 3 or 33 columns.

How to accomplish something like this: select * into mytable from exceldataset,

if you can not use the OPENROWSET function and execute SQL task.

Hadi
  • 36,233
  • 13
  • 65
  • 124
NeshaSerbia
  • 2,324
  • 2
  • 14
  • 13
  • Could you indicate the expected result of what you need to obtain? I have an idea to evaluate columns, but I do not know if that's what you need. – arce.est Jan 09 '19 at 16:57
  • Ok, Excel has 3 columns (A, B, C) and I want to have a table with ABC column in the database. After some calculations, I will drop this table. The next time I have excel that has 5 columns and then I want to create a table from these 5 columns (select * into ..)... – NeshaSerbia Jan 09 '19 at 17:01
  • a simple option is to recreate your destination table, before the process ... or if you want to keep historical information? **IF OBJECT_ID('mytable','U') IS NOT NULL DROP TABLE mytable** – arce.est Jan 09 '19 at 17:10
  • Every time I Want to create table, beacuse not every time excel has same number of columns. – NeshaSerbia Jan 09 '19 at 17:12
  • @NeshaSerbia why you cannot use `OPENROWSET` or `Execute SQL Task`?? I think this is the only way that you can do it without creating the package programmatically or using BIML. Or you have to convert the Excel file into csv file and import it – Hadi Jan 09 '19 at 22:24
  • @NeshaSerbia anything new?! – Hadi Jan 27 '19 at 20:14

2 Answers2

0

This has been answered many times before. In SSIS, the metadata for your source is stored with the package. It cannot change. The only way to do this is by programmatically altering the data source definition in your package, which is obviously not trivial. You need a script task and dive into the APIs. It is easier to change the source file, either in Excel by flattening, unpivoting, or whatever is needed, or in the system that generates it.

Check out this question not for an answer, but for some pointers. It actually deals with different files, not columns.

cdonner
  • 37,019
  • 22
  • 105
  • 153
0

The easiest way to achieve that is using Execute SQL Task with a dynamic OPENROWSET command.

Since you mentioned that you cannot use this method i think that you must convert you Excel file into a csv file using a Script Task then import the Flat File into the SQL.

You can refer to the following links for more information:

Convert Excel into csv using Script Task

Import Flat File with different Header

If your Excel file has the same columns number but the headers are variables

Other helpful articles

If you don't want one of these approaches you have to generate your packages programmatically or using BIML for each Excel file because SSIS need to validate the metadata of the Excel files and does not support multiple excel structure to be imported using the same Excel Source

Hadi
  • 36,233
  • 13
  • 65
  • 124