i have a website project making an online catalog, maybe 20 categories, a few hundred items.  the small business starter kit looked perfect to jump start this.  the client has provided an access database with the two table for categories and products.  i've got the dts wizard installed and have been trying to import the access data into the sbsk database but i'm stuck.  i'm almost positive that ONE time when i was banging around with the dts wizard, the Edit Mapping screen would let me drop down a list of fields in the destination table so I could actually tell it which field data from the source i wanted to go into which field of the destination.  Of course it didn't succeed that time and now i can't seem to figure a way to get back to having the option to choose the destination fields, all it gives me in the drop down is "<ignore>" or the field name of the source table, which of course doesn't match any of the ones that are in the sbsk.

i've been back through it for a couple days, trying different ways, migrating the data first to sql, letting it try a transfer to a new table then trying to import that, moving both out to excel, aligning the data manually and trying to import that, etc.  i'm bout out of ideas and would sure like to get back to just trying to work out the data type alignment issues with whatever it was i did when i could choose the destination table's fields.

i'd be most appreciative if anyone has a clue what i'm talking about and can put me back on track.

matthew

Follow this Blog Post http://cfpayne.wordpress.com/2006/07/23/how-to-import-microsoft-access-into-sql-server-2005/

Thank you for the reply Kamran.  I have seen that article and used it to *convert* the tables to sql but that is as far as I've gotten with it, I haven't been able to figure out how to *import* the data into the existing tables that come with the Small Business Starter Kit.  Maybe I'm using the wrong words, I'm new at this, perhaps I mean *insert* or *update*?

In other db work/apps I've messed with there has been a "mapping" component included, so for example, you could direct the data contained in a field called "name" in the source table to go into a field called "title" in the destination table.

My goal is to avoid hand entering the several hundred items the client already has in the Access table into the SBSK or, probably more importantly, having to learn how to re-write the SBSK so it will use the field names from the Access db if all I can do is convert it SQL, not import the data into an existing SQL table.

Sorry if I'm long winded, my first guess is I'm too ignorant to explain myself well.  If there is a way in the article you mentioned to do what I need, could you tell me about where it is in there?  I haven't found it.

Thanks again.

Matthew

I never tried this with access database, but I'm sure theres is an option called "Edit Mappings" that would allow us to map columns. This would be available in "Select source tables" option of the import wizard.

Thanks XPSCodes, there is and like I said in my first post, it doesn't, even after I first convert the Access table to SQL.  Probably lost in my longwindedness, trying to be thorough.

That was kind of my question, what's the trick to getting it to let you choose the destination fields?  I think I saw it one time but then lost it and never again, not sure what exact path I took to get there or even which tables I was experimenting with.  I think there must be a prior choice in the DTS Wizard that get you to a screen with a different set of options or maybe it happened when I was working with an exported set and all the data types were plain text, or...?

Thanks again for the reply.

To give it a try, I downloaded a sample access database from this page http://www.blueclaw-db.com/download/download_inventory_calculations.htm

Here are the steps I followed:

1) Right click on the database in Management studio/Tasks/Import Data

2) In source, Set DataSource as Access and select the database.

3) In Destination, Set the Target database.

4) In next screen, selected the first option - Copy data from one or more tables or views

5) Here I selected the tables on the right(Source),  From traget, selected the target table name. Clicked edit mappings, Under destination, selected the destination column name, Click Ok to continue.

 Is this what you looking for or something else. My understanding could be wrong here.

that's exactly what i'm looking for but my results after clicking Edit Mappings, under Destination, the only choices I'm given are the source field's name or <ignore>, there is no listing of the destination table's field names to choose from.

Also, if I click the Edit SQL button above, the statement starts with "CREATE TABLE", which I think is odd if I've chosen to "Copy data from one or more tables" but the limited choice of field names would actually make sense if I was creating a table.

After clicking Next, the following appears:

Click Finish to perform the following actions:

Copy rows from `category` to [C:\WEBSITES\SMLBZQSK\APP_DATA\SMALLCOMPANYDB.MDF].[dbo].[category]
The new target table will be created.

The package will not be saved.
The package will be run immediately.

After clicking Finish, it attemps to perform the operation but stops on Prepare for Execute and errors out on Executing with the following:

Error 0xc002f210: Preparation SQL Task: Executing the query "CREATE TABLE [C:\WEBSITES\SMLBZQSK\APP_DATA\SMALLCOMPANYDB.MDF].[dbo].[category] (
[name] nvarchar(50),
[parent] int
)
" failed with the following error: "There is already an object named 'category' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If you can shed any light on this, or if I can provide any other info that might help, please let me know.

Thanks again.

 Just to make sure, Did you set the destination table to Category, before you clicked Edit Mappings ? Once destination table is set, under <ignore> in edit mappings, the destination table's column will get listed.

yes.  just to make sure, since there's a "category" table in either db, i renamed the one in Access to cat2.  Still shows in the Destination field of the Edit Mappings screen only the name of the source field and <ignore>.

i think i mentioned previously i am using SQL 2005 Express and the Data Transfer Wizard you from downloading the Toolkit that's available for SQL Express.  my understanding is it's the same thing as what pops up if you're using the full SQL 2005 but maybe this is a limitation, that you can only create new tables and not copy (INSERT) data?  ugh, hope not.

thanks.

matthew

Oh I completely missed that point. I was trying this in SQL Server Developer Edition, My bad.

If its a one time requirement, you could download the Developer edition trial and do the migration. I have not used the toolkit, cant really comment about it, Sorry!.

ok, i'll give that a shot.  i have this posted in the asp starter kit forum but no help there either, which is kind of surprising, i'd think a lot of people trying to use these kits would want to insert their data first thing.

thanks again, i'll let you know if the trial does the trick.

ps.

another buddy pointed out that i may run into problems when the insert runs into the foreign key required in the table.  any thoughts on that?

In that case, we have to migrate the tables with primary keys first and then do the dependent tables.