SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help !! Create dynamically table destination


Help !! Create dynamically table destination

Author
Message
Lidou123
Lidou123
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 380
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19155 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Lidou123
Lidou123
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 380
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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19155 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4160 Visits: 2629
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
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 615
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
Lidou123
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 380
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
Lidou123
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 380
So, Can I do the same with import/export fonctionnality.

Can I automate an import/export functionnality with SSIS ?
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19155 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Lidou123
Lidou123
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 380
Phil,

I will use it like a staging table.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search