DTS Truncating when transforming data from Access to SQL 1

[kavita11]

I have a DTS Package that I am using to tranform data from MS Access to a SQL Server table. The MS Access database holds a list of sequence numbers and corresponding notes. These notes run to about 3 -4 lines of text data. Here is the problem :

My first source of data was a Fox Pro table which held the seq nos and the text data I transformed this to MS Access first and this came out just fine no truncations occurred.

Now the second step is to transform the data from MS Access to a SQL Server table. Data truncation in the text column is occurring here.

I have tried the following:

Access --> Text file - Truncation occurred
Access --> Excel file --> - Truncation occurred

I have also tried to reset the attribute under Disconnected edit for "Maximum characters per delimited" and I have set this value to 9999 but it did not work. I have also checked out the article

http://support.microsoft.com/default.aspx?scid=kb;en-us;247527

suggested by a lot of people but it does not help. I am running SQL Server 2000 with the latest SP. I need to transform the data from Accesss or Text file or Excel file to SQL Server without any truncation issues.

Any suggestions would be great

Thanks

Kavita

 

[SQLSister]

What is the date type and length of the field you are trying to import into?

 

[kavita11]

From Access it is a memo feild and in SQL it is a text field. There is no option to set a field lenght in SQL for text data type.

Thanks for your help

Kavita

 

[wolfert]

Have you solved this already? I'm having the same problem now.

Patrick.

 

[kavita11]

No I was not able to find a solution to this. We ended up writing a VB interface which creates a SQL INSERT statements in a text file without truncating the data and then all you do is run the INSERT Statement script in Query Analyzer. Try this, it definitely worked for me. I would still like to know if there is a fix though with DTS. Do let me know if you find a fix.

Good luck

Kavita

 

[JefB]

Instead of using a "text" data type, try "varchar(4000)".

JefB

 

[MakeItSo]

Yepp, I was about to suggest the same JefB.
Little addition perhaps: If your Access field contains special characters, you might use nvarchar(4000) instead.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de