Access data into SSIS ???

[mscertified]

This is probably a long shot but has anyone successfully processed MS Access
memo column data with SSIS (SQL Server Integration Services)? I'm trying to
build a package to export Access data to an FTP server. The data flow is
defaulting to datatype DT_NTEXT which generates an error and says I must
transform it to type DT_TEXT. When I do this transform, I get another error.
Now I try to transform it to type DT_WSTR and this fails too. I need this
data to be a fixed length of 2000 characters and only DT_WSTR lets me specify
a length. If I do LEFT(xxx,2000) in the Access query, SSIS comes up with a
length of 255.

 

[Pieter Wijnen]

not familiar with SSIS, but I'd try with a custom function to return the
2000 character string

HtH

Pieter

 

[mscertified]

SSIS (SQL Server Integration Services) cannot read any Access query containg
any function (even like FORMAT or LEFT). It omits them from its drop-down. So
I have to import the entire memo column and deal with it inside SSIS.

 

[Pieter Wijnen]

I thought as much.
But you where the one talking about Left <g>

Pieter

 

[Rick Brandt]

SSIS (SQL Server Integration Services) cannot read any Access query
containg any function (even like FORMAT or LEFT). It omits them from
its drop-down. So I have to import the entire memo column and deal
with it inside SSIS.

Have you tried using the wizard? We had a myriad of problems transitioning
from DTS to SSIS and most of our packages bring in data from our IBM ISeries
box. Always the problems were in setting up the transforms. Then one of
our guys discovered that a package created using the data transfer wizard
worked perfectly.

That is all we use now. Even if a package just needs modifying we find it
is less trouble to just delete it and recreate with the wizard. Otherwise
the transforms start protesting about data types again. I think it's a bug
myself. One should be able to create manually anything that the wizard can
create and yet the transforms that the wizard builds just work.

 

[mscertified]

Thanks for the idea.
I gave it a try...
The wizard generated an SSIS package but running it gave the error I talked
about before 'Datatype DT_NTEXT not supported with ANSI files'. Needless to
say the wizard chose this datatype itself and gives me no way to change it!
This is the Access memo column.