Executing a DTS from another DTS...

Hi there,
I am trying to execute a DTS package from another DTS
package and change some variables in the original DTS. I
have used the info on the following page but it doesn't
work: http://www.sqldts.com/default.aspx?215. Would you
have any input?
Thanks,
Andreas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Andreas Terzis" <gr8patra@.pacbell.net> wrote in message
news:40709788$0$202$75868355@.news.frii.net...
> Hi there,
> I am trying to execute a DTS package from another DTS
> package and change some variables in the original DTS. I
> have used the info on the following page but it doesn't
> work: http://www.sqldts.com/default.aspx?215. Would you
> have any input?
> Thanks,
> Andreas
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You'll need to give more information - exactly what does "doesn't work"
mean? Did you get any error messages? Which version of SQL Server are you
using? Have you tried using the Execute Package task?

You might also want to post to microsoft.public.sqlserver.dts.

Simon|||"Doesn't work" means that I get no errors when I execute the child DTS
but nothing happens; in other words the child
package doesn't get executed. My child package is not saved with an
owner or other password. I am using SQL Server 2000. I will try the
Execute DTS task, but I wanted to try the ActiveX method first because
in the future I want to pass variables from the child to the parent
package. Do you know anything about the Yukon version of DTS? Is it
going to have enhancements over the 2000 version?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Execute SQL task to update variables

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek

What are the symptoms of this not working? Are there errors or warnings?

Donald

|||

Hi Donald

No symptoms or problems, the package executes successfully. Apart from the fact that the variables aren't updated at the end of the package when I look at them in the variable screen.

Derek

|||

Hi Derek,

The Variable Screen will always show the Initial Value set for that variable.

To check whether your program works fine -

1) Change the DateTime variable to 'Package' Scope.
2) Place a breakpoint for the PreExecute Event for the following task
3) Execute the Package and When the program stops for the BreakPoint - Select Debug Option -> Windows -> Locals
4) Scroll through the list and Locate the DateTime Variable.
5) You will find that the DatTime Variable is updated with the value returned by the SQL Task.

Alternatively -

1) Change the DateTime variable to 'Package' Scope.
2) On Success of the SQL Task - Have a Script Task
3) Put that DateTimeVariable in ReadOnlyVariables list in Script Task
4) Have the following lines of code in the Public Sub Main() method in the script task

Windows.Forms.MessageBox.Show(Dts.Variables("<DateTimeVariable>").Value.ToString())
Dts.TaskResult = Dts.Results.Success

5) You will observe that the datetime value returned by the SQL Statement in SQL Task will be poped up in a small popup window.

After the successful execution of the package - if you go to the Variables window - you will observe that it will only show the initial value. The runtime value assigned to the DateTimeVaribale isn't stored permanently and the runtime value will only be available during execution.

Thanks,
Loonysan

|||

Thanks Loonysan

you were right, it was working just couldn't see it.

Cheers,

Derek

Execute SQL Task Error

Hi:

I am getting the following error message while trying to run a Execute SQL task with Variables in BIDS. My connection type is ADO.NET . My Variables defined are

Varout and Varin. Both are String type Variables. @.varout has a value set to "Category" and @.varin has a value set to "Test Category". I am using the expression

" Select * into " + @.[User::VarOut] + " FROM " + @.[User::Varin]

The expression eveluates correctly. The error I get when i run the package is:

Package Validation Error. Failed to lock Variable "Select * into TestCategory from category" for read access with error 0XC0010001. The Variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variables collection on a container during the execution of package and the variable is not there. The Variable name may have changed or the Variable is not being created.

Can anyone please tell me what I am doing wrong or where do I need to look at?.

Thank you

AK

Can anyone please please give me a solution. This is really urgent. I found a KB describing a issue related to Script task but not sure if it is applicable to this issue. It involves applying a hot fix. Anyone from MS please comment.|||this link might help: http://msdn2.microsoft.com/en-us/library/ms141003.aspx|||

Thanks Duane. That Article helped. I was selecting the SQLSourcetype as Variable and that was throwing errors. I changed it and my package runs like a charm!!!.

Thanks again.

Execute SQL Task Editor - Variables

Hi,

Im trying to do an Insert:

INSERT INTO myTable(column1)

VALUES(..)

How can I use my parameter mapping value inside the SQL statement ablove?

Thank you.

Use ? where you want to use the parameters.

INSERT INTO myTable(column1)

VALUES (?)

|||

My SQL Task looks as follows:

Parameter Mapping:

User::ID Direction: Input Data Type: VarChar ParameterName: ID

SQL SourceType:

Direct Input

INSERT INTO Table1(id, name)
VALUES (1,'john')

How can I use my Parameter Mapping value inside the SQL Insert statement (instead of the 1 I put above)?

Thank you.

|||Try using a parameterName of 0 (zero)

Then:
INSERT INTO Table1 (id, name) VALUES (?,'john')|||

It worked!!

Thanks a lot!!

Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.

Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.

Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.

Execute Process Task Arguments

HI,

Is it possible to provide variables ( multiple variables ) in the arguments parameters of the Execute Process Task?

Thanks

Shafiq

You can use multiple command arguements in one task by using spaces to delimit arguements.

Thanks,
Loonysan

|||To use variables in the process arguments, use Expressions tab and define an expression for Arguments property. You can use multiple variables there.

Execute Package Task: setting child variables

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

fleo wrote:

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

No, it's not possible using the Execute Package Task. However, using Parent Package Configurations, you can hook the parent variable to a child variable.|||

fleo wrote:

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

You could use execute process task to run the child packages via dtexec and using the /SET option for pushing the parameter. The only problem I see with that is that the master package will not know when the package finish as opposed to Execute package task.

Just an idea you do the test.

|||

We have a "common" stored proc to run SSIS packages (dtexec is called by that sp).
It can be used in a Execute SQL task and we are able to determine when the execution is done and wheter the package was executed successfully. But building the command and retrieving the result is quite a hassle...

The parent/child package configuration will do the trick. The only thing I dislike is the parent being dependent upon the child (maybe I'm from another school of thought).

Thanks all for your input.

Execute DTS 2000 Package Task Editor (Inner Variables vs Outer Variables)

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

Jo?o

Can anyone Help me? Any help is welcome. Thanks a lot!

Jo?o

|||

Hi,

Were you able to pass the variables from SSIS to DTS package (called from SSIS using Execute DTS 2000 package task) and back to the parent package (SSIS) global variables?

Thanks in advance,

Subah.

|||

The outer variables are a means of communication from the SSIS package to the DTS package it executes using late binding (the outer variable is evaluated before the DTS package is executed and not earlier).

We map the outer variables from the SSIS package as global variables to the DTS package. E.g. if you add an outer variable called User::mystring variable to the list, we strip the namespace (because DTS does not know about namespaces) and pass it as a global variable named mystring to the DTS package.

|||Thanks, that helped my understanding a lot. I was able to implement the same.

Execute DTS 2000 Package Task Editor (Inner Variables vs Outer Variables)

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

Jo?o

Can anyone Help me? Any help is welcome. Thanks a lot!

Jo?o

|||

Hi,

Were you able to pass the variables from SSIS to DTS package (called from SSIS using Execute DTS 2000 package task) and back to the parent package (SSIS) global variables?

Thanks in advance,

Subah.

|||

The outer variables are a means of communication from the SSIS package to the DTS package it executes using late binding (the outer variable is evaluated before the DTS package is executed and not earlier).

We map the outer variables from the SSIS package as global variables to the DTS package. E.g. if you add an outer variable called User::mystring variable to the list, we strip the namespace (because DTS does not know about namespaces) and pass it as a global variable named mystring to the DTS package.

|||Thanks, that helped my understanding a lot. I was able to implement the same.