Passing Variables From One DTS Package To Another DTS Package

May 22, 2002

I want to loop through a recordset of email addresses, and for each value in the recordset, I want to perform a set of task which includes creating an Excel spreadsheet and emailing it through to those email addresses.

I have 2 DTS package.

Package 1 - This contains an ActiveXScript task. The purpose of this package is to do the looping through of the recordset, and passing the email address variable to package 2.

Package 2 - Receives the email address from package 1. Package 2 contains the creating Excel spreadsheet and emailing it through to the recipients function.

I am having difficulty passing the variable from package 1 to package 2.

The code I have to date for the ActiveXScript task in package 1 is:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim oPackage
Dim oStep
Dim oTask
Dim oCustTask
Dim oGlobal

'Initialise package
Set oPackage = CreateObject("DTS.Package2")

Set oStep = oPackage.Steps.New
oStep.Name = "TestStep"
Set oTask = oPackage.Tasks.New ("DTSExecutePackageTask")

Set oCustTask = oTask.CustomTask
oCustTask.Name = "TestCustTask"
oStep.TaskName = oCustTask.Name
oPackage.Steps.Add oStep
oCustTask.FileName = "C:TempVeraTest1.dts"
oPackage.Tasks.Add oTask

set oGlobal = oPackage.GlobalVariables.New("CurrentAirlineCode")
oGlobal.value = "SIA"
oPackage.GlobalVariables.Add oGlobal

'Execute package
oPackage.Execute

Main = DTSTaskExecResult_Success

End Function

This does not work. Can anyone please help?

Thanks very much in advance.

View 1 Replies


ADVERTISEMENT

Passing Variables To A Package From A Website

Feb 28, 2008

I'm developing an SSIS package that will theoretically be run in two different ways. One is as a nightly job, called by a SQL job. The second is from a web application. When run as a job, it will be looking for a flat file in a known location. Run from the web, the file name and location are determined at runtime. I've tried to handle this by using a variable to hold the flatfile connection string, and setting the variable value to the known location. Then, when called to the web, I determine the file location/name at runtime, and pass it to the package. I'd assumed that if I pass the value into the package, it would override the variable's value that I'd set, but this doesn't seem to be happening. I realize this could very well be due to a coding error, but I thought I'd check to see if this is even a viable approach to be taking. If a variable's value is set in the package itself, will it be overwritten by a value passed to the package (assuming no stupid mistakes)? Or is this entirely the wrong approach to be taking for what I'm trying to do?

View 6 Replies View Related

Passing Variables To A Package Before Execution

Mar 13, 2006

I would like to pass variables to the package before it is executed (e.g. I am calling the bcp utility and I need to pass a password to the command line), so that at runtime a variable is set and then used.

Does anyone has some hints for good approaches? Every idea is welcome.

FYI: I do not use the bulk insert task as I need an errorlog file and the command is buggy with the errorfile option, therefore I chose the bcp approach. My connections are dynamic as far as servername and userid is concerned, but I did not find a solution for the password issue.

View 1 Replies View Related

Passing NULL-value Into Package Variables

Dec 20, 2006

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

View 3 Replies View Related

Problem With Package.Execute Passing Variables

Dec 20, 2006

I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions.

When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error.

Any ideas?



---- C# ----

Reference to Microsoft.SQLServer.ManagedDTS

using Microsoft.SqlServer.Dts.Runtime;

Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\SSISPackages\Package.dtsx", null);
DTSPack.Variables.Add("Variable1", true, "", var1.ToString());
DTSPack.Variables.Add("Variable2", true, "", var2);
DTSPack.Variables.Add("Variable3", true, "", 100);
DTSPack.Variables.Add("Variable4", true, "", var4.ToString());
DTSExecResult pkgResult = DTSPack.Execute(null, DTSPack.Variables, null, null, null);

View 16 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

Passing Values For Variables To An SSIS Package From C#/ ASP.NET Page.

May 30, 2007

I've got an SSIS package that works fine. It does extracts from a foreign ODBC source and moves it to SQL Server. It has 3 variables, a customer ID, a fromDate and a toDate. Those variables are used to complete a SQL statement expression. So far so good.



I now want to provide a web interface in a web page that will enable the user to provide values for those variables. How can I execute my package passing the user's input?



Thanks for any advice.



Lou

View 3 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

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

Passing Execute DTS Package Result (success/failure) To Calling SSIS Package

Mar 6, 2008

I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.

As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.

Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?

If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?

View 5 Replies View Related

Passing The Xml Configuration File To The Package As An Input Parameter While Executing The Package

Feb 2, 2007

Hi,

I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.

So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.

Is this possible?

Thanks for your help.

$wapnil

View 6 Replies View Related

Passing Value From A Child Package To The Parent Package That Calls It In Ssis

May 21, 2007

hi,



I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.



I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.



I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...





Any suggestions?



Thanks for any help in advance..



smathew

View 8 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

Parent Package Variables And SQL Package Store

Aug 23, 2007

Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

View 13 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

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Variables In DTS Package

Dec 8, 2000

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

View 6 Replies View Related

Package Variables

Feb 12, 2007

Couldn't quite find the answer I was looking for via a forum search.

I have 9 packages that are currently called by a master package - all child packages take a datetime variable from the master package which is subsequently used in the child processes.

The question I have is that if I run the master package manually from Visual Studio I can set the master package variable manually to a date of my choosing and run it, which works fine. However we will be wishing to automate the package execution at some point and want to know the best way to run the package(s) on an automated basis and have the data variable supplied to the master package.

What would be the best way to do this ?

Presently we have a variable called MasterPackage_vLoaddate which is a DateTime data type.

Any help appreciated.

View 16 Replies View Related

Passing Parameters To A DTS Package

Mar 10, 2005

Hello All,

I am executing a DTS package from an asp page using the following code. I would like to also pass DTS Global variables along. i assume this is possible but can't seem to find an example.


Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "HOFDBMCRM4","TraubGar","ripley",DTSSQLStgFlag_Default,"","","","DSC_CalculateBOS"
oPkg.Execute()


Thanks, Gary

View 1 Replies View Related

Passing Values To Package

Apr 28, 2008

Well is there a way to pass the hardcoded values in a package externally
like for example if in a file system task i have specified the location for moving my files after processing, this will be a harcoded value how can i pass it externally so i have not to go into the package if in case i have modify it to some other location.

View 9 Replies View Related

How To Get The Value Of The Variables When I Debugging A Package?

Oct 12, 2007



How to get the value of the variables when i debugging a package?

Thanks!

View 3 Replies View Related

Copying Variables From Another Package

Aug 28, 2006

HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?



Thank you,

Ccote

View 3 Replies View Related

Persisting Package Variables

Apr 7, 2006

I was able to write successfully a Script Task to set the values of several package variables. But when the execution completes, the variables still contain values which were specified by default.

What I want is a method of persisting the values assigned to a variable through a script. I believe this was possible in DTS

View 1 Replies View Related

SSIS Package &&amp; Variables

Aug 15, 2006

I have a SSIS package that was migrated from DTS 2000. It had a variable that I passed to the DTS package when executing. Now when I try executing the SSIS package with the variable, I get the following error: The package path referenced an object that cannot be found: "package.variables[user::SnapShotDate].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

From everything that I can find, I am passing the variable correctly. If I look at the package within BIDS, my variable is listed under the variable window & it is scoped for the package. Any ideas on what I might be missing here? This is my first SSIS package that contains a variable.

Let me know if you need more info.

Thanks!

John

View 3 Replies View Related

Variables / Package Execution

Jul 17, 2006



I have 10 or so packages with package scope variables. I would like to create a package that not only runs all the packages but also sets the variables within the packages being executed.

A) Whats the best way to run all the packages from one (package??)?

B) Whats the best way to set the variables in the child packages, without having to change them manually everytime I run it.

Thanks,

Mardo

View 3 Replies View Related

How To Run Package With Variables Using Dtexec?

Jan 30, 2006

Hi,



I am running my package in this way:



exec xp_cmdshell 'dtexec /SQL "SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareaaab.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '



I got errors:



Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

View 4 Replies View Related

Variables Vs. Package Configuration

May 4, 2006

Hi,

We can set the connection parameters using Variables as well as thru Package Configuration. My question is, if we use both to pass value for a connection string, which one will take precedence or in other words, which value will be used?

Advance thanks for your help.

View 1 Replies View Related

Package Variables Not Available At Runtime

Feb 15, 2007

Hello,
I have three package variables that I need to have access to at runtime. All three variables have package scope. The first two, 'StartDate' and 'EndDate' are DateTime variables. The third is called FilePath and is a String variable.

I have taken the following steps;

1. Enabled package configurations
2. Set up all three variables as Parent Package Variables, and have targeted the 'Value' property for each.
3. In the properties of the solution, I have set AllowConfigurationChanges to True.
4. After the package was built, I ran the Package Installation Wizard from the Manifest.

I have done both File System and SQL Server installations. When I go to run the package, none of the three package variables are available for modification.

What am I doing wrong?

Thank you for your help!

cdun2

View 15 Replies View Related

Could Not Set Package.variables[_VarProdValue].value

Apr 29, 2008

Hi,

I am executing one of my package through cmd line using DTEXEC and also passing parameter.

The CMD line is :

"E:Program FilesMicrosoft SQL Server (x86)90DTSBinndtexec"
/FILE "C:UnitTestingLGASProd-InFeed.dtsx"
/CONFIGFILE "C:UnitTestingLGASDataFeedConfig.xml"
/MAXCONCURRENT " -1 "
/CHECKPOINTING OFF
/SET package.variables[_VarProdValue].value;0
/REPORTING E

when i try to run the job i am getting the error i putted below...............

Started: 9:37:21 PM
DTExec: Could not set package.variables[_VarProdValue].value value to 0.
Started: 9:37:21 PM
Finished: 9:37:22 PM
Elapsed: 1.188 seconds



Please tell me any one what is wrong in that CMD line.


Thanks
Thiru Senthil

View 4 Replies View Related

How To Initialize Package Variables?

May 2, 2007

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.



thanks

View 3 Replies View Related

Variables In SSIS Package

Jan 22, 2008



I have defined an active X task (converting from SQL 2000)


Function Main()


DTSGLOBALVARIABLES("TEST").Value= InputBox("TEST : ","Message Box")

Main = DTSTaskExecResult_Success

End Function


The problem is that this variable is not recognized by the subsequent tasks although I have defined a variable "TEST" in the variable lists.

Is there something else to do to get it recognized ?

Thanks for helping,

Pierre

View 8 Replies View Related

Passing Parameters To An Ssis Package?

May 31, 2007

 When someone executes my ssis package, I want the user to be prompted to fill in a few parameters. How do i do that?

View 4 Replies View Related

Passing Login Info To DTS Package

Feb 14, 2003

I'm trying to use DTS to copy data from an Accounting system to a SQL table. A login is required by the proprietary ODBC driver to the database-which includes company, user name, password. Is there code that I can put in the DTS package to pass this to the database so it can run automatically?

Thanks.

View 6 Replies View Related







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