SQL Server Performance Forum – Threads Archive

Strange connecting behaviour, why?

I have this DTSpackage, several Data Transformation tasks from a SybaseDB to a SQL2K. On the one site a have an OLE DB connection for Sybase from Sybase ASE OLE DB provider, on the other site our trusted Microsoft OLE DB Provider for SQL Server, pointing to DB1. Now I have changed something in my Data Transformationtasks, created a duplicate targetDB, DB2, and run the package. Succesfully ended, it says. I look in DB2 and see no records… I try again, and again: no records appear in DB2. I check connection parameters: yes, the targetdatabase is definitely DB2. I run it again, succesfully, and no records in DB2…
I wake up and check DB1. A lot of records… Suspicious now I run the package again, no records in DB2, and lo! New records added in DB1!
I check again: yes, the target IS DB2!
I detach DB2 and run the package again, message: Error in Ole DB for SQL Server, unspecified error… What is going on here? I point to DB2, but DB1 is still the target, although the properties of the Connectionsource tells me otherwise.
I am running SQL2K sp3 on W2003 server.
Greetz,
FreeHansje

Sorry, just an interim "answer". [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Have you check<a target="_blank" href=http://www.sqldts.com>http://www.sqldts.com</a> yet? – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Tnx for answering, Frank, and yes, it’s the first place I look when in DTS-trouble…<img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> But no, they had no article which names this event. Greetz, FreeHansje

Have you checked the driver used for SYBASE OLE DB, may check their site as well for updated driver. Probably try to redesign another new package to pull the rows. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Sorry for the delay.
It does not matter which connection I use. Same thing happens if I use Microsoft OLE DB Provider for SQL Server. I have submitted the problem to MS Support, but sofar they seem to have no clue as to what the problem is, let alone have a solution.
I have a work-around via an ActiveX-task, but I don’t want to have to need a work-around, I want it to work correctly in the first place. Greetz,
FreeHansje

Hi, Have you checked the connections in the disconnected properties option of the package? U will be able to see all package’s connections and their properties. Check datasources and provider to check that DB2 is pointed to a diferent database than DB1. Check that all your transformation are using the correct connection, and if db1 and db2 are in the same server check all the queries of the transformation. usually the SQL statement generates by tasks are name as database.owner.table so it could be that the query is inserted into db1.dbo.table instead of db2.dbo.table

]]>