sql server dts: Has anyone successfully input an MS Access memo column into a SSIS package. I
need to have this be a fixed length file and truncated to 2000 characters. I
am having trouble with the data type which defaults to DT_NTEXT. How do I get
this data into a fixed width column with the truncation. I've been messing

Hello,

Yes, I have but its kind of a PITA. The Data Conversion component isn't going
to help you. I used a script component in transformation mode to to this.

Let's say your access table has an ID column of type INT, and your memo field
named as MemoFld. Let's also say that you want to load that into a SQL Server
2005 table that looks like this:

create table dbo.accessImport(
ID int
, MemoFld nvarchar(2000) not null
, MemoFldLen as len(memofld)
)
go

I created a package that has a single Data Flow task in it. In that task,
I has an OLEDB source pointed at the Access Field; A SQL Server Destination
pointed at my destination and the Script component. On the Inputs and Output
pane, I added a colum to Output0 called MemoFld2. My script main looks like
this:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO ' MemoryStream
Imports System.Text ' StringBuilder
Imports System.Text.Encoding ' Unicode class
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
' Read 4000 bytes (2000 characters) from the stream
Dim buffer As New MemoryStream(Row.MemoFld.GetBlobData(0, 4000))
' Get a stringBuilder to work with
Dim bldr As New StringBuilder(4000)
' Convert the bytes from the buffer into an array of
' characters and put them into the stringBuilder
bldr.Append(Unicode.GetChars(buffer.ToArray()))
' Set the new Field value
Row.MemoFld2 = bldr.ToString()
End Sub
End Class

Seems to have worked for me.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/