[pywin32-bugs] [ pywin32-Bugs-1203013 ] Python won't run correctly under MS DTS

[SourceForge.net <no...@so...>]

Bugs item #1203013, was opened at 2005-05-17 04:07
Message generated for change (Comment added) made by mhammond
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=551954&aid=1203013&group_id=78018

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: com
Group: None
>Status: Closed
>Resolution: Fixed
Priority: 5
Submitted By: Carl Waldbieser (carl_waldbieser)
Assigned to: Nobody/Anonymous (nobody)
Summary: Python won't run correctly under MS DTS

Initial Comment:
Synopsis:
Microsoft Data Transformation Services (DTS) is part of
SQL Server 2000.  It is essentially a framework that
allows a user to transform data from a source (text
file, Excel, database, etc.) to a destination.  Part of
the framework allows the user to employ an ActiveX
Scripting language to perform the transformations. 
While I have been successful in using server other
ActiveX Scripting languages (VBScript, JScript,
ActivePerl) in this capacity, Python does not behave as
expected.

Environment:
I am using Python 2.4 with the Win32 extensions
installed and activated.  I believe the build of the
exetensions is 204 (the instaler file name is
pywin32-204.win32-py2.4.exe).  I am able to
successfully instantiate COM objects with Python in
stand alone programs, and server-side scripting with
Python in an ASP environment behaves normally.

Steps to reproduce:
I created a new DTS package in SQL Server 2000, and
entered Design Mode.  I created a data source and a
data destination (just a simple map from one table to
another).  I create a Transform Data Task from the
source to the destination.  In the Task properties
dialog, on the Transformations tab, I created a
transformation from one of the source columns to a
destination column.  I chose the type of transformation
to be "ActiveX Script".  In the properties dialog for
the script, I set the Language drop-down to "Python".

At this point, there are several noticable
discrepencies from normal behavior.  With any of the
other languages (including Perl), the "Functions"
dialog is populated.  With Python, it is not.  Also, if
I exit and re-enter the dialog for any of the other
languages, boilerplate transformation code is
generated.  For Python, there is no code.

By trial and error, I was able to determine that the
entry point for Python.  However, objects made
available to the other languages are inaccessible from
python.  The following code snippets in the four
languages I mentioned should demonstrate the problem:

#**********************************************************************
#  Perl Transformation Script
#************************************************************************

#  Copy each source column to the destination column
sub Main()
{
	$DTSDestination->Item("data")->{Value} =
$DTSSource->Item("data_value")->{Value};
	return 1;     # DTSTransformStat_OK;
}


//**********************************************************************
//  Java Transformation Script
//************************************************************************

//  Copy each source column to the destination column
function Main()
{
	DTSDestination("data") = DTSSource("data_value");
	return(DTSTransformStat_OK);
}


'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
	DTSDestination("data") = DTSSource("data_value")
	Main = DTSTransformStat_OK
End Function

######################################################################
# Python transformation script
# Not auto-generated.  Commented line produces an error
when 
# uncommented.
######################################################################
def Main():
	#DTSDestination["data"] = DTSSource["data_value"]
	return 0

The error indicates that the DTSDestination and
DTSSource names do not refer to any values at this
point in the program.  I can only hypothesise that
these global values are not be exposed correctly,
though I have no idea whether that is a problem with
the DTS or the Python side of the Active Script engine.
 Seems to work OK for the other languages, which is
what prompted me to report this bug.


----------------------------------------------------------------------

>Comment By: Mark Hammond (mhammond)
Date: 2005-06-27 18:16

Message:
Logged In: YES 
user_id=14198

I'm pretty sure we resolved this to your satisfaction -
please reopen if not.

----------------------------------------------------------------------

Comment By: Mark Hammond (mhammond)
Date: 2005-05-19 10:09

Message:
Logged In: YES 
user_id=14198

It may be better to take this to mail.  I note that there is
no AddNamedItem call for either DTSDestination or DTSSource.
 However, the object 'SQLActiveScriptHost' was added with
flags which include SCRIPTITEM_GLOBALMEMBERS.

So it seems that Python is unable to extract these names
from the object.  There are 2 things you can try:

* Explicitly say "SQLActiveScriptHost.DTSDestination" - that
may avoid the issue.  You could try "print
dir(SQLActiveScriptHost)" to see what is there.

* Open pyscript.py and search for IsGlobal() - that block of
code is where we try and enumerate all "global" objects for
their children.  You could add a few print statements to
this loop and see what it is finding (or not finding)

Everything in that trace seems correct (The None is not an
issue - AddNamedItem only takes 2 params - the name and
flags.  We remember them and later query the engine for
those names)

----------------------------------------------------------------------

Comment By: Carl Waldbieser (carl_waldbieser)
Date: 2005-05-19 09:37

Message:
Logged In: YES 
user_id=997941

Mark,

Thanks, for the advice.  Here is the output I got:

# This window will display output from any programs that
import win32traceutil
# win32com servers registered with '--debug' are in this
category.
Object with win32trace dispatcher created (object=None)
 in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-SetScriptSite(<PyIActiveScriptSite at
0x3c39124 with obj at 0x2a36f38>,) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-InitNew() [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'SQLActiveScriptHost',
10) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-SetScriptState(1,) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddTypeLib(IID('{10010200-740B-11D0-AE7B-00AA004A34D5}'),
2, 0, 0) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSErrorRecords', 2)
[1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSGlobalVariables',
2) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSTransformPhaseInfo',
2) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-ParseScriptText(u'#\'**********************************************************************\r\n#\'
 Visual Basic Transformation
Script\r\n#\'************************************************************************\r\n\r\n#\'
 Copy each source column to the destination
column\r\n#Function Main()\r\n#\tDTSDestination("data") =
DTSSource("data_value")\r\n#\tMain =
DTSTransformStat_OK\r\n#End Function\r\n\r\ndef
Main():\r\n\tDTSDestination["data"] =
DTSSource["data_value"]\r\n\treturn DTSTransformStat_OK',
None, None, None, 0, 0, 2, 0) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-GetScriptDispatch(None,) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSSource', 2) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSDestination', 2)
[1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSGlobalVariables',
2) [1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-AddNamedItem(u'DTSErrorRecords', 2)
[1,0,None]
in <win32com.axscript.client.pyscript.PyScript instance at
0x03D810A8>._InvokeEx_-Close() [1,0,None]
-----------------------------------

I am pretty well versed in COM and Python, but I am not sure
what I am looking at.  I would guess that it looks like the
"AddNamedItem" functions are being passed empty (None)
values instead of the actual objects.

I tried using DTS with Python back when I had Python 2.3,
and I had similar problems, so I think you can rule version
differences out as a problem.

I have developer tools on my workstation, so if you need me
to try to compile something or run some other sorts of
tests, I may be able to help in that regard.

Thanks,
Carl W.


----------------------------------------------------------------------

Comment By: Mark Hammond (mhammond)
Date: 2005-05-17 12:21

Message:
Logged In: YES 
user_id=14198

Could you please try the following:
* Change to your
\python\lib\site-packages\win32comext\axscript\client directory
* Execute 'pyscript.py --debug'
* Start Pythonwin and select tools->"Remote Trace Collector
Tool"
* Re-try your test

You should see lots of output printed to Pythonwin. 
Hopefully you will also see a traceback, which will help us
narrow it down.  If you do see one, please paste it here.

If you don't it might be worth testing Python 2.3 and the
matching pywin32, just to see if it is an issue relating to
Python 2.4 or the VC7 used to build it.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=551954&aid=1203013&group_id=78018