Null value problem with SQL-Server

[Sql7user]

I´m trying to grab tables from an AS/400 to an SQL-server7 via odbc (client access) connection. To do this I have set up DTS packages that copy the data (mirror) from one table and then drops the data in the sql table the next day and copy it again. On a few packages it works fine but here is the problem on other packages:

The DTS packages start to run but stops soon with the error that a certain column does not allow nulls and that the as 400 column contain a null value. Ive checked the column in the AS/400 for null values but there are none. Instead there are "blanks" in the column, but the packages somhow take this for null values. Because I want a exact copy of the column the solution to allow nulls in the sql column dont work.

Then a little question, when builing the DTS package you set up a connection between AS/400 and SQL-server, then when I click to choose the right table it takes forever to choose it. This is because the connection is checking all the tables in the AS/400. Is there anyone how now if it is possible to buffer all this tables? I´ve got a lot of packages to build so it could save me a lot of time.

Thanks!

 

[foxdev]

The only way that comes to mind to work around the "blanks becoming nulls" problem is to use a VBScript transformation on that particular column. --------------
School supplies for the needy:

http://www.agrainofhope.org/classzoom.shtml

 

[SQLSister]

Set up your table to allow nulls
You could add an Execute SQl Task to your DTS program after the transformation to do the following:
Change the Nulls to empty string

IF you really want to have the no nulls in the SQL table you can drop the null requirement before running the conversion and put it back in afterward running the Execute SQL Task above. This can also be done with Execute SQL Tasks. But be aware that this constraint isn't really doing anything for you if you always get all your data for this table through the DTS package.

 

[Sql7user]

Thank you for your fine solutions. Ive got another solution from another forum too:

In the data transformation properties under advanced there is check box for "Enable identity insert". If I check that box the transformation is working without complains, but can there be other problems with this?

Regards Sql7user