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

param for MySQL data source in SSIS 2005

Hi experts,

I 'm trying to get countNumber from a select statement in MySQL source. Something likes this:

select @.countNumber = count(*) from tableName where condition_1 = xxx

I use ExecuteSQLTask to get the result set with result name @.countNumber along with ADO.NET connection type.
I 've installed mysql-connector-net-5.0.5 cause the source is MySQL 5x.

And I got a task failed error which is described below:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "new_process": "Result binding by name "@.new_process" is not supported for this connection type. ".

Please help me to fix this error, thanks alot

Best reguards,

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

|||

Rick wrote:

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

No, do not make the data type of the variable "object." Make it an integer.

|||

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

|||

Rick wrote:

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

Sure, the overhead is not needed. Plus, I don't think you can use an object variable in a precedence constraint, for instance (Unless SSIS can implicitly cast it). Object variables are designed to hold more than one row of data and/or multiple columns.|||Thanks Phil |||Hi experts,

At my first try, the task fails with integer data type.
I'm sorry Phil . But you are right when saying that object type is used to store more than one record with many columns.

I try it again with the object data type. It's cool but I have to run ScriptTask to get to cast type of the object var into int32.

Here is the way it runs:
Dim vars As Variables
Dim result As Int32
Dts.VariableDispenser.LockForRead("objVar")
Dts.VariableDispenser.GetVariables(vars)
Try
result = CInt(vars("objVar").Value)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Dts.Variables("intCount").Value = result

If you have other better ways to solve my problem, it will be appreciated

thanks and best reguards,

khanhmy|||

Have you tried

Define variable 'CountNumber' of type Int32

Query

Select count(*) As CountNumber from TABLENAME where condition_1 = xxxx

In the resultset window map CountNumber to User::CountNumber.

Hope this helps!

|||To back up Jay L's post, this is what I do. Now, I guess it depends on what data type is returned from the COUNT(*) against a MySQL database. Since it's like Oracle, it's probably a DAMN numeric(nn,0) which SSIS has issues with.

You could try:

select cast(count(*) as REAL) from TABLENAME where condition_1 = xxxx

Try stuffing that into a variable of type, double.

Parallelism in SSIS

Hi,

I would just like to confirm something with you guys...

Am I correct in saying that you dont need multiple connections to the same DB in a SSIS package in order to achieve parallel processing across multiple SQL tasks. In other words, I have 2 SQL tasks executing different stored procedures on the same DB that I want to run in parallel. They should be able to share one connection and still process in parallel, correct?

With that in mind, would the processing be faster if they each had their own connection?

Thanks in advance.

GBez wrote:

Hi,

I would just like to confirm something with you guys...

Am I correct in saying that you dont need multiple connections to the same DB in a SSIS package in order to achieve parallel processing across multiple SQL tasks. In other words, I have 2 SQL tasks executing different stored procedures on the same DB that I want to run in parallel. They should be able to share one connection and still process in parallel, correct?

They can share a connection manager but they still maintain seperate connections to the database. This is a good thing by the way.

GBez wrote:

With that in mind, would the processing be faster if they each had their own connection?

No. it wouldn't make any difference.

-Jamie

Parallel processing not supported in standard edition (use developer)

Hi,

I have a developer edition of SQL2005 upgraded to SP1 plus hotfixes on the dev box where I create my stuff.

Now, when I try to set a SSIS cube processing task to process in parallel, it tells me "Parallel processing is not supported in standard edition of analysis services."

Why?

I cannot edit the package on the enterprise edition used on production. I have to deploy as it is on dev.

Thanks,

Philippe

Hi,

The SQL Server build is 9.00.2153.00

I note that the problem exist only in the SSIS task. If I specify the parallel processing option when processing from management Studio, I do not get the error.

I bet it is a bug in SP1 or in the hotfix

Philippe

|||I have this exact same problem - I'm running Enterprise Edition, but in SSIS I get the error that "Parallel processing is not supported on STandard edition of Analysis Services". Can anyone confirm if this is a known issue and whether there is a workaround?

Parallel processing not supported in standard edition (use developer)

Hi,

I have a developer edition of SQL2005 upgraded to SP1 plus hotfixes on the dev box where I create my stuff.

Now, when I try to set a SSIS cube processing task to process in parallel, it tells me "Parallel processing is not supported in standard edition of analysis services."

Why?

I cannot edit the package on the enterprise edition used on production. I have to deploy as it is on dev.

Thanks,

Philippe

Hi,

The SQL Server build is 9.00.2153.00

I note that the problem exist only in the SSIS task. If I specify the parallel processing option when processing from management Studio, I do not get the error.

I bet it is a bug in SP1 or in the hotfix

Philippe

|||I have this exact same problem - I'm running Enterprise Edition, but in SSIS I get the error that "Parallel processing is not supported on STandard edition of Analysis Services". Can anyone confirm if this is a known issue and whether there is a workaround?

Parallel execution of source SQL commands

Hi,

we're accessing a SQL Server as a source for some SSIS packages using quite complex SQL commands. We have dataflows getting data from up to 10 queries. The problem is that SSIS starts all these queries in parallel using up all the memory of the server (the source SQL server, not the server SSIS is running on). So the queries are very slow. Is there any way to force SSIS to start the queries after each other?

I already browsed the web for some answers on that and I'm not very optimistic... Maybe the only solution is really to feed the result of the query in raw files and process them later...

Thanks,

Thomas,

I know you'll already have considered this but what is wrong executing them in sequence using precedence constraints?

Alternatively you could change the package's MaxConcurrentExectuables property which I'm guessing is currently set to -1.

-Jamie

|||

Jamie,

thanks for the fast reply...

