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

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

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

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

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

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005

Hi All,

Parameter passing in SSIS 2005 sometimes appears to be a cumbursome task. I have been digging into this topic for quite some time and here i note down some simple steps to demonstrate parameter passing at Package level.

(1) Create a SSIS project using Business Intelligence 2005 Or VS 2005.

(2) Create datasource (.ds) and Data Source View as required.

(3) A default SSIS Package by the name Package.dtsx is created. Double click this and you are shown tabs for Control Flow, Data Flow, Event Handlers, Package Explorer. On the Control Flow, drap and drop Execute SQL Task from Control Flow Items in the toolbar.

(4) Lets now create a variable at Package level. Right click anywhere in the control flow box (not on the Task created in Step 3 above). Click on the Variables on the context menu displayed. Variables window appears on the left of the screen. Click the Add Variable box in this window to create a variable. Name it var1 (or whatever you may like), Scope as Package, DataType as String and Value as MyValue. This is only the default value.

(5) Now let us edit the SQL Task created in Step 3. Double on it, on the General tab you can change its Name, Description. Set ResultSet as None. We shall proceed to execute a stored procedure by name MySPName and pass it a parameter. Set ConnectionType as OLE DB. Select the connection you creates in step 2. Set SQLSourceType as Direct input. SQLStatement as MySPName ? . Note the ? mark after the name of the stored procedure. This is important to accept the variable value (var1) created in Step 4.

(6) Select Parameter Mapping tab now. Click on Add button. Select the Variable Name as User::var1. This is the user created variable of Step 4. Select Direction as Input, DataType as Varchar and Parameter Name as @.var1. Click on OK now.

(7) This sets up the basic of parameter passing. Compile the project to verify everything works. Right Click on the SQL Task and select Execute Task. This will execute the package taking default value of the variable. This can be used along with dtexec command with /set option to pass the parameter at command prompt.

---

Hi Rupesh,

Can we manually pass the parameter (source file name) to SSIS package from .net application?

if yes then what would be approach for this?

Please provide me some pointers on this.

Thanks,

Anshu

|||

Hi Rupesh,

I followed ur steps to pass parameter in SSIS 2005.

I am passing a datetime variable to my stored procedure usp_GenerateCompEvents ? @.as_of_date with the "?" as you had mentioned in ur message in my sqlstatement.

And I also set my resultset to none in the General Tab.

And I mapped the parameter to a user defined variable of data type DBDATE and when I executed the task it gave me an error message

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "The type is not supported.DBTYPE_DBDATE". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

When I changed the datatype to DATE it gave me the error msg

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "Incorrect syntax near '@.as_of_date'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

When I changed the datatype to DBTIMESTAMP it gave me the error msg

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

Pls. let me know what's wrong with the Parameter Mapping or is there any syntax error with the SQLStatement.

Thanks,

Sridevi

|||

Sorry for replying late Anshu. The steps which i have mentioned is used to manually pass the parameter values. If i could understand the scenario you want about passing parameter from a .net application, i could post a better reply.Anyways, You can execute a package in the command prompt as:

dtexec /set \Package.Variables[User::var1].Properties[Value];yoursourcefilename /f D:\MyPackageLocation\Package.dtsx'

where yoursourcefilename is the name of the file you want to pass to the package located at MyPackageLocation.

Now you can use the Process and ProcessStartInfo class from your .net application to run the same command and pass the value like:

Code Snippet

string myFileName = "abc.txt";

string txtToRun = "dtexec /set \Package.Variables[User::var1].Properties[Value];"+myFileName+" /f :\MyPackageLocation\Package.dtsx";

Process.Start("CMD.exe", txtToRun); // in System.Diagnostics namespace

Hope this idea might help.. Thanks... Rupesh

|||

I guess you are following Step 5. Also, it seems you have written usp_GenerateCompEvents ? @.as_of_date in the sql statement instead of usp_GenerateCompEvents ? . Try removing the @.as_of_date variable from your statement.

Regards, Rupesh...

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005

Hi All,

Parameter passing in SSIS 2005 sometimes appears to be a cumbursome task. I have been digging into this topic for quite some time and here i note down some simple steps to demonstrate parameter passing at Package level.

(1) Create a SSIS project using Business Intelligence 2005 Or VS 2005.

(2) Create datasource (.ds) and Data Source View as required.

(3) A default SSIS Package by the name Package.dtsx is created. Double click this and you are shown tabs for Control Flow, Data Flow, Event Handlers, Package Explorer. On the Control Flow, drap and drop Execute SQL Task from Control Flow Items in the toolbar.

(4) Lets now create a variable at Package level. Right click anywhere in the control flow box (not on the Task created in Step 3 above). Click on the Variables on the context menu displayed. Variables window appears on the left of the screen. Click the Add Variable box in this window to create a variable. Name it var1 (or whatever you may like), Scope as Package, DataType as String and Value as MyValue. This is only the default value.

(5) Now let us edit the SQL Task created in Step 3. Double on it, on the General tab you can change its Name, Description. Set ResultSet as None. We shall proceed to execute a stored procedure by name MySPName and pass it a parameter. Set ConnectionType as OLE DB. Select the connection you creates in step 2. Set SQLSourceType as Direct input. SQLStatement as MySPName ? . Note the ? mark after the name of the stored procedure. This is important to accept the variable value (var1) created in Step 4.

(6) Select Parameter Mapping tab now. Click on Add button. Select the Variable Name as User::var1. This is the user created variable of Step 4. Select Direction as Input, DataType as Varchar and Parameter Name as @.var1. Click on OK now.

(7) This sets up the basic of parameter passing. Compile the project to verify everything works. Right Click on the SQL Task and select Execute Task. This will execute the package taking default value of the variable. This can be used along with dtexec command with /set option to pass the parameter at command prompt.

---

Hi Rupesh,

Can we manually pass the parameter (source file name) to SSIS package from .net application?

if yes then what would be approach for this?

Please provide me some pointers on this.

Thanks,

Anshu

|||

Hi Rupesh,

I followed ur steps to pass parameter in SSIS 2005.

I am passing a datetime variable to my stored procedure usp_GenerateCompEvents ? @.as_of_date with the "?" as you had mentioned in ur message in my sqlstatement.

And I also set my resultset to none in the General Tab.

And I mapped the parameter to a user defined variable of data type DBDATE and when I executed the task it gave me an error message

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "The type is not supported.DBTYPE_DBDATE". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

When I changed the datatype to DATE it gave me the error msg

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "Incorrect syntax near '@.as_of_date'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

When I changed the datatype to DBTIMESTAMP it gave me the error msg

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

Pls. let me know what's wrong with the Parameter Mapping or is there any syntax error with the SQLStatement.

Thanks,

Sridevi

|||

Sorry for replying late Anshu. The steps which i have mentioned is used to manually pass the parameter values. If i could understand the scenario you want about passing parameter from a .net application, i could post a better reply.Anyways, You can execute a package in the command prompt as:

dtexec /set \Package.Variables[User::var1].Properties[Value];yoursourcefilename /f D:\MyPackageLocation\Package.dtsx'

where yoursourcefilename is the name of the file you want to pass to the package located at MyPackageLocation.

Now you can use the Process and ProcessStartInfo class from your .net application to run the same command and pass the value like:

Code Snippet

string myFileName = "abc.txt";

string txtToRun = "dtexec /set \Package.Variables[User::var1].Properties[Value];"+myFileName+" /f :\MyPackageLocation\Package.dtsx";

Process.Start("CMD.exe", txtToRun); // in System.Diagnostics namespace

Hope this idea might help.. Thanks... Rupesh

|||

I guess you are following Step 5. Also, it seems you have written usp_GenerateCompEvents ? @.as_of_date in the sql statement instead of usp_GenerateCompEvents ? . Try removing the @.as_of_date variable from your statement.

Regards, Rupesh...

sql