SSIS on 2005 sql server behaves differently than DTS on 2000 sql s

Paul Shapiro

2009-05-26 00:31:17 UTC

It's been a few years since I made the transformation from DTS to SSIS, but
what I remember is that SSIS is quite different from DTS, even though they
target the same task. I had to get a book on SSIS and found it easier to
re-create my packages from scratch. Or keep them in DTS and install the
components that let you edit and run DTS packages in SQL 2005. One of the
biggest changes was that DTS would automatically map datatypes to the best
of its ability, so you could have a source nvarchar going to a destination
varchar or vice versa. SSIS requires explicit conversions, and throws an
error if you map non-identical data types.

Post by Dovla
I have very interesting problem with SSIS import. I need to migrate old DTS
import (from txt file) to SSIS import packet. Problem is that I can’t
configure SSIS on SQL2005 that can work like old DTS on SQL2000.
1.The text source file opened with editors like Notepad, Wordpad, Word,
TotalCMD doesn’t show any delimiter.
2.On old system SQL2000 and DTS is working OK.
a.Opening Text source file (connection properties), on Properties
preview I don’t see any delimiter.
b.Next step is to open Transform Data Task Properties. When I click
Preview on Source panel structure of table is show correct. On Option panel
Row delimiter is {CR}{LF} and Column delimiter is Vertical Bar. File type is
select to 7.0 format.
Import with this DTS work OK.
3.On new system SQL2005 and SSIS I can’t find solution that this import
procedure could work.
a.Both connection to text source file (DTS and SSIS) is to the same
path. The source text file is the same form both.
b.When I try to put Row and Column delimiter ({CR}{LF} and Vertical Bar
{|} the preview of the column is not correct and SSIS will not work.
What could be the problem?