As far as I understand (and that's what I see at least while debugging) precendence constraints and MaxConcurrentExecutables control only the workflow, not what's happening in the dataflow... The problem is that it's one dataflow with 10 queries feeding one destination. I guess both options don't help in this case...

|||

Thomas,

I'm sorry, I misunderstood. I thought you meant the queries were all in seperate data-flows. Now I understand your comment about raw files. Currently that is definately the only way to go.

Mind you, if they are all on the same server can you not just join all the tables together using a single SQL statement?

I have raised a similar request on Microsoft Connect about being able to define the order in which we insert to the destination adapters. You can see it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058

Why not click-through and add some detail about your source adapter problem as well? Or open it as a seperate issue. I would definately vote for it.

-Jamie

|||

Hi Jamie,

I hoped that there is a tweak for it... But that's what I expected...

I voted for your suggestion and opened up a new one since the problem and the solution is a little bit different... http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178069

THANKS!

|||

Excellent. i've voted and commented on it.

-Jamie

Parallel Development of SSIS packages

I have seen a number of posts regarding parallel development of SSIS packages and need some further information.

So far we have been developing SSIS packages along a single development stream and therefore have managed to avoid parallel development of our packages.

However, due to business pressures we will soon have multiple project streams running in parallel, and therefore multiple code branches, as part of that we will definitely need to redevelop the same SSIS packages in parallel. Judging from your post above and some testing we have done this is going to be a nightmare as we cannot merge the code. We can put in place processes to try and mitigate this but there are bound to be issues along the way.

Do you know whether this problem is going to be fixed? We are now using Team Foundation Server but presumably the merge algorythm used is same/similar to that of VSS and therefore very flaky?

However, not only are we having problems with the merging of the XML files, but we also use script tasks within the packages which are precompiled, as the DTSX files contain the binary objects associated with the script source code, if two developers change the same script task in isolated branches the binary is not recompiled as the merge software does not recognise this object.

Do you know whether these issues have been identified and are going to be fixed to be in line with the rest of Microsoft Configuration Managment principles of parallel development?

Many thanks.

[Microsoft follow-up] A question to be addressed here.|||

A. as long as no more then 1 developer works on the same dtsx package at a time there is no problem ( lock it in vss)

B. try to make the packages light, microsoft is not recommending heavy packages .

C. If you would like to merge codes from packages just copy all the elements from one package to the other ( use a container)

|||

In response to your points, I'm still not sure this is a satisfactory situtation for what is supposed to be an enterprise standard tool.

A - The problem we have is that we will have more than 1 developer working on the same package at the same time, and that those versions of packages will have to be deployed at different intervals. This is because of business priorities and is the normal development scenario on oither platforms such as .Net (C#) where you can have multiple branches with multiple developers working on the code. In these scenarios the Config Mgmt tool will wherever possible merge the source code automatically and raise conflicts wherever the code changes are in the same place to enable the Developer/Config Mgmt analyst to determine what cause of action to take. The problem we have here is the capability of the VSS or TFS to merge the DTSX packages is not accurate and our testing has proven that this sometimes works and more often doesn't.

Also say we deploy a package and that is running in production, as soon as it has gone live work on the next stream of development starts and the development team crack on making changes to the package. However, after a month in live a bug is identified in the package and an emergency fix is required to the DTSX package, the support team make this change to the live version to ensure that we fulfill the business needs as they cannot wait for the next development implementation for the fix. How do we merge that support change into the version of the package the development team are working on.... is this a manual process to physically open the development package as well and make the same changes in two places. Obviously this is very risky and introduces potential issues of developers knocking out support changes.

B - Wherever possible we have tried to keeping the packages light but specific ETL processes are always going to be quite intensive and include a lot of code, especially if working with a set of data within a pipeline.

C - When you mention merging the elements from one container to the other, how does this work if both version of packages also create new variables, add new connection managers, logging, checkpointing, error handlers or use the binary compiled objects from script tasks or even change the same script task.

I think the answer at the moment is that this cannot be done consistently and accurately but wanted to know whether Microsoft recognised this limitation of their product and how/if this would be addressed in future as the uptake and usage of SSIS increases, and this problem becomes more of an issue as development and support of ETL systems will be done in parallel.

Thanks.

|||

Hi Tom,

The SSIS team is aware of the issue, and your assessment is accurate. Right now SSIS packages essentially have to be treated as blobs, allowing only one person to work on them at a time because merging the XML is difficult. We have something in the works that should ease some of the problem, but we’re not sure if it will make it into the initial Katmai release at this point.

One alternative I’ve seen for projects/packages which change frequently is to switch to building the packages dynamically with code, instead of graphically in the UI. This makes the merging of changes much easier. However, the downside is that you lose the ease of use of the designer, and there can be a bit of learning curve for the object model.

Thanks,

~Matt

|||

Nice one Matt. Good to know that you guys are thinking about this. I'm interested to know what your " something in the works " is.

-Jamie

Parallel Development of SSIS packages

I have seen a number of posts regarding parallel development of SSIS packages and need some further information.

So far we have been developing SSIS packages along a single development stream and therefore have managed to avoid parallel development of our packages.

However, due to business pressures we will soon have multiple project streams running in parallel, and therefore multiple code branches, as part of that we will definitely need to redevelop the same SSIS packages in parallel. Judging from your post above and some testing we have done this is going to be a nightmare as we cannot merge the code. We can put in place processes to try and mitigate this but there are bound to be issues along the way.

Do you know whether this problem is going to be fixed? We are now using Team Foundation Server but presumably the merge algorythm used is same/similar to that of VSS and therefore very flaky?

However, not only are we having problems with the merging of the XML files, but we also use script tasks within the packages which are precompiled, as the DTSX files contain the binary objects associated with the script source code, if two developers change the same script task in isolated branches the binary is not recompiled as the merge software does not recognise this object.

Do you know whether these issues have been identified and are going to be fixed to be in line with the rest of Microsoft Configuration Managment principles of parallel development?

Many thanks.

[Microsoft follow-up] A question to be addressed here.|||

A. as long as no more then 1 developer works on the same dtsx package at a time there is no problem ( lock it in vss)

B. try to make the packages light, microsoft is not recommending heavy packages .

C. If you would like to merge codes from packages just copy all the elements from one package to the other ( use a container)

|||

In response to your points, I'm still not sure this is a satisfactory situtation for what is supposed to be an enterprise standard tool.

A - The problem we have is that we will have more than 1 developer working on the same package at the same time, and that those versions of packages will have to be deployed at different intervals. This is because of business priorities and is the normal development scenario on oither platforms such as .Net (C#) where you can have multiple branches with multiple developers working on the code. In these scenarios the Config Mgmt tool will wherever possible merge the source code automatically and raise conflicts wherever the code changes are in the same place to enable the Developer/Config Mgmt analyst to determine what cause of action to take. The problem we have here is the capability of the VSS or TFS to merge the DTSX packages is not accurate and our testing has proven that this sometimes works and more often doesn't.

Also say we deploy a package and that is running in production, as soon as it has gone live work on the next stream of development starts and the development team crack on making changes to the package. However, after a month in live a bug is identified in the package and an emergency fix is required to the DTSX package, the support team make this change to the live version to ensure that we fulfill the business needs as they cannot wait for the next development implementation for the fix. How do we merge that support change into the version of the package the development team are working on.... is this a manual process to physically open the development package as well and make the same changes in two places. Obviously this is very risky and introduces potential issues of developers knocking out support changes.

B - Wherever possible we have tried to keeping the packages light but specific ETL processes are always going to be quite intensive and include a lot of code, especially if working with a set of data within a pipeline.

C - When you mention merging the elements from one container to the other, how does this work if both version of packages also create new variables, add new connection managers, logging, checkpointing, error handlers or use the binary compiled objects from script tasks or even change the same script task.

I think the answer at the moment is that this cannot be done consistently and accurately but wanted to know whether Microsoft recognised this limitation of their product and how/if this would be addressed in future as the uptake and usage of SSIS increases, and this problem becomes more of an issue as development and support of ETL systems will be done in parallel.

Thanks.

|||

Hi Tom,

The SSIS team is aware of the issue, and your assessment is accurate. Right now SSIS packages essentially have to be treated as blobs, allowing only one person to work on them at a time because merging the XML is difficult. We have something in the works that should ease some of the problem, but we’re not sure if it will make it into the initial Katmai release at this point.

One alternative I’ve seen for projects/packages which change frequently is to switch to building the packages dynamically with code, instead of graphically in the UI. This makes the merging of changes much easier. However, the downside is that you lose the ease of use of the designer, and there can be a bit of learning curve for the object model.

Thanks,

~Matt

|||

Nice one Matt. Good to know that you guys are thinking about this. I'm interested to know what your " something in the works " is.

-Jamie

paradox to sql server 2005

Hi,
I have been told, I will have to transfer some paradox data into sql server 2005.
What is the best way to do this? i.e. if SSIS can be used, then what will be the source? I ask because there does not seem to be a source for Paradox.
Any thoughts please?
ThanksParadox came with an ODBC driver. Install the driver and use that as your source.|||

Do you mean in the DataFlow sources there should be ODBC sources? If so, how do I get it to appear there? Do I need to install ODBC driver first? where do I get it from please?

Thanks

|||

arkiboys wrote:

Do you mean in the DataFlow sources there should be ODBC sources? If so, how do I get it to appear there? Do I need to install ODBC driver first? where do I get it from please?

Thanks

Please reread Tom's post. If you can connect to Paradox, you should already have th ODBC driver loaded. Using a DataReader Source in your Data Flow, you should be able to see the Paradox ODBC driver listed as an option for you. If you don't then you need to work with the vendor of Paradox to get the ODBC driver.

Pakcage Execution Error under SQL server agent

Hi SSIS experts!

I have been trying to schedule a package I design to run off hour, but unable to do so. Here is a strange issue:

1. I am able to run and excute the package successfully through VSS. After I finished designing all my flows and containers, my exceution was successful to all my data sources.

2. I was able to deploy and run the actual package by sending to my local file system and it runs successfully through Execute Package Utility.

HOWEVER!!! when I tried to schedule this package through file system under sql server agent to run at night or through start job within SQL agent always failed...

I am puzzled so I added some logging on the package. The error message shows the following....

<message>The connection "{087B883F-D188-440A-9501-FF38CF5CEC28}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

<message>Failed to acquire connection "10.0.2.2.LogDB.jhwang". Connection may not be configured correctly or you may not have the right permissions on this connection.

But I thought if I had set the connection correctly to remember my passwords and using SQL server standard login within my package (connection manager) and should resolved the connection issues....

Why did it failed when I try to run it under sqlagent? But not through Execute Package Utility? Is there is a special setting I need to do for it to run under sql agent?

I notice within the job step when I choose the file source to point to my package... there was a tab called data sources where it has the connections I defined in my package. Does it matter if I put a check box on them or not? Either way they failed to connect.

Please help!

JON

Not sure what the problem is but you may want to debug it by using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie