Concurrently Run The Same Package With Different Variables Values
Feb 5, 2008
Hello,
I am looking into a new project and I assume that I can end-up in a situation where the same package could be called at the same time (or close enough) by different jobs passing different values to the package's variables.
Is it going to work? Will each instance of the package run in a separate thread?
I would think so because I already have a package similar to this.
The different is that the new package will run for a long time as opposed to the older one which runs for a second or so only.
Just wanted to make sure and gather your feedback.
Thanks
View 3 Replies
ADVERTISEMENT
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
Jun 1, 2006
Hi
Does anybody know how to pass values from asp dot net to SSIS package variables ?
Currently I have an SSIS package for monitoring windows service... for that...
I have to pass the Server-IP Addrress, UserName, Password, Service Name as Parameter.
I would like to pass these parameters through an Interface from RUN TIME.
Please help this problem
Regards
Deepu M.I
View 1 Replies
View Related
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
Feb 2, 2007
Hi,
My scenario:
I am using a FTP Connection Manager and the configuration setting for it is being set in the package configuration xml file. So the xml file contains the Ftpserver, FTp server username and password. The package is picking up the values from the xml file and is executing successfully. I have to do this because I was not able to provide an expression to the Connection Manager Server Password property.
Now, I want to pick up the ftp details from a database table and set it in the xml file during runtime. Is this possible? OR something like using the
<Configuration ConfiguredType="Property" Path="Package.Connections[FTP Connection Manager].Properties[ServerPassword]" ValueType="Variable"><ConfiguredValue>@[user::FtpPassword]</ConfiguredValue></Configuration>
Kindly look at the items in bold. Is this possible? Then I can set the value of the variable in the package before the FTP connection manager task is executed.
Thanks for all the help.
$wapnil
View 10 Replies
View Related
Jul 16, 2015
Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.
DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"
Wondering is there a different way in 2012 to pass values to variables dynamically.
View 2 Replies
View Related
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
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
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
Jul 3, 2007
Is there any way to avoid multiple packages to run concurrently on the same SSIS server ?
View 3 Replies
View Related
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
View Related
Jul 20, 2005
is it possible to use a single connection object shared by severaltasks where each task is handled by a thread? these tasks call storedprocedures that return record sets, no editing, update or delete.my objective is that db connection is expensive and each user can onlyhave 1 connection object. each user submits a request to the webserver, and the result web page construction consists of a number ofresult sets obtained from several stored procedure from the samedatabase instance.i could obtained my result sets by making each sp call one at a timebut was thinking whether i could instead break up the tasks, create athread for each task that shares the same db connection objectconcurrently. the connection object will be alive until all tasks arecompleted, that is if the final object ive could be achieved in ashorter time. No connection pooling here.i am wondering over questions like:1. within the same db instance, if 2 stored procedures need to accessthe same table, does SQLserver queue-up and service one request at atime.2. would there be any problem using the connection object this way,sharing between multiple theads?3. is it possible that a 'dead-lock' may occur within the DB?of cos this whole idea can be absurd and into the trash if theconnection object doesn't support multi-thread and is queue based inthe first place.pardon me if my SQL server basics is off track. been doing too manyother things.thanks,meng
View 2 Replies
View Related
Dec 7, 2000
Hi Everyone,
I have 2 variables say "@one" and "@two"
Can I populate these 2 variables in just one SQL statement
normally we do this
select @one = one from mytable where three = 3
select @two = two from mytable where three = 3
can I have one statement which can put the values in the 2 variables.
something like
select @one,@two = one,two from mytable where three = 3
(this does not work)
Awaiting a reply
Thanks,
saad.
View 1 Replies
View Related
Feb 4, 2008
Hi
I have one problem in SSIS for passing Variable Values while executing Package. I'm giving in details as below:
I opened Microsoft SQL Server Management Studio
Made Connection to Integration Services
To Execute Package I Right Clicked and Click on Run Package
Then I Clicked on Execute and package was executed successfully.
Problem is that if I try to Set Values then Package through Error
DTExec: Could not set ProcessMode value to M.
Basically I could not understand in which format I should pass the Variables.
What I tried is listed below:
ProcessMode;M
Package.Variables[User:rocessMode].Value;M
Package.Variables[ProcessMode].Value;M
But every time I got errors.
And then I tried from Command Line
DTEXEC /DTS "MSDBLoad_Order" /SERVER SERVERNAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /SET Package.Variables[ProcessMode].Value;M
First time Process run successfully. And it has changed the ProcessMode to M also. But after that it was also not changing the ProcessMode Value to M.
Please help in regarding. I tried a lot from Site examples also, but could not get proper solution.
Thanks in advance
Bhudev
View 12 Replies
View Related
Nov 12, 2007
Hello.
I need to store 2 values that i retrive from a databse into variables so i can check them towards what is typed into the textboxes for my login script iv built. How can i do this?1 string myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
2
3 SqlConnection myConnection = new SqlConnection(myConnectionString);
4 SqlCommand cmd = new SqlCommand("SELECT [username], [password] FROM [company] WHERE (([username] = @username) AND ([password] = @password))");
5 cmd.Parameters.AddWithValue("username", TextBoxUsername.Text);
6 cmd.Parameters.AddWithValue("password", TextBoxPassword.Text);
7
8 myConnection.Open();
9
10 //how do i pass the values retrived into variables, like string usrname and string password?
11
12 myConnection.Close();
View 13 Replies
View Related
Mar 15, 2008
can u give me some idea how to make Sp who having two variables as a parameter having values seperated by ","
now thses vaues have to insert in to two tables tbColor .... colorname,product_id
and tbSize.......sizename,product_id
thanksss
View 11 Replies
View Related
Dec 12, 2001
I thought a stored procedure was taking much too long to complete. So, I moved the main query to Query Analyzer and found that when I substituted actual values for variables that my SELECT statement ran in seconds. Just to test, I created DECLARE statements, set the variables equal to the same values, re-ran the SELECT statement and it took over a minute. Even the Execution Plan was much different. Any suggestions?
View 4 Replies
View Related
Nov 16, 2006
I have variables and values stored in a table in this format
process_id | t_variable | t_value
-----------------------------------------------------
1 | Remote_Log_Server | AUSCPSQL01
...
many such rows
how to assign values to variables in SSIS?
basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)
SELECT
@varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END)
,@varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END)
FROM Ref_Table
WHERE process_id=1
View 3 Replies
View Related
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
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
Nov 2, 2006
Can someone show how to do this?I have a SqlDataSource1, and i have a SELECT * FROM Table1How would i get@ProdName@ProdNumber Into the following local variablesString ProductNameInt ProductNumber I’m using C# and ASP 2.0 VWDThanks for Help1
View 2 Replies
View Related
Aug 21, 2007
From the microsoft technet How to: run a package using a SQL server agent job (http://technet.microsoft.com/en-us/library/ms139805.aspx):
Click the Set Values tab to map properties and variables to values.
Note:
The property path uses this syntax: Package<container name>.<property name>. Depending on the package structure, a container may include other containers, in which case nested containers are separated by a back slash (). For example, PackageMyForeachLoopMySequenceMyExecuteSQLTask.Description.
If this is the syntax for writing the container.property value, how do you write the variables?
View 10 Replies
View Related
Oct 12, 2007
How to get the value of the variables when i debugging a package?
Thanks!
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
