Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

sql