I’m not a C# or VB developer can someone help me with C# or VB code that will import an excel spread sheet into SQL Server database and build the SQL Server table on the fly.
I need to develop an ETL Process that needs to load an excel spread sheet into a SQL Server Database. The amount of columns will be dynamic, but I do know that the name of the first three columns and the rest of the columns will be integer values. For example the spread sheet structure will be like so
My first idea was to use a distributed query like below, but this is not allowed in current environment that I’m in.
SELECT * into NEW_TABLE FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
So I think my best option is script task and use C# or vb code that will do the same thing as the SQL code above.