• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

DTS (ActiveX) Error

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
QPR
Asked:
QPR
  • 3
1 Solution
 
ZberteocCommented:
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
 
QPRAuthor Commented:
same error - Function "in" was not found in the script
0
 
QPRAuthor Commented:
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
 
QPRAuthor Commented:
hmmm solved it. Disconnected edit - task.
Step 1 had an entry point of "in" instead of "main"
somehow!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now