|
Posted by Eddie B on November 14, 2005, 2:38 pm
Please log in for more thread options Hi Rayan
I saw your posting under (Re: Microsoft RMS and SQL Server 2000 (MSDE) or
Server) on the MSRMS NG. I have been discussing with two different VARs who
are competing to provide us with a connector for plugging our webstores onto
the MS RMS DB, and their opinions in regards to the use of DTS in this
application differ. Would you care to read about their positions and weigh in
your .2?
Thanks,
Eddie B
"Rayan" wrote:
> Hi Glenn and Kent,
> I am from the Microsoft SQL Server Support team who did support Kent on
> the RMS solution with DTS .
> I should thank you guys for redirecting the SQL team to look into RMS which
> can potentially provide solutions for RMS using DTS.
>
> Thanks again
> rayan.
>
> "RMSrookie" wrote:
>
> > Thanks Glenn,
> >
> > My knowledge of SQL is from about 10 years ago and I had no clue that Data
> > Transformation Service was so powerful. After I read your post, I called
for
> > support and a Microsoft SQL Server Engineer walked me through loading data
> > from an excel spreadsheet directly into the 'item' table. There were about
> > 4500 items that loaded sucessfully.
> >
> > Here is a portion of the transcript the engineer created to document the
> > approach.
> >
> > I believe this is a safe procedure because columns that are declared "not
> > null"
> > will have a default data value of the correct type inserted in each case
> > where the source excel data is empty or null.
> > Glen, thanks for your tip.
> > If you or other RMS gurus see a problem with RMS database/product integrity
> > due to this technique, please advise.
> >
> > Thanks again for a terrific pointer.
> > Kent Smith
> > Consulting Systems Engineer
> > Columbus Park, LLC
> >
> >
> >
> >
> > Here is the transcript...
> > ================================
> > As we discussed, the issue you're experiencing is that you wanted to import
> > data from Excel to SQL Server. The data will be coming in from different
> > excel files with different columns which will be consolidated into a single
> > table called Item in SQL server. Once we resolve this issue we will
consider
> > this support incident completed and closed. We'll be working to resolve
this
> > specific issue through the course of the case. If I have misunderstood any
> > aspect of the issue, please let me know.
> >
> >
> >
> > Criteria for Resolution:
> >
> > To successfully import data from Excel file to SQL server that acts as a
> > back end database for retail management server.
> >
> >
> >
> > SUPPORT INCIDENT: An incident is a problem that cannot be broken down into
> > subordinate problems. For a request with subordinate problems, each problem
> > is considered a separate incident, and therefore must be submitted as a
> > separate support request.
> > http://support.microsoft.com/Directory/directory/policies.asp
> >
> >
> >
> >
> > Right now, we have agreed to do the following:
> >
> > I have delivered you the solution and you will be checking on it’s
> > feasibility for the next two days. Here is a summary of the steps we took to
> > create the package.
> >
> >
> >
> > 1 > Open up SQL server Enterprise Manager. Select your database , right
> > click select All Tasks> Import data.
> >
> > 2> In the wizard select Data Source as Microsoft Excel 97-2000. Specify the
> > input excel file.
> >
> > 3> For the destination server select the SQL Server database where you will
> > be importing the data.
> >
> > 4> Select Copy tables and views from the source database option. Click Next.
> >
> > 5> Select the table (Item) Click next. (Make sure you remove the $ sign else
> > the table will be created with the $ sign.
> >
> > 6> If you are creating the item table for the first time select Run
> > Immediately and Save the DTS package.
> >
> > 7> If the table is already present, do not select run immediately option,
> > Save the package and exit of the wizard.
> >
> > 8> Go to the folder Data Transformation Services > Local Pacakages. Open the
> > package that we just saved.
> >
> > 9> Remove the Create table task from the designer. Select the Data Pump Task
> > between the SQL and Excel connection.
> >
> > 10> Here you can Modify the column mappings from source to destination.
> >
> >
> >
> > Also you wanted to know how can you insert data into a column which does not
> > allow NULL value. I informed you that we cannot issue a insert statement to
a
> > table and skip the column value if it is defined as NOT NULL. The work
around
> > I suggested was modifying the table schema to allow NULLS and also if
> > necessary supply a default value to be inserted in case NULL value is not
> > acceptable.
> >
> > Also the column named ID which was the primary key needed to be incremental
> > for each insert statement. I guided you to convert the datatype of this
> > coulmn to Identity.
> >
> > ====================================
> >
> >
> >
> >
> > "Glenn Adams [MVP - Retail Mgmt]" wrote:
> >
> > > The RMS db is just another SQL Server Database, so any techniques that you
> > > are familiar with in SQL Server are certainly feasible. I'm not sure
> > > Replication makes sense for a data load - I'd lean more toward DTS, but
> > > whatever technique you like...
> > >
> > > The primary tables required to load items are:
> > > Item
> > > Supplier
> > > SupplierList (All suppliers for the item get an entry in this table -
> > > the supplier in Item.SupplierID is the "Primary supplier")
> > > Department
> > > Category
> > > ItemTax
> > >
> > > --
> > > --
> > > Glenn Adams
> > > Tiber Creek Consulting
> > > http://www.tibercreek.com
> > > glenn@tibercreek.com
> > > ----------------------------------------------
> > > Please DO NOT respond to me directly but post all responses here in the
> > > newsgroup so that all can share the information
> > >
> > > > Is it feasible to use SQL Server Replication or Merge processing to:
> > > > 1. Do the initial load of a RMS items from a given suppliers
> > > > set of 'Item' and 'Suppliers' records?
> > > > 2. Maintain 'cost' or other data changes via SQL Server Replicaition
or
> > > > Merge.
> > > >
> > > > 3. Using SQL Server (bcp, utilities, etc) to do a correctly matched
> > > > import of
> > > > records into RMS as part of the initial install.
> > > >
> > > > 4. What tables record entries are needed to import bulk records into
the
> > > > 'item' table?
> > > >
> > > > (I know that the supplier table must have a record that matches the
> > > > vendornumber field, but are their other tables that also have a
> > > > pre-requisite
> > > > or dependent record requirements?)
> > > >
> > > > I admit, I am a RMS novice.
> > > >
> > > > Sincerely
> > > > Kent Smith
> > > > bcentral@kc.rr.com
> > > >
> > > >
> > >
> > >
> > >
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap