Execute Package Task: Setting Child Variables

Feb 13, 2007

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

View 4 Replies


ADVERTISEMENT

Setting Child Package Inner Variables Dynamically

Oct 19, 2006

Hi,

I am trying to execute a DTS on sql server 2000 using the "Execute DTS 2000 Package Task".

I can see the inner variables and when I set the values everything works fine.

Now I want to pass the values dynamically. How can I achieve this?

Many thanks

View 9 Replies View Related

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

Sep 4, 2006

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





View 8 Replies View Related

Execute Package Task Can't Continue When The Child Package Failed?

Aug 25, 2006

I use a execute package task to run a child package in which I run some sql task.

as the error handle I insert a script task and link a line from execute package task to script task

of course the line is red,

but I found when the child package failed, the execute package task turns red,it stopped

the script task can't be run, I don't konw why?

View 9 Replies View Related

Using Package Variables As Parameters In Execute Sql Task

Feb 23, 2006

Hi,

I need to run an SQL command, in which one of the fields is based on the current time, represented as a string in a specific format.

I used a script task to create the string from the current time, and store it in a package variable named "newDate". Later, in the execute sql task, I have the following update statement: "update table_1 set field_1 = ?" (OLE DB connection is used)

and in the parameters mapping I set parameter 0 to the string package variable "newDate".

The problem is that on runtime, the sql executes with the default value of the variable - i.e. it doesn't take the new value.

I checked it and placed another script task after the first one, that simply tries to read the variable "newDate" and print it to screen, but even here the default value is used.

how can I use the new value of the variable?

thanks.

View 7 Replies View Related

Package Variables Passed To Execute SQL Task

Aug 14, 2007

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.
Verified I'm using an OLE DB valid connection.
Verified the variables are properly mapped in the parameters mapping section of the task properties.
Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).
Changed the Bypassprepare property to 'True'.
Breakpoints and watch windows to verify the variable values.



I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

View 5 Replies View Related

Passing Global Variables From A Execute Package Task

Apr 28, 2004

I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?

View 6 Replies View Related

Execute DTS 2000 Package Task: Invalid GUID When Setting PackageId Via Expression

May 24, 2007

Hi there,

I'm creating an SSIS package that will execute legacy dts packages. The package to be executed is decided at runtime using a sql query task.

Executing a dts package statically works fine, but when I try to set the details of the dts to be run via expressions, I get the error below.

To make it dynamic, I created a variable of type string, and put the package name in here. I also have a string variable for the packageid. Then I set up an expression on the Execute DTS 2000 Package Task that sets the PackageName & PackageID property to this variable.

The PackageId is a string variable I've retrieved using:

select top 1 name,
cast(id AS varchar(50)) id,
cast(versionid AS varchar(50)) versionid
from sysdtspackages
where name = @PackageName
order by createdate desc

When I use the task to set the package id it works find (by selecting a dts, then changing the name), but when I try to provide the package id I get this message:


Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified.
at DTS.PackageClass.LoadFromSQLServer(String ServerName, String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags Flags, String PackagePassword, String PackageGuid, String PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.PackageUtils.LoadDTS8Package(String pkgSrc, String sourceUser, String sourcePwd, Int32 srcType, Boolean bUseTrustedConnection, String packageName, String packagePassword, String packageID, String packageVersionGUID)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.LoadPackage()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task

but the method signature specifies String PackageGuid, and it is a string..

any ideas??

i tried casting the variable like so:

(DT_GUID) @[User:TSPkgId], as the versionid is a uniqueidentifier on sysdtspackages.. It didn't like that at all (can't cast from type DT_WSTR to DT_GUID error code 0xC00470C2)

View 8 Replies View Related

SQL 2012 :: Passing Record Counts From Execute Task In SSIS To Package Variables

Mar 4, 2014

I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.

The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.

I've tried returning the values as:

Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'

I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).

View 2 Replies View Related

Package Is Not Able To Create Process To Execute Child Package.

Jul 3, 2007

Hi,
In my application, i have two package, parent package and child package. the parent package is executing child package using a Execute Package Task.
"Execute Out Of Process" property of Execute Package Task is set to TRUE. means the child package will be run in separate process not in the process of Parent package.
this was working fine, but at a particular client location. its failing the error is "not able to load child package".
for me it seems some setting on server restricting to create separate process for child package execution.
when "Execute Out Of Process" property of Execute Package Task is set to FALSE. its working fine.

can anyone help what could cause its failure with property set to TRUE.


Appreciate any help.

View 2 Replies View Related

Parent / Child Package - Connections, Variables Etc.,

Aug 13, 2007

In respect to Parent / Child packages, can some one correct me if I'm wrong.

Even if connection managers are created in Parent package, the same needs to be created in child packages if they need to connect to database. On the other hand I can just create connection strings in variable in parent package(instead of connection managers itself) and use parent package variable configuration to configure the connection string of child package with the variable value.

Sorry If I'm confusing


The same with variables, the parent variable needs to be mapped to a child variable(using parent package variable config) to be used in child package, it cannot be used as it is.

Thanks

View 3 Replies View Related

Problem Setting Package Variables In SSIS Package

Sep 8, 2006

Hi,

I am making use of the DtUtil tool to deploy my package to SQL Server.
Following is my configuration:
32-bit machine and 32-bit named instance of Yukon.

I have some package variables which need to be set in the code.

Previously I did it as follows:

Set the package variables in the code. For example:

pkgFile.Variables["User::DestinationServerName"].Value = <myvalue>

Deploy the package as follows:

applnObj.SaveToSqlServer(pkgFile, null,
destinationServer, null, null);

Here the package was successfully deployed and when i open those packages using BIDS, I am able to see that the variables are set to the values as doen in teh code.


Because of oen problem I am not using SaveToSQLServer method. So I switched to DTUtil tool.
Now I am doing it this way:

Set the package variables as before.
Deploy the package to SQL Server using DTUtil tool.

Now is the problem:
The package is successfully deployed. But the variables are not set to the value that I have specified in the code.

I also tried DTexec utility to set the package variable. Even that does n't work.
Can anyone help me out? Is there any alternate method to set package variables?

Thanks,
Sandhya

View 8 Replies View Related

Execute Child Package In 32 Bit Within A 64 Bit Master Package

Dec 4, 2007

Is it possible to call a package through the 'Execute package task' and force it to run in 32 bit while the master package runs in 64 bit?

View 1 Replies View Related

Setting Global Variables In A Script Task, HOW?

Jan 3, 2007

I'm playing (and trying to learn)...

I have an FTP task in a for each containter and am setting the RemotePath using an expression (works great). Thought I could use this to start learning some of the scripting funtionality in SSIS (in a script task) so found some code in this forum (thanks Original Posters!) and tried my hand at some coding... Intent was to create a variable and then dynamically overwrite the Expression in the FTP Task from the script (I know I don't need to do this, I just wanted to use it for learning purposes)....

I have a variable named varFTPDestPathFileName (string) and want to set it to the value of varFTPDestPath (string) + varFTPFileName (string). Note: all variables are scoped at the package level (could this be the problem?). I did not assign any of the variables to ReadOnly or ReadWrite on the Script Task Editor page (seems to me that doing this in the code is a whole lot cleaner [and self documenting] than on the Task Editor page)...

I keep getting the following error:
"The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."

Here is the script:

Public Sub Main()
Dim vars As Variables
' Lock for Read/Write the variables we are going to use
Dts.VariableDispenser.LockForRead("User::varFTPDestPath")
Dts.VariableDispenser.LockForRead("User::varFTPFileName")
Dts.VariableDispenser.LockForWrite("User::varSourcePathFileName")
Dts.VariableDispenser.GetVariables(vars)

' Set Value of varSourcePathFileName <<--- ERROR OCCURS HERE
vars("User::varSourcePathFileName").Value = _
Dts.Variables("User::varFTPDestPath").Value.ToString + _
Dts.Variables("User::varFTPFileName").Value.ToString

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

I would also like to be able to loop through the Dts.VariableDispensor to see the contents of the variables and their values.

Somthing like

For each ??? in vars
msgbox(???.Value)
Next

One other question... Do we always have to preface the variable with "User::" or "System::", if so can you explain why?

Any help would be much appreciated....

View 17 Replies View Related

Dynamically Execute Child Packages Using A Script Task

May 27, 2008

I've been executing a package and passing a parent variable to the child using package configurations but I'd now like to do this using a script task. The script task would then programmatically load the package and execute it.



How do I do this and still use the parent variable?



I've found examples of how to load a package but I haven't been able to find out how to I load it specifying the parent variables.



I think it's possible. MSDN shows the available methods but the example is for the base method.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.execute.aspx



Cheers,

Ben

View 1 Replies View Related

Setting Connection String For The Package To Execute From Another Package

Jun 29, 2006

I am using execute pacakge task to execute another package . I am giving the Connection string for the package to execute. It works fine in my development machine but when i try to run in another server after i deployed it. It looks for the datasource path of the DTSX file in the same location.

how do i set the path according to each server where the dtsx file is stored. or any other method of storing it like connection string.

if i store it in theparent package variable where should i point to...

thanks

aa

View 1 Replies View Related

How Can I Use The Variables In SQL Execute SQL Task?

Aug 24, 2006

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @[User::varOutTable] from @[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?



thanks!!

View 14 Replies View Related

Understanding Package Configurations And Setting Variables

Feb 26, 2008

I am trying to understand the relationship of setting package configurations and setting variable values during job scheduling. I understand that I can select variables that I want to manipulate at run time using package configurations. I understand that the configuration file is an xml file that the job can be told to access at run time. Here are my questions:

1. Once I create a configuration file, do I physically modify the file to change the variable that is input at runtime?
2. Do I have to select the config file and then change the value using the Set values tab?
3. What is the relationship between the config file and the set values tab?

4. When creating a package configuration, when would you use the options other than XML configuration file?

View 8 Replies View Related

Execute SQL Task - Passing Variables

Aug 28, 2007

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

View 9 Replies View Related

Execute SQL Task To Update Variables

Jun 30, 2006

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





View 5 Replies View Related

Problem With Variables In Execute SQL Task

May 2, 2007



I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.



when I run the package i get the following error:



Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" 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 execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".



543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.



Please help i have very little hair left!!

View 16 Replies View Related

Execute SQL Task Editor - Variables

Jun 11, 2007

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.

View 5 Replies View Related

Using Execute SQL Task Expression With Variables

Jan 15, 2008

I have a package with variables which are set to default values, in this instance table names.

I then use a script task to change these variable values, these change fine and I have looked in the watch window whilst debugging to ensure that the values have changed correctly, and they have.

My Script Task then pipes to a Execute SQL Task, where I am using Expressions to set the SqlStatementSource. The Expressions statement uses the variables I change in my script task to form a SQL query.

However, at run-time the Expression appears to be fixed with the default variable values, it does not change even though the variable values have changed. Below is an extract from my expression for the SqlStatementSource.

i.e




Code Block
"USE [DBNAme]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].["+ @[User::StagingEventLossTable] +"](
[PolicyId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
<snip>
...







Is an Expression value fixed from the moment the package is run and is therefore not dynamic during run-time?

If this is the case, how can I get around this? As I want to be able to change this SQL Statement on the fly at run-time.

Thanks

View 8 Replies View Related

Assigning Variables Via Execute SQL Task

Apr 15, 2008

Somebody provided me with this link http://www.sqlis.com/58.aspx that helped me understand Execute SQL Task and how to assign values to variables from a resulting SQL query.

But I'm not sure if this tutorial addresses my particular example.

Here is what I'm trying to do.

I have a a simple table called VarTable with 2 columns. Col1 is called VarName, and Col2 is called VarVal. As you may have guessed from the column names, I want to hold variable and value pairs in this table.

Right now, I have 2 entries as follows.

Var1, Hello
Var2, Bye

I want to assign Hello and Bye into two variables in my package.

In using the above tutorial, I figured out how to assign one variable by doing the following SQL and using "Single row" Result Set property.

Select VarVal from VarTable
Where VarName = 'Var1'

Then in Result Set section of the Execute Task Editor I map VarVal with one of my user variables.

How do I take care of the 2nd variable in the same Execute SQL Task, or do I need to have another Execute SQL Task? Based on my understanding, in order for me assign multiple variables in one Execute SQL Task, I need somehow construct a SQL statement to return as many columns as I have variables to assign, but this seems hard to do given the variable table structure I have.

Please let me know if my example is unclear.

View 13 Replies View Related

Trouble With Execute SQL Task And Variables

Mar 2, 2007

I have 3 Execute SQL tasks to find the max value of some fields. In two tasks the SQL

SELECT MAX(field bigint)
FROM TABLE

Is returned and stored as a string variable. My 3rd execute SQL task is

SELECT MAX(field int)
FROM TABLE

Is returned and stored as an object type. If I try to save it as a string, the execution fails with a mismatch.

WHY? Why aren't they all strings? What am I missing/doing wrong? This is very frustrating behavior. The queries are running against tables on SQL Server 2005 the field types are listed in the SQL above.

Thanks,
Matt

View 7 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.


FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

Loading Multiple Variables From Within The Same Execute Sql Task

Aug 28, 2007

Hi All,

I was wondering if it is possible to assign values to multiple variables from within the same execute sql task, ie I want to use only one execute sql task and have multiple T-SQL statements within it and then assign the results of these sql statemenst as values to multiple variable.

Typically I would declare variables var1 , var2 and var3 , then can I just add one execute sql task and have mutiple sql statements within it? something like this

select max(id) from table1

select max(id) from table2

select max(id) from table3

Thanks

View 3 Replies View Related

Send Mail Task In Child Package

Oct 27, 2006

Hi everyone,

In my project i need to call a child package to send email to a group of users. All the configurations for sendemail task such as to, from, subject, attachment, and body are set using global variables. I pass all those variables from parent package and this is workin fine. Now my problem is the BODY MESSAGE. I need to have the body message which looks like the one below....
Status of PCCODS database on (PRODSQL6):
TimeStamp on database: 10/25/2006 6:51:32 PM
FACILITY OPEN/CLOSE STATUS
--------------------------
-- Number of facilities open for month 10 is 80

(See attached PCCCLoseStatus excel file for details on closed facilities.)
Here u need to look at Timestamp, some values like '10', '80' , 'PRODSQL6' are all passed through a different global variables. This is my problem!
I already have a workin project that works fine if i pass just one line of body message. Now follows the issues
1) I cannot enter more than one line in the variable value column(if it's one line then it will work fine!!!!)
2) I need to get some values from parentpackage global variables displayed in the email.
3) I need to have a body message that looks like above one.
I really doubt whether i could implement these by just passing all these in one global variable or i should have a script task, i donno..... may be someone can help me do this!!! How can i have this kindof body message implemented in SSIS?
Suggestions are greatly appreciated....

View 2 Replies View Related

Programmatically Setting Package Variables In Job Step Command Line

Mar 1, 2007

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \Package.Variables[User::period].Value;"" + periodEndDate + """;

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

View 3 Replies View Related

SQL Server 2008 :: SSIS - Using Variables In Execute Task?

Mar 3, 2015

I have built the following query in SSMS, when I add it to an Execute SQL Task in SSIS. I get this error -

"[Execute SQL Task] Error: Executing the query "SELECT @columnz = COALESCE(@columnz + ',[' + times..." failed with the following error:

"Must declare the scalar variable "@columnz".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Query:

use design

drop table tmpNCPCNCDownstreamMaxUtilization3wks
select node, max(utilization) as max_Utilization, DATE
into tmpNCPCNCDownstreamMaxUtilization3wks
from stage_ncpcncdownstream_temporal
WHERE Date BETWEEN DATEADD(day, -20, GETDATE()) AND GETDATE()

[code]....

View 1 Replies View Related

Passing Variables As Arguments To Execute Process Task?

Nov 9, 2007

I am running my "execute process task" in a foreach loop that uses two variables (file paths) from the recordset that feeds into it.

My executable line looks like this: C:Program FilesWinZipWZZIP.EXE ,
which runs the command line Winzip executable and my argument line looks like this: -rp @ZipArch @ZipTxt.

When I replace the variables with literal strings, the process works (only once, of course).

My Argument won't parse in the expression editor, nor does the process run when embedded in the foreach loop. What am I doing wrong? Thanks for your help.


View 12 Replies View Related

Execute DTS 2000 Package Task. Mischievous Task??

Sep 21, 2006



Hi everyone,

For first time I'm testing this task and surprisingly, when I try "Edit Package" option:


1)The DTS host failed to load or save the package properly
2)The selected package cannot be opened
3)Error HRESULT E_FAIL has been returned from a call to a COM component

But after these messages you can see all the tasks but they haven't name!!

It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.

I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ?¿

Any guidance or idea about this?

View 5 Replies View Related

Passing A Variable Value (Package Scope) To A DTS Package Embedded Into Execute DTS 2000 Task

Jul 19, 2007

Hi friends,

I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?



Thanks

Subhash Subramanyam

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved