Solved

DTS (ActiveX) Error

Posted on 2008-10-14
4
224 Views
Last Modified: 2008-10-20
Am editing an existing (and working) transformation.
The script parsing fine (no idea if my logic is correct yet) but when I test it or run it I get the error that "Function 'in' was not found in the script".

At first I assumed it was a typo but I can't see it! If not a typo then what could it be?
I have removed the indentation from the code snippet so it reads better in the code snippet window.... makes the if, else, end a bit tricker to follow tho :(
'**********************************************************************
'Visual Basic Transformation Script
'************************************************************************
 
'Copy each source column to the destination column
Function Main()
if DTSSource("CHART_NAME")= "GGLCHART" OR DTSSource("CHART_NAME") = "GJCCHART" THEN
if len(DTSSource("ACCNBRI")) = 11 then
if mid(DTSSource("ACCNBRI"),6,6) <> "&&&&&&" then
 
DTSDestination("column1") = left(DTSSource("Chart_Name"),2)
DTSDestination("column1")= DTSDestination("column1")& mid(DTSSource("ACCNBRI"),6,6)& "1000"
 
DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
 
DTSDestination("column3") = DTSDestination("column1")
DTSDestination("column4") = DTSDestination("column2")
 
Main = DTSTransformStat_OK
else
if mid(DTSSource("ACCNBRI"),3,3) <> "&&&" then
 
DTSDestination("column1") = left(DTSSource("Chart_Name"),2)
DTSDestination("column1")= DTSDestination("column1")& "000000" & mid(DTSSource("ACCNBRI"),3,3) & "0"
 
DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
 
DTSDestination("column3") = DTSDestination("column1")
DTSDestination("column4") = DTSDestination("column2")
 
Main = DTSTransformStat_OK
 
end if
end if
else
if mid(DTSSource("ACCNBRI"),1,6) <> "&&&&&&" then
 
DTSDestination("column1") = left(DTSSource("Chart_Name"),2)
DTSDestination("column1")= DTSDestination("column1")& mid(DTSSource("ACCNBRI"),1,6) & "1000"
 
DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
 
DTSDestination("column3") = DTSDestination("column1")
DTSDestination("column4") = DTSDestination("column2")
 
Main = DTSTransformStat_OK
 
else
Main = DTSTransformStat_SkipRow
end if
end if
 
 if DTSSource("CHART_NAME") = "GGLCHT" then
if (left(DTSSource("ACCNBRI"),3) = "&&&" AND right(DTSSource("ACCNBRI"),6) = "&&&&&&" AND mid(DTSSource("ACCNBRI"),4,3) <> "&&&") then
DTSDestination("column1") =mid(DTSSource("ACCNBRI"),4,3) 
DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
DTSDestination("column3") = DTSDestination("column1")
DTSDestination("column4") = DTSDestination("column2")
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
end if
 
 
 if DTSSource("CHART_NAME") = "GNJCCHT" then
if mid(DTSSource("ACCNBRI"),3,6) <> "&&&&&&" then
DTSDestination("column1") = left(DTSSource("ACCNBRI"),8)
DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
DTSDestination("column3") = DTSDestination("column1")
DTSDestination("column4") = DTSDestination("column2")
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
end if
end if
End Function

Open in new window

0
Comment
Question by:QPR
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22722991
Sim misplaced "end if"s, i think. Try this:
'**********************************************************************
'Visual Basic Transformation Script
'************************************************************************
 
'Copy each source column to the destination column
Function Main()
 
	if DTSSource("CHART_NAME")= "GGLCHART" OR DTSSource("CHART_NAME") = "GJCCHART" THEN
		if len(DTSSource("ACCNBRI")) = 11 then
			if mid(DTSSource("ACCNBRI"),6,6) <> "&&&&&&" then
 
				DTSDestination("column1") = left(DTSSource("Chart_Name"),2)
				DTSDestination("column1")= DTSDestination("column1")& mid(DTSSource("ACCNBRI"),6,6)& "1000"
				 
				DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
				DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
				 
				DTSDestination("column3") = DTSDestination("column1")
				DTSDestination("column4") = DTSDestination("column2")
				 
				Main = DTSTransformStat_OK
			else
				if mid(DTSSource("ACCNBRI"),3,3) <> "&&&" then
					 
					DTSDestination("column1") = left(DTSSource("Chart_Name"),2)
					DTSDestination("column1")= DTSDestination("column1")& "000000" & mid(DTSSource("ACCNBRI"),3,3) & "0"
					 
					DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
					DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
					 
					DTSDestination("column3") = DTSDestination("column1")
					DTSDestination("column4") = DTSDestination("column2")
					 
					Main = DTSTransformStat_OK
					 
				end if
			end if
		end if
	else
		if mid(DTSSource("ACCNBRI"),1,6) <> "&&&&&&" then
		 
			DTSDestination("column1") = left(DTSSource("Chart_Name"),2)
			DTSDestination("column1")= DTSDestination("column1")& mid(DTSSource("ACCNBRI"),1,6) & "1000"
			 
			DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
			DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
			 
			DTSDestination("column3") = DTSDestination("column1")
			DTSDestination("column4") = DTSDestination("column2")
			 
			Main = DTSTransformStat_OK
		 
		else
			Main = DTSTransformStat_SkipRow
		end if
	end if
 
 
	if DTSSource("CHART_NAME") = "GGLCHT" then
		if (left(DTSSource("ACCNBRI"),3) = "&&&" AND right(DTSSource("ACCNBRI"),6) = "&&&&&&" AND mid(DTSSource("ACCNBRI"),4,3) <> "&&&") then
 
			DTSDestination("column1") =mid(DTSSource("ACCNBRI"),4,3) 
			DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
			DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
			DTSDestination("column3") = DTSDestination("column1")
			DTSDestination("column4") = DTSDestination("column2")
			Main = DTSTransformStat_OK
 
		else
			Main = DTSTransformStat_SkipRow
		end if
	end if
	 
 
	if DTSSource("CHART_NAME") = "GNJCCHT" then
		if mid(DTSSource("ACCNBRI"),3,6) <> "&&&&&&" then
 
			DTSDestination("column1") = left(DTSSource("ACCNBRI"),8)
			DTSDestination("column2") = mid(DTSSource("DESCR2"),1,15)& " "&mid(DTSSource("DESCR1"),1,30)
			DTSDestination("column2") = ltrim(mid(DTSDestination("column2"),1,30) )
			DTSDestination("column3") = DTSDestination("column1")
			DTSDestination("column4") = DTSDestination("column2")
			Main = DTSTransformStat_OK
 
		else
			Main = DTSTransformStat_SkipRow
		end if
	end if
 
End Function

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22724512
same error - Function "in" was not found in the script
0
 
LVL 29

Author Comment

by:QPR
ID: 22724744
I deleted the transformation and repasted the activex back in and tested it (I should add that it parses just fine).
This time I get a new error....

script function 'main' returned an invalid DTStransformstatus value, or an activex script did not return an integral type. No further work will be done by the datapump.

huh?
0
 
LVL 29

Accepted Solution

by:
QPR earned 0 total points
ID: 22727654
hmmm solved it. Disconnected edit - task.
Step 1 had an entry point of "in" instead of "main"
somehow!
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Conditions in Where clause 9 46
Database maintenance 36 98
Need help with another query 10 34
How can I retrieve the column names from a T-SQL table? 2 13
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

680 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