Getting a reference to a Script task in VB.net

I am trying to get a reference to a script task so I can manipulate it's properties. However I can't seem to figure it out? I have a reference to Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.Design and based on BOL they show

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

Imports Microsoft.SqlServer.Dts.Runtime

But I get errors saying

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

can't be found? What do I need to reference or Import to be able to see the Tasks and/or Task types so I can convet InnerObject to a ScriptTask type and manipulate?

What am doing wrong?

Dim pkgPath As String = "C:\Program Files\\ETL\ODS\Policy\"Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ? = ctype(t.InnerObject,?)

End If

Next

You cannot manipulate properties of objects in the package directly from script within the package. Period.

What you CAN do is put expressions on properties which affect them at execution-time.

-Jamie

|||

I am not trying to do it from within a package. this is outside of SSIS in the vb.net IDE.

I have figured it out.

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

Module SSISPackage

Public Sub main()

Dim pkgPath As String = "C:\Program Files\Insurity\Reporting Decisions\ETL\ODS\Policy\"

Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ScriptTask = CType(t.InnerObject, ScriptTask)

Dim cp As ScriptTaskCodeProvider = sct.CodeProvider

Dim Moniker As String = "dts://Scripts/" & sct.VsaProjectName & "/" & sct.VsaProjectName & ".vsaproj"

Dim code As String = cp.GetSourceCode(Moniker)

End If

Next

But still does not seem to show the user text added by the developer? Strange.

|||

I had to add a reference to:

Microsoft.SqlServer.ScriptTask

Microsoft.SqlServer.VSAHosting

to my project and then this would work:

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

|||

Ah OK. Sorry, my bad!

Glad you got it working.

-Jamie

sql

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

sql

Getting - Setting Environment Variables

Hi,

1st. Issue

I have written a package that uses an environmet variable which contains certain information that the user must enter. A sql script task will then get the information from the environment variable and manipulate it.

I don't have a problem programmatically getting information from the environment variable. However, when I manually edit the contents of the environment variable, the changes won't take place unless the box is rebooted. Is there a way to refresh environment variables without rebooting?

2nd. Issue

I have written another package that also reads data from an environment variable but it must also manipulate the data and save the changes to the environment variable.

I have accomplished this programmatically by using GetEnvironmentVariable() and SetEnvironmentVariable(), but unfortunately the changes live during program execution only. Afte the program executes, the changes are wiped out.

How can I make changes stick to environment variables (programmatically)?

-- I found a solution to this:

When programmatically getting and/or setting an environment variable the 'target' parameter must be specified in the method call. By 'target' I mean the location in the registry where your environment variable is stored.

GetEnvironmentVariable( var, target )

SetEnvironmentVariable( OldValue, NewValue, target )

Please visit this link for more info:

