Link to home
Start Free TrialLog in
Avatar of ColumA
ColumA

asked on

ActiveX-Script in dts

I would like to use DTS for a transfer of excel-data into a table. I now have the problem with the date. As I have a datefield in  excel, where part of the recordsets do have 00.01.1990 as an entry, the table does not take them. I think that this might be solved by the ActiveX-Transformationmanager. Can anybody tell me how the script should look like. I try

'**********************************************************************
'  Visual Basic-Transformationsskript
'************************************************************************

'  Alle Quellspalten in die Zielspalten kopieren
Function Main()

If DTSSource("Periode") <= '01.01.1990' then DTSDestination("Periode") = ''
Else
DTSDestination("Periode") = DTSSource("Periode")
end if
      
      Main = DTSTransformStat_OK
End Function

But this does not work. Thx
Avatar of patrikt
patrikt
Flag of Czechia image

This will not work. You are using single quotes. Use double and be avare that you compare strings so it is aplhabetical compare.

Patrik
Avatar of ColumA
ColumA

ASKER

I tried
If DTSSource("Periode") <=  "01.01.1990" then DTSDestination("Periode") = ""
Else DTSDestination("Periode") = DTSSource("Periode")
end if

as well but still get a compile-error. How could I avoid strings/alphabetical?
SOLUTION
Avatar of patrikt
patrikt
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alphabetical compare means that you read string from excel and compare it to sting "01.01.1900". It is not date compare.

For date compare use Date, DateDiff atc. But your 00.01.1900 is not convertable to date.

To indicate this error I'll use Left(DTSSource("Periode"),2)="00".
But your should work also.
Avatar of ColumA

ASKER

If I analyse the code, it says correct. But in the test, it still does not work. I used

If Left(DTSSource("Periode"),2)="00"<=  "01.01.1990" then
DTSDestination("Periode") = ""
Else
DTSDestination("Periode") = DTSSource("Periode")
end if

to find the possible converting-error, but I still get the error in the test
Avatar of ColumA

ASKER

If I go into a preview of the source-data, I get 06/30/2004 for the correct ones and 12:00:00 AM for the ones who appear 00.01.1990 in the excel !!
This isn't a valid statement:

If Left(DTSSource("Periode"),2)="00"<=  "01.01.1990" then


What are you trying to do??
Avatar of ColumA

ASKER

Hi Arbert, I am still try to get used to the DTS (https://www.experts-exchange.com/questions/21120301/How-to-avoid-Timeout-on-Update-Event.html)
I now have created a package where I intend to transfer the WFD-Exceldata to my SQL. But executing has failed. I then copy/pasted the data out of excel into the table by hand to find out where the problem lies. It seems that I do have a conflict in the date. For the better understanding: I do download external data into excel, so I cannot format. Back to the date: In excel, some fields showing sometimes the date 00.01.1990, which I then saw in the preview of the source data as 12:00:00 AM. The format won't be accepted in the table, so i guess this is why it doesn't execute the transfer. This why I try to get this solved by  ActivX-Transformationmanager. This is the basis of this question.

I now thinking about several think you might be able to answer. First of all, as I would like to execute the transfer by my own intention and in the front-end-application, because the download is sometimes finished at 8AM or 9AM, scheduling seems not to be the best solution, I have seen in my book that the whole DTS can be done in a query? But the code is huge and I have no idea and I might misinterpret. Or is there another way of executing the transfer in front-end? Second, I now tried in the Package-Manager to create a query as a source instead of the whole excel and convert there the date. But right now, as I am new to SQL, I think If(,,) can not be used in this kind of query.

I am happy to increase the points and share them if I can solve my problem.
Thx
ColumA
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Albert is as usual correct. :)

Only one comment. You previously put empty string as replacement for incorrect date; it is converted to zero date rather then null. Zerro date is 1.1.1900 12:00AM which is one you have got.

Patrik
"Only one comment. You previously put empty string as replacement for incorrect date; it is converted to zero date rather then null. Zerro date is 1.1.1900 12:00AM which is one you have got."

That's why I would rather make it equal to null...
Avatar of ColumA

ASKER

Hi arbert, I have allowed nulls but it still doesn't work. If I execute the package step by step, I get a runtime-error saying: invalid use of Null: 'replace'

??

One question: If I execute this DTS daily, does it add to the table or delete the former entries and then put the new ones in to it? I need last.
Avatar of ColumA

ASKER

Found out how to delete before transforming. But still run the error on 'replace'. Could it be the better way to go for a query and convert or if/when in the source-menu of DTS? How should this look like because I think if/when is not usable in SQL-queries.
Avatar of ColumA

ASKER

!!! got it worked. The system automaticaly installed smalldatetime in all datefields in the table. Changed them to datetime, it works.

Arbert, do you see a way to give a instruction to execute the transform in the front-end or do I have to schedule by a fix-time?
"Arbert, do you see a way to give a instruction to execute the transform in the front-end "

What do you mean "front-end"?  It's coming from excel, so you either make the people enter it right, or you clean it up in DTS, you don't have too many options unless you add VBA to the excel to enforce the rules on data entry.
Avatar of ColumA

ASKER

Right now, I run only on Access, but it's getting to big. I now go for SQL-Server on backend and Access as frontend. Till now, I linked the excel-sheet into access, so after updating the excel, I had already the newest data in access. With SQL, I update the excel and then use DTS to transfer the data in SQL and then are able to use them in Access (frontend). Till now, I had no time-gap between the excel/access. But going now for SQL, I had to wait for DTS to transfer, which as I understand, can be done only by scheduling for a fix time, or every hour or so. My question is: When I updated excel, can I then instruct DTS, without going on the server and execute manualy, to run immediate by a click on a button in front-end? If yes, what would the SLQ-code look like?
"When I updated excel, can I then instruct DTS, without going on the server and execute manualy, to run immediate by a click on a button in front-end? If yes, what would the SLQ-code look like?"

Yes, you can--this is kind of in addition to your original question, but you can have your button press execute a stored proc on the server:

master..xp_cmdshell 'dtsrun /......'

If you search for dtsrun and xp_cmdshell, you can find all kinds of sources on it...
Avatar of ColumA

ASKER

ok. let me try. If I don't come forward, I'll post a additional question. Thx. to everybody.