Solved

ActiveX-Script in dts

Posted on 2004-09-08
18
487 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:ColumA
  • 9
  • 5
  • 4
18 Comments
 
LVL 12

Expert Comment

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

Patrik
0
 

Author Comment

by:ColumA
ID: 12006251
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?
0
 
LVL 12

Assisted Solution

by:patrikt
patrikt earned 25 total points
ID: 12006336
:))) You catched me.

Use correct line ends ant it is. Basic needs line end after Then and Else.

If DTSSource("Periode") <=  "01.01.1990" then
DTSDestination("Periode") = ""
Else
DTSDestination("Periode") = DTSSource("Periode")
end if
 
0
 
LVL 12

Expert Comment

by:patrikt
ID: 12006369
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.
0
 

Author Comment

by:ColumA
ID: 12006552
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
0
 

Author Comment

by:ColumA
ID: 12006609
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 !!
0
 
LVL 34

Expert Comment

by:arbert
ID: 12007576
This isn't a valid statement:

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


What are you trying to do??
0
 

Author Comment

by:ColumA
ID: 12008997
Hi Arbert, I am still try to get used to the DTS (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21120301.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
0
 
LVL 34

Accepted Solution

by:
arbert earned 50 total points
ID: 12009152
DateIn=replace(DTSSource("Periode"),".","/")
if isdate(DateIn) then
   DTSDestination("Periode") = DateIn
Else
   DTSDestination("Periode") = null
end if


Make sure you have the table definition in SQL Server setup to allow nulls on your date column....
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 12

Expert Comment

by:patrikt
ID: 12011267
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
0
 
LVL 34

Expert Comment

by:arbert
ID: 12011942
"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...
0
 

Author Comment

by:ColumA
ID: 12014031
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.
0
 

Author Comment

by:ColumA
ID: 12014773
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.
0
 

Author Comment

by:ColumA
ID: 12015454
!!! 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?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12017567
"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.
0
 

Author Comment

by:ColumA
ID: 12023898
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?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12027939
"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...
0
 

Author Comment

by:ColumA
ID: 12033421
ok. let me try. If I don't come forward, I'll post a additional question. Thx. to everybody.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now