Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dylanmendes
Starting Member
7 Posts |
 Posted - 2005-06-04 : 09:59:10
|
| Hello People,Here's another DTS question. I intend to import either an Excel file or an Access table into SQL Server 2000. However, the DTS package prompts me to supply the schema of the destination table and the column mapping between source and destination columns. I don't want to do this as the schema of my source is not fixed and I need to use the same package to import different data conforming to different schema.Is there some way I could simply select my source file and have the package create a destination "default" table? Thanks!Dylan |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-06-04 : 21:38:51
|
| Hi DylanYes if you leave it as the default destination it will do exactly that, it comes up with some horrible name based of the worksheet name. Once that data is in, you can use some SQL to insert the data you want into a final destination.DamianIta erat quando hic adveni. |
 |
|
|
dylanmendes
Starting Member
7 Posts |
Posted - 2005-06-05 : 02:42:34
|
| Thank you (again) Damian!I must be doing something wrong, though. Here are the steps I'm going through:1) I create a package and add the source excel file2) I then select the destination SQL server. At this point, I also have to select the database I want to work with.3) I create a transformation task between source and destination. My problem is that at this point I'm asked to select a TABLE to import into. What's more - I'm also required to supply the mapping between the source and destination column sets.So, I thought to myself "maybe I needn't create the transformation task". I tried the package with the same elements but minus the transformation task. It executed without an error, but I couldn't find the table that, I expect, should've been created!Again, I know I'm missing something.. could someone please fill in the blank(s).Dylan |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-06-06 : 00:29:56
|
| Hi DylanI just figured out what this is about... Instead of starting with a blank DTS package, right click on your DB in enterprise manager and choose Import Data. This will step you through a wizard that will generate a new table that matches your spreadsheet.If you need to automate this, at the end of the wizard you have the chance to save the import as a DTS package. You can save this to VB code which you can integrate into an application. If you know some vb you could add it in here to be a bit more dynamic.DamianIta erat quando hic adveni. |
|
|
|
|
|
|
|
|
Importing from Excel/Access using DTS.