October 1, 2005 at 11:07 am
Group,
I am trying to automate some tasks using DTS packages, namely to extract a number of tables from Oracle 8 into pipe-delimited ascii files (don't ask...). I experimented with the most elaborate of these which relies on a query for some transformations (it also adds some columns) by using the wizard and it works fine. I saved the package and ran it.
However I wanted to open the package file and edit the transformations but Enterprise Manager crashes when trying to define the destination columns of the text file and without them of course there can't be any transformations. I tried creating thie process from scratch in DTS Designer and always, at the point when I create the transform data task, there is a problem with the columns for the destination. I also tried to use just a straight table from my Oracle db with no transformation with the same result. I also tried copying the table to a SQL Server database first and that didn't help.
What is there in the Oracle query output or table definitions that does not compute with the DTS design tool?
Thanks for any insight,
Caroline Wise
October 4, 2005 at 8:00 am
This was removed by the editor as SPAM
October 6, 2005 at 5:26 pm
I'm using SQL server 2000 and having the same problem. I have a view that uses 3 tables. I am manually creating DTS scripts and I want to export the view contents to a delimited text file. There are 29 columns and it's about 455 characters long. I know that there is an issue with the column length being over 255 but I already fixed that with the disconnected edit. It still doesn't work. I can remove any one or two columns and the problem goes away.... If I try to use the whole thing however when I try to 'define columns' enterprise crashes EVERY TIME. I need help....
Sara
October 7, 2005 at 1:06 pm
I got around it sortof. I used the DTS export wizard to create it. (It works when I hit run, I just can't MODIFY it) So I just made sure that the only 'Transformation' it needed to do was copy columns. The actual DTS I need is rather large and I'm halfway done already so I kept my original and at the point I need this I just execute the DTS the wizard created from the main DTS. Everything is working so I'm going to leave it at that but I also put a note in the DTS that warns "DO NOT MODIFY!!!" so that anyone to come after me knows there are problems....
Sara
October 10, 2005 at 6:09 pm
That's what I described above - I can create and run the package in the wizard, but it kills Enterprise Manager when I try to modify the destination part of the transform task. I hope I'll be able to look into this a bit more to figure out what in the oracle data caused this issue, because just having a single task doesn't really solve my problem.
I'll be back ...maybe,
Caroline
October 10, 2005 at 9:10 pm
Hi,
Try upgrading it to sp4.The issue had been sloved in sp4 sql server.
From
Killer
October 27, 2005 at 5:30 am
Had same problem SQL to Text file - SP4 fixed it.
Thanks!
December 13, 2006 at 10:21 am
I upgraded to SP4 and it still is an issue for me. I created the DTS package with the wizard and it spits out the data containing all of the columns full lengths. However, when I look at the package, the length of the column is less than the original length, and I can't edit it. EM crashes when I try. I experimented with editing it via a "disconnected edit", but only the first column changed. Additionally, when I run the package, despite the output column length showing as less than column length in the table, it spits out the data with the correct column lengths (full column lengths). Any ideas? Thanks.
Chris
December 18, 2006 at 10:05 am
I did it fine. I created an Oracle connection and a destination text file connection then used transform data task to put the data into the text file.
It worked fine.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply