DirectCopyXform error when using DTS

I am trying to import data and I receive an error that it failed to copy one table. The error says conversion error: Destination does not allow NULL on column pair 2(source column 'DateUsed' (DBYTYPE_DATE), destination column 'DateUsed'(DBTYPE_DBTIMESTAMP)). I have tried to change the data type for DateUsed to several different types in Access. I am not sure why it is not accepting it. If anyone has any ideas, I would appreciate it. Thanks.Well, for starters, what's the actual SOurce? SQL Server?

What's the Destination? MS Access?

Why not DTS The Source to the Destination and let DTS create an object for you, and see what it defines.

Then comapre the 2.|||The source is actually Access and the destination is SQL server. I used DTS to import data from an Access db and it failed to copy one table because of this date error.|||OK.

What I suggests still stands.

Use DTS and let it create a sql server table and find out what it creates.sql

dimension table population-out of fact table

I have a large flat file that comes to me. I first import the flat data in to a SQL table for ease of use. Then i put it into a more permanent table with the proper references to dimension tables. I want to build a dimension table out of information from my flat file. I have a dimension table with columns, [Org Client], and [Client#] where [org client] is the name of the client. Both of these columns appear in my flat file but i want to use only the client# in my permanent table. How extract distinct values of client # and [org client] into a dimension table?

My idea was to select distinct values of client# and use some type of foreach loop to go through each client# and use a query to select the TOP(1) values of [org client] where client# = x. Would this work and if so how do I go about setting this up?

I'm really hoping there is a simpler way than this. Thank you all for your time.

A sort transformation in a data flow can get rid of duplicates for you. Then from there you can go into whatever table you want.|||That is very impressive. Thank you very much. Works better than i could've imagined.sql

Different behaviours on different servers

I have a database and an application in .net using it.
In that database I have a stored procedure where is an import mechaism using
ODBC driver [Microsoft Text Driver (*.txt; *.csv)] and a .csv file.

On my machine everything works fine, but on the other machine import fails
with an exception

Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] Too few
parameters. Expected 6.]

My query looks like that:

DECLARE @.TSQL varchar(8000)

SET @.TSQL = 'INSERT INTO TABLE ( col1, col2, col3 )
SELECT * FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver
(*.txt; *.csv)};DefaultDir='+@.import_dir_path+';'', ''select COL1, COL2,
COL3 from '+@.filename+''')'

EXEC (@.TSQL)
SELECT @.Err = @.@.ERROR, @.Rct = @.@.ROWCOUNT

Second error I get:
My location is Polish but my national chars do not pass through other ODBC
driver - for dbf s.
Database was simply detached and atached, so collation hasn't changed.

First machine (the good one) works under WinNTWkst4.0 Polish version with
SP6 and ODBC Drivers version 4.00.5303

Second machine (the bad one) works under Win2kServer Eng with MDAC 2.7.
ODBC Drivers version 4.00.4200.

If anyone can help me - please do that! I'm frustrated to the limit
already...

Regards,
Sliver"S" <sliver_1@.poczta.onet.pl> wrote in message
news:capbr5$jj4$1@.nemesis.news.tpi.pl...
> I have a database and an application in .net using it.
> In that database I have a stored procedure where is an import mechaism
using
> ODBC driver [Microsoft Text Driver (*.txt; *.csv)] and a .csv file.
> On my machine everything works fine, but on the other machine import fails
> with an exception
> Could not execute query against OLE DB provider 'MSDASQL'.
> [OLE/DB provider returned message: [Microsoft][ODBC Text Driver] Too few
> parameters. Expected 6.]
> My query looks like that:
> DECLARE @.TSQL varchar(8000)
> SET @.TSQL = 'INSERT INTO TABLE ( col1, col2, col3 )
> SELECT * FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver
> (*.txt; *.csv)};DefaultDir='+@.import_dir_path+';'', ''select COL1, COL2,
> COL3 from '+@.filename+''')'
> EXEC (@.TSQL)
> SELECT @.Err = @.@.ERROR, @.Rct = @.@.ROWCOUNT
>
> Second error I get:
> My location is Polish but my national chars do not pass through other ODBC
> driver - for dbf s.
> Database was simply detached and atached, so collation hasn't changed.
> First machine (the good one) works under WinNTWkst4.0 Polish version with
> SP6 and ODBC Drivers version 4.00.5303
> Second machine (the bad one) works under Win2kServer Eng with MDAC 2.7.
> ODBC Drivers version 4.00.4200.
>
> If anyone can help me - please do that! I'm frustrated to the limit
> already...
> Regards,
> Sliver

I don't really have a good answer, but one possible issue is that the ODBC
drivers are different versions, and the one which doesn't work seems to be
older, so you might want to install the latest MDAC drivers (2.8, I think),
to see if that helps.

As an alternative, you could try using the Jet driver instead:

http://www.users.drew.edu/skass/sql/TextDriver.htm

Simon|||> I don't really have a good answer, but one possible issue is that the ODBC
> drivers are different versions, and the one which doesn't work seems to be
> older, so you might want to install the latest MDAC drivers (2.8, I
think),
> to see if that helps.

Thanks for answer - but it doesn't seams to be because of different vers. of
ODBC drvs - I've been so desperated that I installed on the fresh machine
win2kSrv with a 2kEnt database, set a patch on it and then mdac 2.8 - but
between all changes I'd been verifing if my query works. Laugh if you want
to - it was working even on fresh db - without any upgrade - and hadn't stop
after upgrades had been set up.
ODBC drivers versions are now the same as on the bad one machine ...

So - I have no idea what can be wrong. Is there anything like national
versions of ODBC drivers?

> As an alternative, you could try using the Jet driver instead:
> http://www.users.drew.edu/skass/sql/TextDriver.htm

It is good idea - but remember of the second problem - chars of my national
my charset aren't served properly... and the files are dbf... I have them
done through a linked server - but still the ODBC driver seems to be a
failure...

S.|||Just a hunch, but I would keep looking down the route of MDAC. We had
a problem with insufficient parameters a while back. From what I
remember later versions of MDAC included an additional parameter to
the connection string when opening a database through an ODBC
connection. It would store this depending on the PC which created the
connection. We found this with a DTS package.

We could create a connection within a DTS package and then use in on
another server (ran as scheduled) which had a lower version which
would then not run. You may be able to test by re-creating the
connection on the version with the lower version so it doesn't include
this additional parameter and it may work. If it does, then I would
suggest a version issue which needs addressing.

As I say, it's a hunch as it's a little while since I looked at it,
but it may be of help to you.

We upgraded each PC and server to the same version of MDAC (2.8) to
get rid or the problem.

HTH

Ryan