Help !! Create dynamically table destination

Question

Help !! Create dynamically table destination

Lidou123

Hall of Fame

Points: 3186
More actions
January 10, 2013 at 4:03 am

#147804

Hi All,
Please I need your help.
I have an excel file File.xls that i want to load in a table TempTable. But my File.xls can have columns that change everyday.
I think that If I create my table in runtime with the structure of the excel file, I will solve my problem.
Please can U help me to create table dynamically with the same table structure that the columns of excel file.
Thank U

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

Lidou123 (1/10/2013)
Hi All,
Please I need your help.
I have an excel file File.xls that i want to load in a table TempTable. But my File.xls can have columns that change everyday.
I think that If I create my table in runtime with the structure of the excel file, I will solve my problem.
Please can U help me to create table dynamically with the same table structure that the columns of excel file.
Thank U

Very difficult - SSIS is meta data driven and changing meta data gives it a severe headache.

Also, if your columns change every day, how will you know which columns to map them to on TempTable? Is the structure of TempTable changing too?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 2

Phil Parkin (1/10/2013)

Lidou123 (1/10/2013)
Hi All,
Very difficult - SSIS is meta data driven and changing meta data gives it a severe headache.
Also, if your columns change every day, how will you know which columns to map them to on TempTable? Is the structure of TempTable changing too?

Hi Phil,

Thank You for your answer !

Yes the structure of TempTable change too.

The columns of the TempTable are same that the columns of the Excel File.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

Lidou123 (1/10/2013)

Phil Parkin (1/10/2013)
Lidou123 (1/10/2013)
Hi All,
Very difficult - SSIS is meta data driven and changing meta data gives it a severe headache.
Also, if your columns change every day, how will you know which columns to map them to on TempTable? Is the structure of TempTable changing too?
Hi Phil,
Thank You for your answer !
Yes the structure of TempTable change too.
The columns of the TempTable are same that the columns of the Excel File.

So the temp table needs to be dropped and recreated everytime the import runs, based on the contents of the Excel file.

There's no way I can think of doing this using standard SSIS components. If I were you, I would probably craft a fully scripted solution in a C# script task (after checking Google to see whether anyone else has already done it another way).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

I have heard of people using the Data Profiling task for something like this, but I don't know how they were doing it. You might want to poke around Google for a bit. I believe the guy's name is Ira Whiteside.

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 5

Gembox software makes a component that reads xls and xlsx files without using automation.

You could use it inside a CLR stored proc to return a result set with any number/type/names of column.

I suppose you could also open a connection inside it and write the data into table.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 6

Hi All,

I think I found the solution: http://www.sqlservercentral.com/Forums/Topic1232909-2674-1.aspx

But I don't know if it works with excel file?

What do U think ?

Lidou123 Hall of Fame Points: 3186 More actions · Answer 7

So, Can I do the same with import/export fonctionnality.

Can I automate an import/export functionnality with SSIS ?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 8

Lidou123 (1/10/2013)
Hi All,
I think I found the solution: http://www.sqlservercentral.com/Forums/Topic1232909-2674-1.aspx
But I don't know if it works with excel file?
What do U think ?

It won't work with Excel files without tweaking the code, that's for sure. But it's probably do-able.

How many rows do the spreadsheets typically contain? If more than a few thousand, this technique is likely to be slow.

What happens to the data once it has been imported? As the table is going to be dropped and recreated tomorrow, it has only a one-day lifespan - and as the column names are changing all the time, a new query will need to be written every day to use it (except for SELECT * FROM

, of course).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 9

Phil,

I will use it like a staging table.

After data in the table, I will use them easily with SSIS and T-SQL.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 10

Lidou123 (1/10/2013)
Phil,
I will use it like a staging table.
After data in the table, I will use them easily with SSIS and T-SQL.

If the column names and datatypes change every day, it may be easy but it will also be manual. If you're happy with that, you might as well keep the entire process manual and just use the import wizard.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 11

No :(.

I need to automate the process.

I have a headache. I will go to sleep.

May be tomorrow I will find the solution.

Tomorrow is another day.

Thank U Phil

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 12

try this , you could always play with datatypes once you have data in your table..

string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Documents\\Book1_3.xlsx;Extended Properties='Excel 12.0;HDR=NO;'";

OleDbConnection con = new OleDbConnection(constr);

con.Open();

DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,null);

con.Close();

SqlConnection sqlcon = new SqlConnection("Server=servername;Database=dbname;Trusted_Connection=True;Integrated Security=SSPI;");

string tblcreate = "Create Table ##TEMP_Excel(";

foreach (DataRow dr in dt.Select("TABLE_NAME='Sheet1$'"))

{

tblcreate = tblcreate + dr["COLUMN_NAME"].ToString() + " varchar(32)" + ",";

}

tblcreate=tblcreate.Substring(0, tblcreate.Length - 1);

tblcreate = tblcreate + " );";

sqlcon.Open();

SqlCommand cmd = new SqlCommand(tblcreate, sqlcon);

cmd.ExecuteNonQuery();

sqlcon.Close();

Lidou123 Hall of Fame Points: 3186 More actions · Answer 13

Lidou123

Hall of Fame

Points: 3186

January 25, 2013 at 11:45 am

#1581170

Thank U for your help. 🙂