Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help !! Create dynamically table destination Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 4:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:04 PM
Points: 69, Visits: 280
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
Post #1405329
Posted Thursday, January 10, 2013 5:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1405348
Posted Thursday, January 10, 2013 7:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:04 PM
Points: 69, Visits: 280
Phil Parkin (1/10/2013)
[quote]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.
Post #1405419
Posted Thursday, January 10, 2013 7:15 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
Lidou123 (1/10/2013)
Phil Parkin (1/10/2013)
[quote]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).



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1405426
Posted Thursday, January 10, 2013 9:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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.
Post #1405523
Posted Thursday, January 10, 2013 11:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:16 PM
Points: 107, Visits: 511
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.
Post #1405578
Posted Thursday, January 10, 2013 1:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:04 PM
Points: 69, Visits: 280
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 ?
Post #1405635
Posted Thursday, January 10, 2013 1:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:04 PM
Points: 69, Visits: 280
So, Can I do the same with import/export fonctionnality.

Can I automate an import/export functionnality with SSIS ?
Post #1405636
Posted Thursday, January 10, 2013 1:54 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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 [table], of course).



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1405639
Posted Thursday, January 10, 2013 2:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:04 PM
Points: 69, Visits: 280
Phil,

I will use it like a staging table.

After data in the table, I will use them easily with SSIS and T-SQL.
Post #1405640
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse