July 10, 2001 at 12:47 pm
I want to convert DBase5 table to SQL Server 7.0 table with DTS package. The DBase5 table have DATETIME field, that may have Null value. To convert the field I use ActiveX script. When I "Test" conversion it give me error on the first DBase5 record were field value is Null. I try "On Error Resume Next", analys on "IsNull", but result the same: Function Main() do not executed with DTSSource("FIELDNAME") is Null. Would anybody of You help with advice - how to forth DTS ActiveX script process this Null field?
July 10, 2001 at 6:42 pm
sk...Is there a reason why you need to use an active x script to do the transform. Have you tried just doing a basic DTS import from a DBASE datasource to a SQL table?
July 10, 2001 at 7:18 pm
Yes, jwiner, I tried it.
In DTS I created two connections - DBase5 and SQL Server, Connect them with "Add Transform" arrow, and in Properties of that transformation (tab "Transformations") simply connect two fields with arror and choose type of transformation "Copy Column". Then I click on that arrow with right-mouse button and choose "Test". And result the same. DTS copy from DBase5 table only first records, where field not Null.
I do not know other ways to make this transformation in DTS.
Thahk You for Your attention!
July 10, 2001 at 7:22 pm
Hope I am understanding you problem correctly...but have you tried adding a default value to the SQL table where the DBASE value would be null, or can you uncheck the SQL Table option in desing mode to allow nulls for that field?
July 10, 2001 at 7:49 pm
Yes, i did it. Both of them - define default value for SQL server table and uncheck "Allow Null" checkbox. But result the same. DTS stop with error when try to "Copy column" with Null value from DBase5 to SQL.
I also tried to use query in source -"select FIELDNAME from TABLENAME where FIELDNAME IS NOT NULL", assuming, that DTS will extract from DBase5 table only those fields, that not Null. But - again in convert only first records, where field not Null. Maybe I need to change certain DTS settings, how it treat DBase5 fields? Or general settings about Null fields in source?
But, again, thank You for Your help.
July 10, 2001 at 8:50 pm
I think I faced a similar issue, though I'm not sure why it is a problem.
Make sure your field in SQL Server allows Nulls. Then, in your ActiveX script you can do the following:
If Not(DTSSource("<source field>") & "" = "") Then
DTSDestination("<dest. field<") = DTSSource("<source field>")
End If
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 10, 2001 at 9:17 pm
Thank You Brian!
I allow Null for destination field in SQL table and use code You gave, but DTS still convert only first records from DBase, where source field not Null. The only way I found to deal with it is to set large number of possible errors in advanced tab of transformation property. I also assign the exception file. DTS report in it following error:
Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Data for source column 10 ('BIRTHDATE') is not available.
Error Help File:sqldts.hlp
Error Help Context ID:30701.
I check in advanced tab "Keep Null". But that does not help.
I think i need to dig deeper.
Thank You for Your help.
Sergey.
July 11, 2001 at 11:44 am
Hi jwiner and Brian!
I finally found out what going on. In DBase5 table the field value was not Null! In Visual DBase it represented as " / 0/ ". So it is not Null, but also not valid value, because DTS report error. After I clean manually in Visual DBase "0" from the value it became Null and DTS work perfect. Yesterday I wrote a little VB program that read DBase file with DAO lobrary. And logic "If IsNull(rcTable.Fields("BIRTHDATE").Value) Then" return True on that fields. So I conclude that it is Null. But as I see now DAO recognize some non-valid values as Null. Now I must find out - what actual value of that field is, and how forth DTS accept it. Thank You again for Your help and advices. Hope I will solve this problem.
Sergey.
July 11, 2001 at 12:46 pm
Instead of a datetime or smalldatetime variable type for the field, change it to char(10) in the SQL Server table. That should let you see what's coming across.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 11, 2001 at 8:27 pm
Glad to hear you got it working.
July 12, 2001 at 11:42 am
Hi Brian!
I already did it many times - change field type to char and try to retrieve that strange values. But as I see control simply do not passing in Function Main() when DTS get that " / 0/ " values from DBase record. So fo the moment best what I can do is give up with DTS and write program that read DBase file with DAO (to retrieve ALL fields) and write values to SQL 7.0 tables with ADO. The reason is because customer program somehow initialize this "BIRTHDAY" fields with zeros, instead of initializing it with any Date. This is what I suspect. And DTS with its strict rules simply treat it as error and skip processing. Thank You Brian and JWINER for You generous help. I think this strange case add information about DTS. Thank You both again. Sergey.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply