SQL Server Performance

Strange connecting behaviour, why?

Discussion in 'SQL Server DTS-Related Questions' started by FreeHansje, Dec 9, 2005.

  1. FreeHansje New Member

    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
  2. FrankKalis Moderator

    Sorry, just an interim "answer". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Have you check<a target="_blank" href=http://www.sqldts.com>http://www.sqldts.com</a> yet?<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  3. FreeHansje New Member

    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=';-)' /><br />But no, they had no article which names this event.<br /><br />Greetz,<br />FreeHansje
  4. satya Moderator

    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.
  5. FreeHansje New Member

    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
  6. ji06 New Member

    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

Share This Page