|
|
 |
 |
Joined: 12/30/2010
Posts: 190
|
|
|
So to elimate a lot of manual labor I am creating a spreadsheet that pretty much mimics the ProductQtyDiscount table in sql. Downloaded it, edited some data, and the reupload is where I lose.
Looks like there is a lock or a read only column in the table, can anyone confirm? I had success when I imported to a new table (it creates it automatically) called ProductQtyDiscountTEST. Worked like a charm.
I have no problem deleting and recreating the table, I just want to make sure that if I do delete the existing table that I am not also deleting something else. I don't see any relationships or anything...
I also noticed that in the data types for all of the columns except 'price' are listed as int, but in the excel spreadsheet they are characterized as 'double'. Not sure if this is the reason or not, but 'double' is not a data type that I can choose in the table design.
Thanks for any help or insight!
|
|
|
|
 | |
|
|
 |
 |
Joined: 3/11/2010
Posts: 1979
|
|
|
Are you trying to import the identity column (first column) if so then when you import you need to check the box for enable identity inserts.
-Scott
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
Joined: 9/7/2010
Posts: 117
|
|
|
I used the template xls located here.
http://www.smith-consulting.com/forums/forumid/4/threadid/4993/scope/posts.aspx
I am using 3.9.7 and this template is not mapped correctly.
I tried figuring out the mapping but it is missing full columns of data (like ModelName).
If you need a sample xls let me know.
|
|
|
|
| |
|
|
|
Joined: 7/28/2009
Posts: 1452
|
|
|
Reading your first post I thought you said you were trying to import to the ProductQtyDiscount table. Are you using the built in product import in the cart? If so post your xls and I will take a look.
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
Joined: 9/7/2010
Posts: 117
|
|
|
Dave, I am a different user who didn't want to create a new thread. I can create a new thread if you wish.
Spreadsheet is attached.
|
|
|  | Login to download attachment |
|
|
|
| |
|
|
|
|
Joined: 12/30/2010
Posts: 190
|
|
|
skelly wrote:
Are you trying to import the identity column (first column) if so then when you import you need to check the box for enable identity inserts.
-Scott
When I do that I get a little father, but now I get this error message:
- Copying to [dbo].[Smith_ProductQtyDiscounts] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Smith_ProductQtyDiscounts'. Cannot insert duplicate key in object 'dbo.Smith_ProductQtyDiscounts'.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (42)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (42)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Smith_ProductQtyDiscounts" (29) failed with error code 0xC0209029 while processing input "Destination Input" (42). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
|
|
|
|
| |
|
|
|
|
Joined: 12/30/2010
Posts: 190
|
|
|
It seems like I get the same errors over and over again. I'm trying this with the category table as well and I get all sorts of errors, importing a spreadsheet that I just exported should not be that hard! all I am adding now is seo tags (in accordance to what you have listed; lowercase letters, numbers, etc, ONLY) in mass quantities and uploading through sql will be the easiest.
If I try to import into the existing table I get an error, importing in a new table works fine. I just need to know if I can delete the existing table and create a new one with the same name, not sure what that will screw up.
I don't know if excel is changing the source types, but I think that is the underlying issue. I'll report back more on my findings.
|
|
|
|
| |
|
|
|
|
Joined: 3/11/2010
Posts: 1979
|
|
|
Did you try deleting the data in the Smith_ProductQtyDiscounts before importing your xls?
-Scott
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
|
Joined: 12/30/2010
Posts: 190
|
|
|
I guess thats what I was kinda waiting to know, whether or not me deleting the entire table will screw anything up. Deleteing the rows themselves seems to have worked ok.
Will deleting and re-creating the entire table do any harm at all?
|
|
|
|
| |
|
|
|
|
Joined: 3/11/2010
Posts: 1979
|
|
|
I dont see a reason to delete the table just delete the data in the table.
-Scott
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
Joined: 9/7/2010
Posts: 117
|
|
|
When you get the PK error it is because you don't have the auto-identity set on the table or you are trying to insert a primary key that is already there.
Do I need to start a new thread for the XLS import for product table?
Thanks
Chad
|
|
|
|
| |
|
|
|
|
Joined: 12/30/2010
Posts: 190
|
|
|
yea going through and just deleteding the info that was in the table seems to work fine, I just didn't want to do it right away without knowing that it wouldn't screw anything up. Originally I wanted to delete the table; didn't see any relationships tied to it but deleting data works just fine.
|
|
|
|
| |
|
|
|
Joined: 4/5/2010
Posts: 148
|
|
|
Deleting the entire table will automatically reset the identity field counter.
|
|
|
|
| |
|
|
 |
 |
Joined: 5/28/2010
Posts: 471
|
|
|
ahhendela wrote:
Deleting the entire table will automatically reset the identity field counter.
Really? I don't think it will.... but it's been a while since I've done it.
I would:
1) Truncate the table then
2) Enable Identity insert
|
|
|
|
| |
|
|
|
Joined: 4/5/2010
Posts: 148
|
|
|
There are a few ways to reset the identity field:
1) Drop and Create. Since the drop physically removes the table, all values including the identity field is gone. When you do the Create to recreate the table, the value is reset to the beginning value. You need to understand all the table dependencies before you do this, but it will reset the value. See http://msdn.microsoft.com/en-us/libra...
2) TRUNCATE TABLE tablename -- this will clear the data AND reset the IDENTITY. You cannot use this if the identity field is referenced by a FOREIGN KEY constraint. Source: http://msdn.microsoft.com/en-us/libra...
3) Use DBCC CHECKIDENT to reseed the nex t value. See: http://technet.microsoft.com/en-us/li...
Regards,
Art
|
|
|
|