November 6, 2008 at 1:38 pm
I have created a product database and I would like to integrate data from excel worksheets to various tables which are related in the data base. Secondly, my category table contain a parent category column, how would I fill this column.
November 6, 2008 at 8:39 pm
More information is needed. Table and Excel Schemas would be helpful. As would some sample data. See the links in my signature.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 7, 2008 at 8:30 am
This is my database schema and I am using SSIS to integrate data from an excel spreadsheet with the columns of tables Product, Manufacturer, category, and Productlines each with a separate worksheet. All the columns corresponds to that of the tables
SET QUOTED_IDENTIFIER ON
go
/* Create New Product database. */
use master
go
create database "New Product"
go
use "New Product"
go
/* Create new table "ProductManufacturer". */
/* "ProductManufacturer" : Table of ProductManufacturer */
/* "ProductID" : ProductID partly identifies ProductManufacturer */
/* "ManufacturerID" : ManufacturerID partly identifies ProductManufacturer */
/* "DistributorName" : DistributorName is of ProductManufacturer */
create table "ProductManufacturer" (
"ProductID" int not null,
"ManufacturerID" int not null,
"DistributorName" nvarchar(max) null) ON 'PRIMARY'
go
alter table "ProductManufacturer"
add constraint "ProductManufacturer_PK" primary key clustered ("ProductID", "ManufacturerID")
go
/* Create new table "Manufacturer". */
/* "Manufacturer" : Table of Manufacturer */
/* "Name" : Name partly identifies Manufacturer */
/* "SEKeywords" : SEKeywords is of Manufacturer */
/* "SEDescription" : SEDescription is of Manufacturer */
/* "SETitle" : SETitle is of Manufacturer */
/* "Address1" : Address1 partly identifies Manufacturer */
/* "Address2" : Address2 is of Manufacturer */
/* "Suite" : Suite is of Manufacturer */
/* "City" : City partly identifies Manufacturer */
/* "State" : State is of Manufacturer */
/* "ZipCode" : ZipCode is of Manufacturer */
/* "Country" : Country is of Manufacturer */
/* "Phone" : Phone is of Manufacturer */
/* "Fax" : Fax is of Manufacturer */
/* "URL" : URL is of Manufacturer */
/* "Email" : Email is of Manufacturer */
/* "Xmlpackage" : Xmlpackage is of Manufacturer */
/* "Description" : Description is of Manufacturer */
/* "ManufacturerID" : ManufacturerID identifies Manufacturer */
create table "Manufacturer" (
"Name" nvarchar(10) null,
"SEKeywords" ntext null,
"SEDescription" ntext null,
"SETitle" ntext null,
"Address1" nvarchar(max) null,
"Address2" nvarchar(max) null,
"Suite" nvarchar(max) null,
"City" nvarchar(max) null,
"State" nvarchar(max) null,
"ZipCode" nvarchar(10) null,
"Country" nvarchar(max) null,
"Phone" nvarchar(max) null,
"Fax" nvarchar(max) null,
"URL" nvarchar(max) null,
"Email" nvarchar(max) null,
"Xmlpackage" nvarchar(max) null,
"Description" ntext null,
"ManufacturerID" int not null) ON 'PRIMARY'
go
alter table "Manufacturer"
add constraint "Manufacturer_PK" primary key clustered ("ManufacturerID")
go
/* Create new table "ProductCategory". */
/* "ProductCategory" : Table of ProductCategory */
/* "ProductID" : ProductID partly identifies ProductCategory */
/* "CategoryID" : CategoryID partly identifies ProductCategory */
/* "DispalyOrder" : DispalyOrder is of ProductCategory */
/* "DMECartID" : DMECartID is of ProductCategory */
create table "ProductCategory" (
"ProductID" int not null,
"CategoryID" int not null,
"DispalyOrder" int null,
"DMECartID" int null) ON 'PRIMARY'
go
alter table "ProductCategory"
add constraint "ProductCategory_PK" primary key clustered ("ProductID", "CategoryID")
go
/* Create new table "Category". */
/* "Category" : Table of Category */
/* "Name" : Name is of Category */
/* "Description" : Description is of Category */
/* "SEKeyword" : SEKeyword is of Category */
/* "SETitle" : SETitle is of Category */
/* "ParentCategoryID" : ParentCategoryID is of Category */
/* "XmlPackage" : XmlPackage is of Category */
/* "SEName" : SEName is of Category */
/* "ImageFileOverride" : ImageFileOverride is of Category */
/* "Published" : Published is of Category */
/* "CategoryID" : CategoryID identifies Category */
create table "Category" (
"Name" nvarchar(max) null,
"Description" ntext null,
"SEKeyword" ntext null,
"SETitle" ntext null,
"ParentCategoryID" int not null,
"XmlPackage" nvarchar(max) null,
"SEName" nvarchar(max) null,
"ImageFileOverride" ntext null,
"Published" tinyint null,
"CategoryID" int not null) ON 'PRIMARY'
go
alter table "Category"
add constraint "Category_PK" primary key clustered ("CategoryID")
go
/* Create new table "ProductLines". */
/* "ProductLines" : Table of ProductLines */
/* "ProductID" : ProductID partly identifies ProductLines */
/* "VariantID" : VariantID identifies ProductLines */
/* "IsDefault" : IsDefault is of ProductLines */
/* "Name" : Name is of ProductLines */
/* "Descirption" : Descirption is of ProductLines */
/* "SEKeyword" : SEKeyword is of ProductLines */
/* "Size" : Size is of ProductLines */
/* "Color" : Color is of ProductLines */
/* "FroogleDescription" : FroogleDescription is of ProductLines */
/* "SKUSuffix" : SKUSuffix is of ProductLines */
/* "ManufacturerPartNumber" : ManufacturerPartNumber is of ProductLines */
/* "Price" : Price is of ProductLines */
/* "SalesPrice" : SalesPrice is of ProductLines */
/* "Cost" : Cost is of ProductLines */
/* "MSRP" : MSRP is of ProductLines */
/* "MAP" : MAP is of ProductLines */
/* "Inventory" : Inventory is of ProductLines */
/* "IsTaxable" : IsTaxable is of ProductLines */
/* "Published" : Published is of ProductLines */
/* "MinimumQuantity" : MinimumQuantity is of ProductLines */
/* "MaximumQuantity" : MaximumQuantity is of ProductLines */
create table "ProductLines" (
"ProductID" int not null,
"VariantID" int not null,
"IsDefault" int null,
"Name" nvarchar(max) not null,
"Descirption" nvarchar(max) null,
"SEKeyword" nvarchar(max) null,
"Size" nvarchar(max) null,
"Color" nvarchar(max) null,
"FroogleDescription" nvarchar(max) null,
"SKUSuffix" nvarchar(max) null,
"ManufacturerPartNumber" nvarchar(max) not null,
"Price" smallmoney not null,
"SalesPrice" smallmoney null,
"Cost" smallmoney not null,
"MSRP" smallmoney not null,
"MAP" smallmoney null,
"Inventory" int null,
"IsTaxable" tinyint null,
"Published" tinyint null,
"MinimumQuantity" int null,
"MaximumQuantity" int null) ON 'PRIMARY'
go
alter table "ProductLines"
add constraint "ProductLines_PK" primary key clustered ("ProductID", "VariantID")
go
/* Create new table "Product". */
/* "Product" : Table of Product */
/* "Name" : Name is of Product */
/* "Type" : Type is of Product */
/* "Description" : Description is of Product */
/* "SEKeyword" : SEKeyword is of Product */
/* "SETitle" : SETitle is of Product */
/* "SKU" : SKU is of Product */
/* "XmlPackage" : XmlPackage is of Product */
/* "Colwidth" : Colwidth is of Product */
/* "SalesPromptID" : SalesPromptID is of Product */
/* "Published" : Published is of Product */
/* "RequiresRegistration" : RequiresRegistration is of Product */
/* "TrackinventoryBySizeAndColor" : TrackinventoryBySizeAndColor is of Product */
/* "ManufacturerPartNumber" : ManufacturerPartNumber is of Product */
/* "TrackInventoryBySize" : TrackInventoryBySize is of Product */
/* "TrackInventoryByColor" : TrackInventoryByColor is of Product */
/* "Iskit" : Iskit is of Product */
/* "IsAPack" : IsAPack is of Product */
/* "ImageFilenameOverride" : ImageFilenameOverride is of Product */
/* "IsActive" : IsActive is of Product */
/* "NoStartEnd" : NoStartEnd is of Product */
/* "StartList" : StartList is of Product */
/* "EndList" : EndList is of Product */
/* "ProductID" : ProductID identifies Product */
/* "SEDescription" : SEDescription is of Product */
create table "Product" (
"Name" nvarchar(max) null,
"Type" nvarchar(max) null,
"Description" nvarchar(max) null,
"SEKeyword" nvarchar(max) null,
"SETitle" nvarchar(max) null,
"SKU" nvarchar(10) null,
"XmlPackage" nvarchar(max) null,
"Colwidth" int null,
"SalesPromptID" int null,
"Published" tinyint null,
"RequiresRegistration" nvarchar(10) null,
"TrackinventoryBySizeAndColor" nvarchar(10) null,
"ManufacturerPartNumber" nvarchar(max) null,
"TrackInventoryBySize" int null,
"TrackInventoryByColor" int null,
"Iskit" int null,
"IsAPack" int null,
"ImageFilenameOverride" nvarchar(10) null,
"IsActive" nvarchar(10) null,
"NoStartEnd" smalldatetime null,
"StartList" smalldatetime null,
"EndList" smalldatetime null,
"ProductID" int not null,
"SEDescription" ntext null) ON 'PRIMARY'
go
alter table "Product"
add constraint "Product_PK" primary key clustered ("ProductID")
go
/* Add foreign key constraints to table "ProductManufacturer". */
alter table "ProductManufacturer"
add constraint "Manufacturer_ProductManufacturer_FK1" foreign key (
"ManufacturerID")
references "Manufacturer" (
"ManufacturerID") on update no action on delete no action
go
alter table "ProductManufacturer"
add constraint "ProductManufacturer" foreign key (
"ProductID")
references "Product" (
"ProductID") on update no action on delete no action
go
/* Add foreign key constraints to table "ProductCategory". */
alter table "ProductCategory"
add constraint "ProductCategory" foreign key (
"CategoryID")
references "Category" (
"CategoryID") on update no action on delete no action
go
alter table "ProductCategory"
add constraint "ProductCategory" foreign key (
"ProductID")
references "Product" (
"ProductID") on update no action on delete no action
go
/* Add foreign key constraints to table "ProductLines". */
alter table "ProductLines"
add constraint "Product" foreign key (
"ProductID")
references "Product" (
"ProductID") on update no action on delete no action
go
/* This is the end of the Microsoft Visual Studio generated SQL DDL script. */
November 7, 2008 at 1:46 pm
You can simply set up a data flow task for each table with an excel source and a sql server destination.
To get the ParentCategory from the Category worksheet, if you have, you would need a new data flow task with an excel source, a lookup to get the ParentCategoryID, a lookup to get the CategoryID, and an OLE DB Command transform that updates the ParentCategoryID for the given CategoryID.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply