Data Link problem

Question

Data Link problem

FreeHansje

SSChampion

Points: 11751
More actions
December 13, 2005 at 3:43 am

#61045

Hi there,
Since I can't change the DBname in a SQL Serverconnection I use a Microsoft Datalink Connection to change the source & destination databases. It seems to be working fine, connectiontests tell me there is connection established. Preview shows me result, destinationtables are shown on the right server/database. However, when I want to execute a Data Transformation task I receive a message:
Error source: Microsoft Data transformation Services(DTS) package
Error description:The connection for a task was not specified or was not found.
I have googled the Internet, no result. What can be wrong here?
System specs: SQL2K Standard Edition with sp4 on W2003 Standard Edition. May I point to another, yet unanswered thread of mine, for which I am using Datalink as a possible solution:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=239654#bm241158
Greetz,
Hans Brouwer

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

I've never used a microsoft datalink, but why don't you use a regular ODBC connection instead. That way you can change a DSN to point to a different database.

------------------------------
The Users are always right - when I'm not wrong!

Crischell Olegario Valued Member Points: 74 More actions · Answer 2

actually, you can change DataSource(server) and catalog(DbName) of sql serverConnection.

you can do it in "disconnected edit" --> connections

click on connection name (ex. Microsoft OLE DB Provider for SQL Server) and edit the corresponding property name

ex

Data Source - (local)

Catalog - myDbName

by the way, its better to do this dynamically.

you can create a global variables and use the dynamic properties.

Hope i understand you correctly.

Glad to Help,

Crischell Olegario

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Tnx for responding. However, the point is, that I CANNOT change the DataSource, either directly or through Disconnect Edit. I have layed the issue down at the MS SupportDesk, hopefully they will find out why and come up with a solution.

Greetz,
Hans Brouwer

Crischell Olegario Valued Member Points: 74 More actions · Answer 4

ic.

by the way, you mentioned that the problem occurs in the data Transformation Task. (just wondering). Kindly check the destination and source table. does it always include this format "DbName1.TableSource"?

thanks.

Glad to Help,

Crischell Olegario

FreeHansje SSChampion Points: 11751 More actions · Answer 5

Hi Chrishell,

Yes it does, and ofcourse I can change that to the table it is supposed to be, which is shown as well. However, int this DTSpackage there are scores of Transformation Tasks. I do not wish to change all these task manually when transferring this package to production. The whole point of these Connections, and especially of MS DataLink is the easy way you can change the target or source at 1 point...

Thx for responding.

Greetz,
Hans Brouwer

Crischell Olegario Valued Member Points: 74 More actions · Answer 6

i also encountered the same problem before.

Here's what I did:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=205600#bm206347

I've never used microsoft datalink but I'll be glad to hear more about that and other ways to solve this problem.

Thanks.

Glad to Help,

Crischell Olegario

FreeHansje SSChampion Points: 11751 More actions · Answer 7

Tnx for the info, Crishell, this sounds interesting, preferable to a change Data TransformationTask with ActiveXscript. I will look into this, if MS does not come up with a solution.

It is however a workaround, not a solution for the problem. Sofar MS has only asked for more info, no solution yet...

Greetz,
Hans Brouwer