Hi,

I have a simple DTS that loads data from a CSV (Comma Separated File) into a
table.
This DTS is called from a VB.NET Windows Application.
I defined 4 global variables with default values (Then I can run the DTS
indepently from the Enterprise Manager, for testing purpose).
The 4 global variables are related to the Connection objects. 1 for the Text
File Connection Object, 3 for the SQLConnection object.
The global variable for the Text File connection is the global variable that
brings problem
The 4 variables are defined with their type being set as string (default
type when I create the global variable)

When I run the DTS from Enterprise Manager, everything works fine.
When I run the DTS from the VB.NET Windows Application it works, except if I
set a value for the Text File connection object. The other values can be
passed from .NET to the global variables in the DTS without any problem.
When I set a value to the global property that is assigned to the Text File
connection data source value. That value is in fact the full path name of
the CSV file. The error type returned by the DTS COM object is "type
mismatch" during the step of the DTS that assigns values to the global
variables. When this is not set in the Windows application, the DTS use the
default value that I defined in the DTS and everything works!

I am banging my head on this issue and any idea/clue would be greatly
appreciated.

Best regards,

Francois

You are calling the package through .Net but setting values on GVs which
in turn in the package set properties of the connection objects?

Why not just set the connection object properties through VB.Net and cut
out the GV middleman?

If you cannot do that then have your package throw out a msgbox which
will tell you the value it thinks the GV holds right before you use it.

Allan

"Francois Malgreve" <francois.malgreveADgmail.com> wrote

Dear Allan,

Thank you so much for suggesting me this. I looked in BOL and I indeed see
that there is a Connection object. Nevertheless, I do not know how to get a
reference to the connection I have in my DTS. My DTS has 2 connections, one
to read the text file, one to write into a SQL Database.
I was looking for some sort of GET Method with a connection name (then I can
retrieve the correct connection) but I did not find anything like that. I
also have to admit that I am new in SQL Server and DTS programming. I am
very unfamiliar with its API.

Is there any code sample or link to code sample that you could provide me?

Thanks again for your help, it is very appreciated.

Best regards,

Francois.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hi again,

Well I found an example that I could reuse:

http://www.databasejournal.com/featu...le.php/1461481

But the problem is that the package still fails to run. When I set the value
of the properties within the DTS and run it from there then it works. When i
set the value from VB.Net, the values are well changed (I can see them
change in the begugger) but the package still fails when it runs.
The only time i can run the package from VB.NET without making it fails is
when i do not change any of it's properties, just call the execute()
directly!

I am very confused now. Also I registerd the OnError event but it never
triggers. Well it used to trigger before but not anymore. I cannot explain
all those strange behaviors.

Did anyone ever experience something like that before?

Francois



"Francois Malgreve" <francois.malgreveADgmail.com> wrote