Import from DB2 transformations stopped working | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Import from DB2 transformations stopped working

SQL Server 2000 DTS package imports data from DB2 into SQL Server 2000 running on Windows NT4 box.<br /><br />Everything used to work fine. However, after an update (I’m not sure which one), the local manual execution of the packages stopped working. This was sorta ok, because we would just use Windows XP boxes to do local manual execution. However, the Windows XP boxes would not allow manipulation of the data pumps connected from DB2 to SQL Server. As a work around we would manipulate everything except adding or subtracting transformations using disconnected edit and use our Dev server to add or subtract transformations. (I don’t believe transformations can be added or subtracted using disconnected edit, or at least I haven’t figured out how to do it.) This was fine as within 6 months (now extended to a year) SQL Server would replace DB2 [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />].<br /><br />About 2 weeks ago, something happened and now we are unable to add or subtract transformations in the datapumps [<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />!]. This is only occuring in our Dev and UAT environments and not in production. We can still sneak into production, add or subtract datapump transformations and get back out. However, who wants to mess with production this way!<br /><br />We(programmers and dba’s at a very, very large company) have spent many hours researching and trying to find out how to fix the problem to no avail. This is a real puzzler. I know there are a bunch of sharpies in this site, so if somebody can come up with anything, at least we’ll have new directions to research. (and if somebody can find the answer, you’ll get a big tip of the hat and lots of respect and gratitude from everybody here.)<br /><br />Let me know if more information is needed. Also, I can send a sample package if anybody wants it.<br /><br />Thank you very much.<br /><br />Michael D. Shields, MCSD, MCDBA, MCAD, SCJP
I feel the problem may lie with the permissions of the package.
Have you assigned the username & password to that particular DTS package.
Ensure the account used to execute the package has required privileges and same for the SQLAgent account too. If you’r using a local system account for SQLAgent then make sure to change to a local account with required privileges. If the DTS package logging is enabled then better to see the log first by checking where it is failing. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.