?
Solved

DTS Export to Flat File

Posted on 2006-06-28
10
Medium Priority
?
400 Views
Last Modified: 2013-11-30
I'm new to making statement in Enterprise Manager. I'm trying to run an export that joins addressid fields in two separate tables. Since DTS doesn't make joins through the wizard, I'm hitting a roadblock on the export. Here's what I got from the wizard:

select [TVFAddress].[AddressID], [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[AddressID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
from [TVFAddress],[TVFUserGroup]
order by [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFUserGroup].[AddressID]

If I add

left join TVFAddress on TVFAddress.AddressID=TVFUserGroup.AddressID

after the "from" line, I get a syntax error with the following line at the bottom: "the provider does not support all the interfaces/methoids required by DTS."

help?
0
Comment
Question by:cbeaudry1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Accepted Solution

by:
Jay Toops earned 400 total points
ID: 17001528
remove tvfaddress from the "FROM" line
its just a sql syntax error
jay
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 17001557
This syntax fails?

SELECT [TVFAddress].[AddressID], [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[AddressID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
FROM [TVFAddress]
LEFT OUTER JOIN [TVFUserGroup]
ON TVFAddress.AddressID=TVFUserGroup.AddressID
ORDER BY [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFUserGroup].[AddressID]
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 17001587
You tried this and it failed?

what database provider are you using to specifiy your database connection?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:cbeaudry1
ID: 17001657
I guess we're getting closer. I ran the statement and now get a "duplicate addressID" error.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 17001728
it shouldn't care ...

can u give me more info?
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17001778
You've got two columns named AddressID.  Take one of them out since you're joining on that column.
0
 
LVL 25

Assisted Solution

by:dstanley9
dstanley9 earned 100 total points
ID: 17001788
So it will be:

SELECT [TVFAddress].[AddressID], [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
FROM [TVFAddress]
LEFT OUTER JOIN [TVFUserGroup]
ON TVFAddress.AddressID=TVFUserGroup.AddressID
ORDER BY [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFAddress].[AddressID]
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 17001804
duplicate addressid error should only occur when inserting into a table with a "Unique primary key" constraint
0
 

Author Comment

by:cbeaudry1
ID: 17001850
There were two addreddid in the select statement. I figured it out by running the following:

SELECT [TVFAddress].[AddressID] as add1, [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[AddressID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
FROM [TVFAddress]
LEFT OUTER JOIN [TVFUserGroup]
ON TVFAddress.AddressID=TVFUserGroup.AddressID
WHERE  [TVFUserGroup].[StoreType] <> ''
ORDER BY [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFUserGroup].[AddressID]
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17002653
Correct.  All calculated columns need to be aliased and all column names (or aliases) have to be unique or DTS will fail.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question