How can I set a global var in another DTS package from inside the one I'm excuting. An example would be that I have in package #1 an ID that gets assigned, and it executes package #2 which needs the ID from the previous package.
Thanks,
Todd
By default the BufferTempStoragePath is mapped to the user running the package's Documents and Settings folder. This is problematic when numerous packages are running simultaneously and using this disk location (i.e. sorts), and you don't have a large disk for your C: drive.
The property of course can be changed. However the property is specific to a data flow task, so this would require developers to change the property is every data flow task of every package. Is there a global setting to change the default location that SSIS will use?
An alternative is to use configurations, however a configuration will be required for every data flow, as it is specific to the data flow task (and name of that data flow task)
Any ideas?
Thanks
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....
Hi,
I am trying to create an SSIS package but am not able to set the global variable values.
I want to have a Global Variable as @EventID
and the create a Execute SQL Task which will run this query:
SELECT Max(EventID)
FROM EventTable
and assign this Max value to the global variable @EventID
How can I achieve this...help me please
Regards,
Nusrath
Hi pals,
I am new to SSIS.I am getting a problem while setting the global parameters.
I created a package with a single task i.e an Execute SQL task.The associated sql is
"delete from src_emp where deptno=?" and i added a new user defined variable as "dno" i.e "user:no"
which of String type with scope=<package name>.The name of my package is "test.dtsx" which is a Structured File
save in my d: directory. I harded code the value in SSIS package desiner i.e in BIDS and tested the functionality,
it is working fine. But when i remove the hard coded value and when i tried to call the dts package using the command line
utility DTExec, i am getting. Below are the different ways i tried to set the global parameters.
trial 1:
---------
D:>dtexec /f test.dtsx /SET "Package.Variables[User:no].Properties[value]";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:06:51 AM
Warning: 2007-07-31 11:06:53.00
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "Package.Variables[User:no].Properties[value]".
This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:no].Properties[value] value to 20.
Started: 11:06:51 AM
Finished: 11:06:53 AM
Elapsed: 1.094 seconds
trial 2:
---------
D:>dtexec /f test.dtsx /SET "Package.Variables[User:no].value";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:08:04 AM
Warning: 2007-07-31 11:08:05.99
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "Package.Variables[User:no].value". This occurs
when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:no].value value to 20.
Started: 11:08:04 AM
Finished: 11:08:05 AM
Elapsed: 1.078 seconds
trial 3:
---------
D:>dtexec /f test.dtsx /SET "PackageExecute SQLTask.Variables[User:no].value";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:09:56 AM
Warning: 2007-07-31 11:09:57.58
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "PackageExecute SQLTask.Variables[User:no].val
ue". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set PackageExecute SQLTask.Variables[User:no].value value to 20.
Started: 11:09:56 AM
Finished: 11:09:57 AM
Elapsed: 1.062 seconds
Any suggestions would be appreciated.
Thanks and Regards,
Manu
Hi,
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
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
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
Can i call global package variables which are defined in ssis packages , in MS SQL Query or stored procedure in Managment Studio.
please advise
Are global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?
I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.
So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?
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 RelatedI am new to SQL server 2005 and have a config question:
I am controlling database connection info using XML indirect config - no problems there.
Essentially I am going to have a number of packages that need to use a common file path, that might change from one server to the next, e.g. Server 1: C:sourceFiles versus Server 2: D:sourceFiles. Within this directory the filenames will remain static. So in the flat file connection manager I'd like to use a variable to reflect the folder - but I don't want to have to create this for each package.
So, I thought I would create a system environment variable and create expressions for the connection managers - something like %SOURCE_DIR% + "file.csv" - but this does not evaluate correctly.
So then i though I could use the SQL server configurations table with a configurationFilter SOURCE_DIR and appropriate configuration value - but then how do I access this in the flat file connection manager to create a dynamic file name?
So essentially I want a variable/property available globally to all my packages and potential flat file connection managers that help me to centrally control file path locations.
Any help would be most appreciated.
Is this possible. How can one do it? Thanks for any info you have.
View 2 Replies View Related
I've used t-sql code like this to dynamically create jobs that perform specific tsql commands, including calling stored procedures
However, I'd like to create a dynamic job that runs a step that's running an SSIS package.
EXEC msdb..sp_add_job @SQLjobname
EXEC msdb..sp_add_jobserver @job_name =@SQLjobname , @server_name = @jobserver
EXEC msdb..sp_add_jobstep @job_name = @SQLjobname, @step_name = 'CPM.command'
EXEC msdb..sp_update_jobstep @job_name = @SQLjobname, @step_id = 1, @database_name = @db,@command = @commandin
I was wondering, is it possible to dynamically (in code) rather than setting the command that a job step is performing, to instruct the job step to perform a type Package? I noticed the command line setting for the step looks like this when it's a package:
/SQL "mypackage" /SERVER AMMIA01DEV04 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
However, if change the step type, the step fails. I don't see a type option on sp_update_jobstep and wonder is that subsystem?
As another option, I could instruct the job step to run the package through cmd shell but then wonder if that will run asynchronosly and if errors will be retained in the job. I'm not favoring that option.
Hi,
If this is the syntax to set a property in a package using dtexec:
/set PackageMyForEachLoopMyDataFlow.Disable;False
What is the syntax for setting a global variable in the package?
Thanks
I'm in the process of creating a series of packages to do the ETL for a datawarehouse. As such, I've got quite a few DataFlow tasks scattered through them.
The problem I'm coming across is where the disk that the temp files (created during the processing) are put on is short of space (when compared to some of the data sets I'm copying around and noting that it's the System disk, not the "Data Drive").
To get around this, I found number of article scattered around that suggested using the BufferTempStoragePath property of the DataFlow tasks to redirect the temp files to somewhere else. This works fine for the tasks that I hard-code the new directory.
Noting the number of these tasks that I want to redirect, and also that the Dev environment will be different to the Production environment (and I have no control over the drive letters, paths, etc, in Prod), it was suggested that a package variable be created, assigned to the property and then said variable could be exposed via the Package Configuration file.
That's great as far as it goes, but I just cannot, for some reason, make it work.
The xml in the config file is:
<Configuration ConfiguredType="Property" Path="Package.Variables[::BufferTempStoragePathValue].Properties[Value]" ValueType="String">
<ConfiguredValue>D:Temp</ConfiguredValue>
</Configuration>
The property in the DataFlow task is then set to: @BufferTempStoragePathValue.
When the package is executed, it logs the error message "The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission."
My question for the group is two-fold: 1) what am I doing wrong in the setup of the property/variable, and 2) what are the security permissions that are required for the (new) folder.
It's got to be something obvious, I just can't see what!
Many thanks for your assistance.
Cheers,
Mike
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?
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
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
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
I just spent the past 3 or 4 hours trying to figure out how i could represent carriage returns and line feeds properly in package configuration variables that an SSIS package can properly handle, so i figured I'd post my results in case anyone else has struggled with this.
Initially, I thought I would be able to just represent the delimiter as the same value that you can pick from in the flat file connection manager dialog, i.e. {CR}{LF}, {CR}, {LF}, etc. nope.
Then I decided to try actually storing the characters as hex, i.e. _x000D__x000A_ for {CR}{LF}. nope.
After scanning the .dtsx packages for a while, I finally found something that works: you have to represent the curly brackets in hex, and you can simply use the CR or LF string literals. so if you wanted to have a row delimiter that was {CR}{LF}, you would need to set your variable to: _x007B_CR_x007D__x007B_LF_x007D_
Hope this saves some people the pain I had to endure.
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,
I have an SSIS package created on SQL Server 2005. I have moved this to a SQL Server 2008 R2 server and amended the package on this new server to point at the correct databases.
The package runs manually. However, I cannot see the package when trying to schedule a job. The dropdown list does not contain the package.
I imported the package by right clicking on MSDB and selecting the package from the file system. The package then appears under this folder (SQL Server Integration Services). I then create the SQL job but cannot see the package I just created.
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)
I have a pretty simple SSIS package that fast loads a 100 million record table into a SQL Server 2008 table on a daily basis. This normally runs fine and completes in about 1 hour. As this is perhaps one of our largest running SSIS packages, about once every 2-3 weeks this SSIS will fail/drop connection. Once it fails, the large number of records will start rolling back. This rollback process can take 1+ hours so I cannot even restart the failed SSIS package immediately. This is a problem.
I am looking for a solution or option so I do not have to wait on that rollback to restart this particular, long running SSIS package. Is there an option/setting to leave the partial data set committed and not rollback? Then I could just restart the SSIS package immediately or set it the SSIS to auto-restart 1 time on failure. The first step in the SSIS does a truncate of the destination table.
I ran into a variety of problems trying to set a script task breakpoint in a package containing multiple script tasks. The debugger apparently treats the breakpoint as if it were set in ALL tasks in the package, not just the one in which it is actually set.
At best this results in hitting breakpoints in scripts where they are not set and at worst the debugger brings up the "Send error report" dialog and quits (while the package continues to run). The latter seems to happen most often when the script with the breakpoint has more lines than an earlier script and the breakpoint is set at a line number that exceeds the number of lines in the earlier script--it bombs when the earlier, shorter script starts.
To get the debugger to work under these circumstances I had to add some nonsense code like
While False
Dim i as Integer = 0
End While
to every script, at the same line number near the beginning of the script (line 40, for example). I then set a breakpoint on the middle statement in one of the scripts (doesn't matter which) to cause the debugger to open at runtime. It doesn't hit the breakpoint because the line is never executed. If the breakpoint is set on a line that can be executed in any script in the package, bad things tend to happen.
I then add a "stop" statement to the script that I want to debug. This only works if the debugger is already open, hence the dummy breakpoint above.
This workaround is usable, but I am debugging a package that has quite a few scripts and having to insert dummy code in all of them at a fixed line number is rather inconvenient. I would really like to see breakpoints work the way one would expect--only in the scripts where they are set.
Is there some other, easier way around this problem? Is there at least an easier way to get the debugger to open so that "stop" will work?
Is there a way to write a SP for all database.
e.g. I see that I can use sp_databases SP in all databases.
How do I write my own SP which will be available in all databases?
The SP that I have is in a particular database, I cannot use it in other databases unless I manually write it in that too. Can I avoid this?
Thanks
Hi All,I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an errorThe 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. I've seen some examples online to get global varaibles in task script and all of them display the same code Any idea Franck
View 1 Replies View RelatedI would like to create a global DB connection. I am going to be using this string throughout my program and want to be able to call the string that references my connection.
Thanks in advance.
Hello,
In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.
I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?
Brian
How to create my own global variable and set its value. In another word, i want to set the value of a variable in one sp and want another sp to see its value.
thanks
Michael
Have anyone encountered a problem with DTS Global variables. What I am trying to do is keep a counter in a global variable. It works fine when I run the job manually but if I schedule the job it doesn't increment the counter. Any ideas?
Thanks in advance!
Nancy
Is there a way to declare a global variable that can be used in seperate stored procedures?
I have a statistics table that tracks the inserts and updates for several stored procedures.
I need to have the procedure_A run and insert a count of inserted and updated rows.
Procedure_B needs to identify the row inserted by procedure_A and update the columns with a count of how many it inserted and updated.
Procedure_C needs to do the same as procedure_b.
I want to pass a global variable with the id of the row inserted by procedure_A to the rest of the procedures so that they will know which row to update. How do I do this? Can I?
Thanks.