http://msdn2.microsoft.com/en-us/library/96xafkes.aspx

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. Severson
I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID =
(SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:

>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a écrit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID = (SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:
>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID => (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
> >My task table contains the id of the task, the jobid that the task is
> >associated with, and a numeric value representing the priority of the
> >task. There can be multiple tasks for a job. I need a view that
> >returns the task id, and job id of the task with the higest priority
> >for each job. I tried grouping but this does not appear to work.
> >
> >Thanks for the help.
> >
> >Sean M. Severson

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a crit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID =
(SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:

>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:[vbcol=seagreen]
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID =
> (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
>

Get the variable from Execute Process Task to C#

Hi!

I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.

How do I get the variable in my C# code?
Thanks

CarlYour Main methods parameter collection?|||

Peter K wrote:

Your Main methods parameter collection?

yes. provided that this functionality has been built into the c# code.|||Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl|||

ctsand wrote:

Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl

did you include the variable as an argument to your c# executable in the execute process task?|||I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.|||

ctsand wrote:

I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?|||

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

In any case, can I put a dynamic variable in the argument?

Carl

|||

the code below is how to execute package in c# code.the red code tell you how to dymamic edit variable.i think this method can get variable.but i didn't try.please try it

//add reference "Microsoft.SqlServer.ManagedDTS"(in microsoft.sqlserver.manageddts.dll)
Imports Microsoft.SqlServer.Dts.Runtime

Dim pkg As String = "package directory"

Dim app As Application = New Application()
Dim p As Pakage = app.LoadPackage( pkg, Nothing )
p.InteractiveMode = true
Dim pty As DtsProperty

'Dim n As Integer = p.Configurations.Count

Dim strPty As String
For Each pty In p.Properties
strPty = pty.Name & ":"
Try
If pty.Get Then strPty &= pty.GetValue( pty ).ToString()
Catch
End Try
Console.WriteLine( strPty )
Next

Dim vir As Variables = p.Variables
vir( "strFile" ).Value = "C:\MyApp2.txt"

Console.WriteLine( p.Execute( Noting, vir, Nothing, Nothing, Nothing ).ToString() )

|||

ctsand wrote:

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

understood

In any case, can I put a dynamic variable in the argument?

Carl

i don't know. did you try?|||

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

|||

ctsand wrote:

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

what are the option settings in the process page of the execute process task editor?|||

RequireFullFileName = True

Executable = M:\Program\Person.exe

Arguments = fakt

WorkingDirectory = M:\Program

StandardInputVariable = User::test

StandardOutputVariable =

StandardErrorVariable =

FailTaskIfReturnCodeIsNotSuccessValue = True

SuccessValue = 0

TimeOut = 0

TerminateProcessAfterTimeOut = True

WindowStyle = Normal

|||what is the value and data type of User::test immediately before the execute process task starts executing? is the value of this variable correct?|||

The data type is string and the value is testing.

I have a question for you.

Is't meaning that I shall use the main-method to get the argument and the variable?

Carl

get the error message in scheduled task

Hello,
Is there a way to get the error message in a scheduled task?
I've a backup scheduled with two steps. The first step is the backup
procedure and the second step is a t-sql code which fires an email to
the backup operator.
How can i set the error in the email message if the schedule fails?
ThnxJust a quick thought. If you have both the backup and the emailing code in
the same step, may be you could capture the @.@.ERROR variable after the
backup completes/fails. If @.@.ERROR is not 0, then you know you have an
error.
The way I deal with this is, using Microsoft Operations Manager. You can get
this to email you when a job fails.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:uSlWOlbkFHA.3316@.TK2MSFTNGP14.phx.gbl...
Hello,
Is there a way to get the error message in a scheduled task?
I've a backup scheduled with two steps. The first step is the backup
procedure and the second step is a t-sql code which fires an email to
the backup operator.
How can i set the error in the email message if the schedule fails?
Thnx|||I find it easiest to define an output file for the first job step and includ
e that output file in
the emailing step.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jason" <jasonlewis@.hotrmail.com> wrote in message news:uSlWOlbkFHA.3316@.TK2MSFTNGP14.phx.g
bl...
> Hello,
> Is there a way to get the error message in a scheduled task?
> I've a backup scheduled with two steps. The first step is the backup proce
dure and the second step
> is a t-sql code which fires an email to the backup operator.
> How can i set the error in the email message if the schedule fails?
> Thnx

get records after executing a stored procedure

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@.countRun =?,
@.startTime =?,
@.endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

I am sure it is a type issue. SSIS has a VERY VERY irritating feature of not telling you it can't convert your var to the SQL type you set in the parameters section, it just ignores it and sets it to nothing.

Try setting your vars to "String" types and your parameters in the task to "VARCHAR". I bet it will work.

You might also try setting vars inside the SQL to the ?. I have had issues where it doesn't like ? in certain places.

DECLARE @.count INT, @.stime datetime, @.etime datetime
SET @.count = ?
SET @.stime = ?
SET @.etime = ?

EXEC [dbo].[ETLloadGROUPS]
@.countRun =@.count,
@.startTime =@.stime,
@.endTime = @.etime|||

Tom,

Thanks for the quick response. but guess am into a soup here... I have done the following in the parameters tab of the Execute SQL Task.

varName Direction Datatype ParaName

user::countRun Input varchar 0

user:endDate input varchar 1

user:runDate Input varchar 2

In the result set , I have done the following,

Result Name variable Name

0 user::countRun

1 user:endDate

2 user:runDate

al the three variables are of the datatype String.

when I execute the package its now failing with the error, the type of the value assigned to the variable differs from the current datatype. I guess the values from the table which are int and date are not accepted in this parameter mapping. How to handle this?

Thanks for the help so far

|||It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

|||

When you say "no rows are fetched" how are you determining this?

what are you doing with the results of the sqltask?

|||

Hi Jeff,

My whole idea is to query a table, get three values onto three variables, pass these values to a stored procedure and then get the entire set of records returned by the proc and then insert it to another OLE DB Destination.

|||

Tom Phillips wrote:

It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

I am selecting all the three values from a table and then passing them to a stored procedure.

|||

Hi All,

I have solved all the issues, now at the last summit though its giving me an error with the OLE DB Source component where am calling the stored proc. The erorr is, A rowset based on the SQL Command was not returned by the OLE DB Provider.

Any idea to resolve this ?

Thanks in advance.

|||adding "SET NOCOUNT ON" at the start of the stored proc resolved the issue. Thx for all the help :)

Get Previous step result in an SSIS Script Task

Hello,

I am using SQL Server 2005 Integration Services to create new values for my tables. One step I must do is execute a query and the script task that receive its constraint (it is set to completion) must do different things depending on the query result.

My question is: how can I know the result of the precedence constraint?

Thank you,

Pablo Orte

If a task executes then it matched your precedence constraint. If you say "Complete" and "VarA == 20", and it executes then you can be assured that was the result. Are you wanting to do branching, or have multiple precedent constraints?|||

The key point to what I think Sean is suggesting, is that if you can get a variable to hold the result, you could use an expression to influence the constraint, whether it is satisfied or not. Expression support on a constraint is very useful for influencing workflow.

I am not sure that this is useful to you, since you have to populate that variable somehow. One way would be to use an on error event handler on the task. Default the variable to true, and change it to false in the event handler.

I don't know why or exactly what you are trying to do, but my first reaction would be this sounds like a bad idea. I think there should be a better, more SSIS way of trying to achieve this. Forgive me, but your method reminds me of the hacks we used in DTS. To conditionally do something why not use two tasks, or why not use event handlers to do an out of band type operation when something fails.

|||

This thread might have somthing for you...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=859001&SiteID=1

Rafael Salas

|||

Hi and thanks for your replies,

The problem I have is that I have to do this in a script task situated after an "Execute SQL". This cannot return any parameter and I need to have something in the code like this

IF objDTSPackage.Steps("DTSStep_DTSExecuteSQLTask_1").ExecutionResult = 0 THEN

This code worked in SQL Server 2000, but not in SQL Server 2005.

Thanks,

Pablo Orte