SQL server DTS jobs and Text columns
I recently had to build a couple of DTS jobs to copy data from a customer database into a datawarehouse. Everything seemed to work fine, untill I was asked to create a DTS job that called each of the DTS jobs I had created. From that moment on, one job failed, at least when I called it from the combined job. I discovered that the job failed on one table specifically. This table contained a column of type Text. When I removed the transfer of that column, the job performed correctly inside the main job.
The strange thing however, was that there was another table in that job that contained a Text column. That table never caused a problem inside my DTS job. I eventually solved the problem by changing the target database. The data from the Text column was moved to a column of type varchar(4096). That way, my DTS job always worked fine.
Of course, that introduces the problem of text being truncated. The customer I work for had no problem with that. As it turned out, only 0.03 percent of the text data was truncated. And the data that was missing always seemed to be irrelevant. But if anyone has a better solution, let me know!