Hello,
Not too sure where to start look so I'm going to ask if anyone can point me
in the right direction.
I have an XLS workbook that I want to DTS into a SQL Server table named
transactions.
A column in the XLS is ACCOUNT NUMBER.
In my SQL server I have an accounts table that has accountID as the PK. It
also has an accountNumber field.
Is there a way to do a lookup on the accounts table so that the DTS can put
in the accountID of the matching accountNumber into the transactions table?
Thanks,
Won LeeThis is a multi-part message in MIME format.
--=_NextPart_000_0011_01C36AFB.7FD5A7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Yes, you can:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsqlp=
ro2k/html/sql00l5.asp
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Won Lee" <noemail> wrote in message =news:e4VTdoxaDHA.1872@.TK2MSFTNGP12.phx.gbl...
Hello,
Not too sure where to start look so I'm going to ask if anyone can point =me
in the right direction.
I have an XLS workbook that I want to DTS into a SQL Server table named
transactions.
A column in the XLS is ACCOUNT NUMBER.
In my SQL server I have an accounts table that has accountID as the PK. =It
also has an accountNumber field.
Is there a way to do a lookup on the accounts table so that the DTS can =put
in the accountID of the matching accountNumber into the transactions =table?
Thanks,
Won Lee
--=_NextPart_000_0011_01C36AFB.7FD5A7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yes, you can:
http://msdn.microsoft.com/library/default.=asp?url=3D/library/en-us/dnsqlpro2k/html/sql00l5.asp
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Won Lee" wrote in message news:e4VTdoxaDHA.1872=@.TK2MSFTNGP12.phx.gbl...Hello,Not too sure where to start look so I'm going to ask if anyone can point =mein the right direction.I have an XLS workbook that I want to DTS =into a SQL Server table namedtransactions.A column in the XLS is ACCOUNT NUMBER.In my SQL server I have an accounts table that has accountID =as the PK. Italso has an accountNumber field.Is there a way to do =a lookup on the accounts table so that the DTS can putin the accountID =of the matching accountNumber into the transactions =table?Thanks,Won Lee
--=_NextPart_000_0011_01C36AFB.7FD5A7F0--|||Thank you. I will read the article right now.
Won Lee
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ePeHE0xaDHA.2476@.tk2msftngp13.phx.gbl...
Yes, you can:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00l5.asp
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Won Lee" <noemail> wrote in message
news:e4VTdoxaDHA.1872@.TK2MSFTNGP12.phx.gbl...
Hello,
Not too sure where to start look so I'm going to ask if anyone can point me
in the right direction.
I have an XLS workbook that I want to DTS into a SQL Server table named
transactions.
A column in the XLS is ACCOUNT NUMBER.
In my SQL server I have an accounts table that has accountID as the PK. It
also has an accountNumber field.
Is there a way to do a lookup on the accounts table so that the DTS can put
in the accountID of the matching accountNumber into the transactions table?
Thanks,
Won Lee|||Whilst Tom's article points to a good solution one to be aware of is
Pump into a scratch table and then use TSQL to do the lookups.
This should perform better the more records you have in Excel as with a
lookup solution in a DTS DataPump task DTS has to issue another statement
for every single row and the larger the source the longer it takes.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Another good suggestion.
I was unable to get the solution Tom Suggested to work.
I always thought DTS with a direct column copy was a data pump but it
doesn't look like it now.
I also tried to do an ActiveX copy with a DDQ but also had some problems.
This may be the solution I'm looking for.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23OtljXzaDHA.3248@.tk2msftngp13.phx.gbl...
> Whilst Tom's article points to a good solution one to be aware of is
> Pump into a scratch table and then use TSQL to do the lookups.
> This should perform better the more records you have in Excel as with a
> lookup solution in a DTS DataPump task DTS has to issue another statement
> for every single row and the larger the source the longer it takes.
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||What errors do you get?
DTS can be a "Copy Column" but it can be so much more too.
DDQ can be confusing but is also quite powerful.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Won Lee" <noemail> wrote in message
news:uCdzAh8aDHA.1384@.TK2MSFTNGP10.phx.gbl...
> Another good suggestion.
> I was unable to get the solution Tom Suggested to work.
> I always thought DTS with a direct column copy was a data pump but it
> doesn't look like it now.
> I also tried to do an ActiveX copy with a DDQ but also had some problems.
> This may be the solution I'm looking for.
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:%23OtljXzaDHA.3248@.tk2msftngp13.phx.gbl...
> > Whilst Tom's article points to a good solution one to be aware of is
> >
> > Pump into a scratch table and then use TSQL to do the lookups.
> >
> > This should perform better the more records you have in Excel as with a
> > lookup solution in a DTS DataPump task DTS has to issue another
statement
> > for every single row and the larger the source the longer it takes.
> >
> >
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
>|||I used the import wizzard to create the DTS package.
Afterwards I looked at the DTS package to edit the the LookUP portion, as
directed by Tom's article.
I created a new connection to the DB and added the following code to the
lookup query.
select accountID from accounts where MCName = ?
I didn't know where the put the rest of the code.
I then designed a new DTS package. I used the ActiveX option to bring over
each column individually and was going to change the VB
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:u%23YlMs8aDHA.1748@.TK2MSFTNGP12.phx.gbl...
> What errors do you get?
> DTS can be a "Copy Column" but it can be so much more too.
> DDQ can be confusing but is also quite powerful.
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Won Lee" <noemail> wrote in message
> news:uCdzAh8aDHA.1384@.TK2MSFTNGP10.phx.gbl...
> > Another good suggestion.
> > I was unable to get the solution Tom Suggested to work.
> > I always thought DTS with a direct column copy was a data pump but it
> > doesn't look like it now.
> > I also tried to do an ActiveX copy with a DDQ but also had some
problems.
> > This may be the solution I'm looking for.
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:%23OtljXzaDHA.3248@.tk2msftngp13.phx.gbl...
> > > Whilst Tom's article points to a good solution one to be aware of is
> > >
> > > Pump into a scratch table and then use TSQL to do the lookups.
> > >
> > > This should perform better the more records you have in Excel as with
a
> > > lookup solution in a DTS DataPump task DTS has to issue another
> statement
> > > for every single row and the larger the source the longer it takes.
> > >
> > >
> > >
> > > --
> > >
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> >
> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment