Re: Newbie: Update From Excel



Ok So here is what you do

Add an Excel Connection.
Point it to your spread***

Add a SQL Server connection
Point it to your SQL Server

Now create a transform data task coming from the Excel Spread*** to the
SQL Server

Double click on the Black line (The transform data task).

In the Source drop down the Table/View combobox and choose the Excel table
you require

Now go to the destinations tab and select your destination table

On the transformations tab map the source columns to thier respective
Destination.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Cavan Vannice" <CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:06D15DA7-0998-482A-AD5D-4D2B3AADA566@xxxxxxxxxxxxxxxx
> IT is a DTS Package Excel spread*** is a connection, then to the
> database,
> then the execute SQL task. I am really not partial to any particular way
> of
> doing it, just something that can be changed and implemented easy. This
> will
> be a regular if not daily process.
>
> Thanks
>
> P.S. I wouldnt mind seeing some similar examples, if anyone knows any
> posts
>
>
> "Allan Mitchell" wrote:
>
>> In what task do you have this statement?
>>
>> Product$ is in Excel still? If yes then how? Looks like a SQL Server
>> table after an import from Excel to me.
>>
>> Allan
>>
>>
>> "Cavan Vannice" <CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> message news:CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx:
>>
>> > I realised that after i posted, thanks
>> >
>> > Here's where I am:
>> >
>> > Latest query in DTS
>> >
>> > INSERT INTO Product
>> > (ListPrice)
>> > SELECT [Product$].ListPrice
>> > FROM Product INNER JOIN
>> > [Product$] ON Product.ProductID =
>> > [Product$].ProductID
>> >
>> >
>> > Syntax checks out and the it runs fine, but it does not do anything to
>> > the
>> > database. To explain my self better heres the basics of what I am
>> > doing:
>> >
>> > Product$(excel) Product
>> >
>> > ProductID ------> ProductID
>> > ListPrice ------> ListPrice
>> > more
>> > more
>> > more
>> > more
>> > more
>> > more
>> >
>> > If the productID's match, then the price from the spread***
>> > overwrites the
>> > price in the database, and in the end the Manufactururs code will have
>> > to
>> > match, right now I am simply working out the proccess.
>> >
>> > For better understanding I am testing on AdventureWorks2000, I exported
>> > the
>> > products table, modified the the top 5 lines, and am now trying to
>> > update the
>> > prices by ProductID.
>> >
>> > Thanks in advance for any help provided and already so.
>> >
>> > Cavan
>> >
>> >
>> >
>> > "Allan Mitchell" wrote:
>> >
>> > > BOL is one of the single most useful resources for your SQL Server
>> > > installation. Books Online aka the help files.
>> > >
>> > >
>> > >
>> > > "Cavan Vannice" <CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> > > message news:CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx:
>> > >
>> > > > The data is still in excel, what is BOL?
>> > > >
>> > > > "Allan Mitchell" wrote:
>> > > >
>> > > > > The statement that you there will not work.
>> > > > >
>> > > > > Is the data still in Excel or have you imported it?
>> > > > >
>> > > > > You would need to use an ExecuteSQL task to do it the way you
>> > > > > detail and
>> > > > > you will need to use either a Linked Server or OPENDATASOURCE to
>> > > > > query
>> > > > > the Excel spread***. Examples of both are in BOL.
>> > > > >
>> > > > > You can alternatively use a DataPump to transfer the data easily
>> > > > > enough
>> > > > > if the format is OK.
>> > > > >
>> > > > > Allan
>> > > > >
>> > > > > "Cavan Vannice" <Cavan Vannice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
>> > > > > in
>> > > > > message news:Cavan Vannice@xxxxxxxxxxxxxxxxxxxxxxxxx:
>> > > > >
>> > > > > > I am a newbie at SQL Server, but I do have a little experience
>> > > > > > from
>> > > > > > Coldfusion. Here's my Question.
>> > > > > >
>> > > > > > We have a database for our business. We sell products. and we
>> > > > > > get price
>> > > > > > lists in excel format all the time. I am working with DTS and I
>> > > > > > would like to
>> > > > > > set it up, so that I can format the spread***, properly and
>> > > > > > execute the
>> > > > > > package. easy as 1,2,3 right?
>> > > > > >
>> > > > > > Well I have tried many things from doing it in SQL DTS to
>> > > > > > Access, and
>> > > > > > everything is a no go. I have limited SQL knowledge but here is
>> > > > > > of the things
>> > > > > > I have tried and it seems to me it should work.
>> > > > > >
>> > > > > > INSERT
>> > > > > > INTO Product (ListPrice)
>> > > > > > SELECT ListPrice
>> > > > > > FROM
>> > > > > > [Product$]
>> > > > > > WHERE (ProductID = Product $ .ProductID)
>> > > > >
>> > > > >
>> > >
>> > >
>>
>>


.