Executing package from C# on 64bit platform

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

sql

Executing package from C#

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection ty

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Hi All,
We are running a job for archiving old data. The job calls a stored
procedure. The data gets copied successfully to a different database but when
a query is run to delete the data from the source database, it does not get
completed.
What is it trying to do ? Why does it not move forward?
The profiles shows the following continuously...
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- sp_sqlagent_get_perf_counters
SET NOCOUNT ON
-- sp_sqlagent_get_perf_counters
CREATE TABLE #temp
(
performance_condition NVARCHAR(1024) COLLATE database_default NOT NULL
)
-- sp_sqlagent_get_perf_counters
INSERT INTO #temp VALUES (N'dummy')
IF (@.all_counters = 0)
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|',
performance_condition, PATINDEX('%[_|_]%', performance_condition) + 1) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE spi1.cntr_type
WHEN 537003008 -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE
spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM
master.dbo.sysperfinfo spi2
WHERE
(spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('%
Base%', spi2.counter_name)))
AND
(spi1.instance_name = spi2.instance_name)
AND
(spi2.cntr_type = 1073939459))
ELSE spi1.cntr_value
END
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@.all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' +
RTRIM(spi1.counter_name)))
sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
SQL Agent service. There might be an entry in the SQL Server registry under
the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
the sampling interval. You can reduce this default by changing the value
there.
Alternatively you can totally avoid them by removing all the alerts set in
SQL Server( some of them are demo alerts set by SQL Server install by
default). Goto EM, under management, SQL Server Agent, delete all the
alerts. If you have no alerts the procedure will not run.
Anith
|||Hi Anith,
Thanks for a quick reply Do these alerts affect the performance of any
other query running in parallel ? One of our queries to delete bulk records
is taking a helll lot of time and is not returning, profiler traces show that
the query has started and then a large number of these
sp_sqlagent_get_perf_counters are shown, can you give some more inputs?
Regards
Sachin
"Anith Sen" wrote:

> sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
> SQL Agent service. There might be an entry in the SQL Server registry under
> the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
> the sampling interval. You can reduce this default by changing the value
> there.
> Alternatively you can totally avoid them by removing all the alerts set in
> SQL Server( some of them are demo alerts set by SQL Server install by
> default). Goto EM, under management, SQL Server Agent, delete all the
> alerts. If you have no alerts the procedure will not run.
> --
> Anith
>
>
|||>> Do these alerts affect the performance of any other query running in[vbcol=seagreen]
Generally, it should be negligible. However, you can check the trace file to
see how often this procedure is being run. If it is being run every 5 sec,
or 10 sec depending on the polling interval, then in heavy transaction
oriented systems, it might have some impact.
[vbcol=seagreen]
I do not have a first hand experience of how it impacts huge deletes, but on
a related note, do you have a profiler trace running 24/7 on the production
machine? In transaction-heavy systems, that itself can have dampening effect
on the overall performance.
Anith
|||No we do not have profiler running on production. Its just on the test
environment.
Not sure what is it trying to do?
And yes, I checked for msdb.dbo.sp_sqlagent_get_perf_counters ? Its taking
around 15-16 ms
Please find below some other traces as well.
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
...
...
-- SET TEXTSIZE 64512
-- select @.@.microsoftversion
-- select convert(sysname, serverproperty(N'servername'))
-- SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
-- EXECUTE msdb.dbo.sp_help_jobstep @.job_id =
0x63314D2F34B5AC449E29CF142843F50F
-- EXECUTE @.retval = sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
-- EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
-- exec @.retcode = dbo.sp_MSsubscription_cleanup @.cutoff_time
Thanks,
Sachin
"Anith Sen" wrote:

> Generally, it should be negligible. However, you can check the trace file to
> see how often this procedure is being run. If it is being run every 5 sec,
> or 10 sec depending on the polling interval, then in heavy transaction
> oriented systems, it might have some impact.
>
> I do not have a first hand experience of how it impacts huge deletes, but on
> a related note, do you have a profiler trace running 24/7 on the production
> machine? In transaction-heavy systems, that itself can have dampening effect
> on the overall performance.
> --
> Anith
>
>

execute dts 2000 failure

I have used to the execute dts 2000 task in Visual Studio to successfully execute a structured storage file for a simple DTS package.

I am trying to use the same method for another DTS package, which executes five other packages. I've tried both the 'structured storage' and 'embedded in task' methods pointing to the parent package but in debugging mode the execution immedately fails. When I execute the parent package in Management Studio, it successfully executes the five child packages. I am including the debugging message below, thogh it doesn't help me any. Does anyone know what the problem is?

SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" starting.
Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
Warning: 0x80019002 at BPSA_LOAD_ACCT_SUMMARY_NEW: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" finished: Failure.

My misunderstanding.

I realize now that I have to execute the 5 DTS 2000 package tasks in the one 2005 project.

execute dts 2000 failuer

I have used to the execute dts 2000 task in Visual Studio to successfully execute a structured storage file for a simple DTS package.

I am trying to use the same method for another DTS package, which executes five other packages. I've tried both the 'structured storage' and 'embedded in task' methods pointing to the parent package but in debugging mode the execution immedately fails. When I execute the parent package in Management Studio, it successfully executes the five child packages. I am including the debugging message below, thogh it doesn't help me any. Does anyone know what the problem is?

SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" starting.
Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
Warning: 0x80019002 at BPSA_LOAD_ACCT_SUMMARY_NEW: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "BPSA_LOAD_ACCT_SUMMARY_NEW.dtSX" finished: Failure.

My misunderstanding.

I realize now that I have to execute the 5 DTS 2000 package tasks in the one 2005 project.

|||I have small info about this problem. The real message in the DTS(2000) package log (but no in SSIS "Execute DTS 2000 Package Task" on SQL 2005 Server!) is:
"Step Error Source:
Step Error Description:(1:"My DTS 2000 Task Name") SubStep 'DTSStep_DTSExecutePackageTask_1' failed with the following error:
Exception of type 'System.OutOfMemoryException' was thrown.
Execution was canceled by user.Exception of type 'System.OutOfMemoryException' was thrown.
Step Error code: 80040427
Step Error Help File:
Step Error Help Context ID:0"
And this problem interrelated, as i any more know, with parallel execution of nested packages on DTS ("Execute on main package tread" option).
If you are yet interesting this problem - answer me there or email on sas72@.rambler.ru