Creating a SQL Agent job to run a package works just fine. On the Step Properties, Set values tab, I can add variables and the values I want for them, no problem. The job runs just fine. If I want to go edit the values however, they're always gone.
Is there some security setting that hides the variables and their values which is on by default?
Hey all,
I have a stored procedure, which need one variable as parameter. I am trying to call this stored procedure from my DTS Task and my parameter is defined as the Global Variable in DTS. here is the SP call within my DTS Task
declare @id int
select @id = DTSGlobalVariables('ClientId' ).value
exec sp_Update_DayPart @ClientId= @id
it gives me an error that DTSGlobalVariables function not defined. In this case how can i pass the value of Client Id which is my global variable to my SP.
Thanks in Advance
Hi.
I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?
Any ideas would be appreciated. Thanks!
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
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
I am modifying a report that sets this variable at the beginning of every dataset:
declare @current_minute int
set @current_minute = DATEPART(N, GETDATE()) % 3
My idea is to set this variable in the Report Properties instead. When I do set this variable it in a Text Box or a Dataset, I get errors such as:
The expression used for the calculated field '=Variables!report_var.Value' includes a variable reference. Variable values cannot be used in calculated field expressions.
The Variable(report_var) expression for the report ‘body’ contains an error: [BC30451] Name 'N' is not declared.
What other strategies can I use to set a variable globally, instead of at the beginning of each dataset?
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
Is it possible to change the database name defined in the destinatation table definition of the transformation object programmitacally?
We have different database names between dev and prod. The package was created against dev. I'm trying to copy the package to the prod server and programmatically change the database name where necessary.
Has anybody used the "Use fast load" or "Table lock" options in the advanced DTS package properties ? These are supposed to be in the Advanced tab of the Data Transformation Properties dialog box. I cannot seen to find them. I want to set them in order to have the load execute in nonlogged mode. Any help would be very much appreciated.
Thanks in advance.
Hi
We have a list of 40-50 SSIS packages and could grow up to 100. Each one loads 5-20 tables. I’d like to write a program (probably another package utilizing "Script task/component") to:
1- List all the packages by name
2- List input and output tables and/or file systems for each package
3- List child packages and procedures that are referenced in each package
So I need to have access to the Package properties.
I would like to hear your idea/clue/tips and or any helpfull direction.
Thanks so much.
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.
I want to input a parameter to my package. I have a variable, and I tried changing its value from DTExecUI.
I did everything as mentioned in http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx but I still get an error -
Could not set "Package.Variables[User::var_input_step_Name].Properties[Value]" value to "RAW".
The variable was originally read from a XML config file. I stored the package to MSDB. I then ran dtexecui, selected my msdb package, and in Set Values, I entered property path and value. I also tried removing the "", and I even deleted the XML configuration. Still the same error.
What am I doing wrong? Any Package Settings to make?
TIA
Kar
I'm currently trying to convert over packages from SQL 2000 to SQL 2005. The biggest obstacle at the start has to do with converting my "Dynamic Properties" control. I use it to read an .ini file and load the user name and password to my connections. With 2000 it's nice and easy 1 file 3 lines. While trying to convert it I€™ve had nothing but problems. I've tried the Registry Entry but it forces everything into the Current User and I can't use that I need local machine. There is no documentation and I can't have a xml file with 1000 different username and password settings because 1 person has to update that file with the new passwords. Any help would be GREATLY appreciated.
Thank You
Jerry
I am attempting to run an SSIS package from a web service. Right now both the service and package are on my local machine which is running XP. I have accessed the web service from a client application in debug mode. I am not sure if it is actually running under aspnet_wp.exe because it is XP and a development environment? (separate question)??? The package fails with a series of OnError messages similar to:
The result of the expression ""/c DEL /F /Q "" + @DeployFolder + "\catalog.diff.lz""" on property "Arguments" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
An initial supposition is that the permissions of the web service are inadequate for the package. I have the authentication as "Windows" and <identity impersonate="true" /> in the Web.Config file. When I break in the debugger the Environment.UserName and Environment.UserDomainName are mine and I am an Admin on the box.
the authorization is 'deny users="?".
The article that describes basic implementation of this in a Web Service states:
With its default settings for authentication and authorization, a Web
service generally does not have sufficient permissions to access SQL
Server or the file system to load and execute packages. You may have to
assign appropriate permissions to the Web service by configuring its
authentication and authorization settings in the web.config
file and assigning database and file system permissions as appropriate.
A complete discussion of Web, database, and file system permissions is
beyond the scope of this topic.
And how!
Note that the load is fine and that this is a run time error and that the package runs correctly when run manually from SQL Server using the 'run package' menu item in the Object Explorer tree of the SQL Server Management Console.
I need to know if this is an ASP.NET issue per se or XP or if this is even a security issue. And how to solve it! This is critical path so an expeditious reply with a solution would be greatly appreciated.
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.
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.
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.
How to get the value of the variables when i debugging a package?
Thanks!
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
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
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
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
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
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.
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
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
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
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
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
Hello,I'm using BIDS to create a package, and I need to pass in a variable value; how does this new DTS process work, in deploying the package and passing in a custom value at runtime?Thanks.
View 2 Replies View RelatedHi,
what I basically want to do is to delete all records from all tables where the field loadnumber equals a certain value.
To do this I've defined the global variable 'DSLoadNumber' (integer) in a DTS package, and I want to use the stored procedure sp_msforeachtable in an Execute SQL Task.
This is the code in the Execute SQL Task:
sp_msforeachtable
@replacechar = '~',
@command1 = "delete from ~ where loadnumber = ?",
@whereand = " and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')"
This code can be parsed without any problem, but the ? is not seen as a variable.
When I get the ? out of the quotes the code cannot be parsed any longer.
Finally I've written a cursor to the job which looks like this:
DECLARE @tablename varchar(35),
@loadnumber int
SELECT @loadnumber = ?
DECLARE delete_cursor CURSOR FOR
select name
from sysobjects
where type = 'U'
and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')
OPEN delete_cursor
FETCH delete_cursor INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
exec('delete from ' + @tablename + ' where loadnumber = ' + @loadnumber)
FETCH NEXT FROM delete_cursor INTO @tablename
END
CLOSE delete_cursor
DEALLOCATE delete_cursor
But this code doesn't compile either...
Any suggestions on how to get the code working within DTS?
Stef
Is there any way to configure a SSIS package variable using a SQL Statement?
The "Package Configurations" does not accept SQL statement.
I have about 5 package variables that need to be initialized using queries.
I don't want to end up writing 5 execute sql tasks to initialize them.
In DTS we could do that at one place using Dynamic Properties Task.
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