Solved

DTS Export to Flat File

Posted on 2006-06-28
10
392 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Accepted Solution

by:
Jay Toops earned 100 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 25 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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