BufferTempStoragePath - Global Setting?
Jul 13, 2006
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
View 4 Replies
ADVERTISEMENT
Jan 8, 2008
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
View 6 Replies
View Related
Oct 25, 1999
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
View 1 Replies
View Related
Jan 3, 2007
I'm playing (and trying to learn)...
I have an FTP task in a for each containter and am setting the RemotePath using an expression (works great). Thought I could use this to start learning some of the scripting funtionality in SSIS (in a script task) so found some code in this forum (thanks Original Posters!) and tried my hand at some coding... Intent was to create a variable and then dynamically overwrite the Expression in the FTP Task from the script (I know I don't need to do this, I just wanted to use it for learning purposes)....
I have a variable named varFTPDestPathFileName (string) and want to set it to the value of varFTPDestPath (string) + varFTPFileName (string). Note: all variables are scoped at the package level (could this be the problem?). I did not assign any of the variables to ReadOnly or ReadWrite on the Script Task Editor page (seems to me that doing this in the code is a whole lot cleaner [and self documenting] than on the Task Editor page)...
I keep getting the following error:
"The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."
Here is the script:
Public Sub Main()
Dim vars As Variables
' Lock for Read/Write the variables we are going to use
Dts.VariableDispenser.LockForRead("User::varFTPDestPath")
Dts.VariableDispenser.LockForRead("User::varFTPFileName")
Dts.VariableDispenser.LockForWrite("User::varSourcePathFileName")
Dts.VariableDispenser.GetVariables(vars)
' Set Value of varSourcePathFileName <<--- ERROR OCCURS HERE
vars("User::varSourcePathFileName").Value = _
Dts.Variables("User::varFTPDestPath").Value.ToString + _
Dts.Variables("User::varFTPFileName").Value.ToString
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
I would also like to be able to loop through the Dts.VariableDispensor to see the contents of the variables and their values.
Somthing like
For each ??? in vars
msgbox(???.Value)
Next
One other question... Do we always have to preface the variable with "User::" or "System::", if so can you explain why?
Any help would be much appreciated....
View 17 Replies
View Related
May 27, 2008
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
View 1 Replies
View Related
Jul 31, 2007
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
View 1 Replies
View Related
Dec 11, 2006
Hello, I have one package that seems to have continuous problems with memory. As of right now it loads a little over 1 million records. I tried leveraging the property, BufferTempStoragePath, but I don't seem to have the right path name. What sort of path do you put there? File? Folder? If it is a file, what sort of file should it be... text, dat, xml? If someone could point me in the right direction it would be greatly appreciated. Thanks.
PS: Below are the error messages I am getting:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
[DTS.Pipeline] Error: 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.
View 3 Replies
View Related
Feb 26, 2006
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
View 1 Replies
View Related
Apr 23, 2007
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 Related
Feb 11, 2008
I 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.
View 1 Replies
View Related
Nov 10, 2004
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
View 3 Replies
View Related
Apr 19, 2001
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
View 2 Replies
View Related
Aug 6, 2001
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
View 1 Replies
View Related
Feb 20, 2003
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.
View 3 Replies
View Related
May 10, 2004
cani define a global time in sql server2000, which all tables in DIFFERENT databases can use. I need this in order to INNER JOIN all tables in SAME/DIFFERENT DBs on basis of this datetime field.
View 7 Replies
View Related
May 27, 1999
Hi all,
I have a user using this tool, which creates a temporrary storeproc in tempdb.
when I don't give him permission on temp_db, it errors out and says you should create a system variable to point to a different database to create this sp.
Can anyone kindly explain to me how could this be done?
I appreciate any comment on this thread.
Thanks in advance.
Jay
View 1 Replies
View Related
Aug 22, 2006
Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.
View 6 Replies
View Related
Feb 17, 2004
Hi,
Is there a way to declare a persistent global variable in SQL Server?
I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.
For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:
IF @MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')
What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.
Any other suggestions are also welcomed.
Thanks,
Skip.
View 3 Replies
View Related
Jan 31, 2007
If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname
View 3 Replies
View Related
Jul 23, 2005
Using SQL2000, I have a DTS that takes data from MySQL to sqlserver, thecatch is I want to specify a specific range of dates.How to use a global variable? At the moment I manually changes the dates andjobs run on a daily basis.sample sql statement from Mysql connection:select *from Table1where date between '1/1/2004' and '6/30/2004'CHANGE TO:select *from Table1where date between @fromdate and @todateTIABob
View 1 Replies
View Related
Jul 20, 2005
Hi all, I need to update some data in a table, based on some criteria.In this case we are talking about the stamping of a price against a job.The update table holds the jobs, and the update_details table holds theactivities performed on each job and the cost for each activity. If ipull back this information using the following codeselect t1.reference,t1.update_id, t2.*from update t1, update_details t2where left(t1.reference,2) in ('EA','ND','SD','ST')and t1.update_id = t2.update_idI get something likeEA 1883 Act1 4.20EA 1883 Act2 3.00EA 1883 Act3 7.50EA 2444 Act1 4.20SD 5433 Act1 5.60I need to update the cost for everything pulled back using the abovesql, to a price determined in another table (activities)the activities table would look something likeActivity_Code Cost_London Cost_RocAct1 5.60 4.20Act2 4.00 3.00Act3 6.20 5.60in a nutshell i need to update the cost in the update details fromCost_roc to Cost_london for all activities for all jobs in the updatetable that have a referance starting with specific letters. The newprices need to be obtained from the activities table.Would be very gratefull for any help on this matterRegards,Ian Selby*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Nov 1, 2007
I have several stored procedures and UDFs that have code that convert between GMT timezone and other U.S. timezones.
for example,
to convert from GMT to CT I use this:
@dtmCentral = dateadd(hour, -5,@GMTdtm)
However, come Nov 4th after 2AM, when DST ends, I need to change this to
@dtmCentral = dateadd(hour, -6,@GMTdtm)
Assuming that I can store this -5 or -6 value in a global variable, I want to have a stored procedure that updates it when DST starts and ends.
Is there a better way of doing this other than the option of going through the entire calculations of figuring out whether it's DST or not each time ?
Thanks.
View 6 Replies
View Related
Feb 5, 2007
hey there
Reporting services - page footer
I have got this off the ssw.com.au
Execution Time
="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)
and it is supposed to show like this
Execution time: 1 minute, 10 seconds
but all I get is
Execution Time: 0 Seconds on all reports
Can someone tell me what is wrong please?
jewel
View 3 Replies
View Related
Jan 12, 2007
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function?
Here's what I did...
Dim p_s_syounin2 As New SqlCommand
Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23")
'''' connection string is not placed here acc. to my research
End If
End Sub
Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand
If e.CommandName = "Approve" Then
cnn.Open()
p_s_syounin2.ExecuteNonQuery()
cnn.Close()
End If
End Sub
I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. "
Please help me.
Thanks guys.
Audrey
View 1 Replies
View Related
Dec 17, 2007
Hi,
Can we create our own system global variable using @@ in Sql server 2000?
If yes, can you please post in the sql code with it?
Alos, is the system global variables are kwywords in Sql or they can be updated?
Thanks in Advance!!!
Shobana
View 1 Replies
View Related
Apr 1, 2004
Is there a way in SQL Server to setup Global Server Variables or Constants
We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.
It would be great to be able to set a global variable and use it anytime that code is needed.
Any ideas.
View 2 Replies
View Related
May 12, 2004
A sample connection string is something like this....
"Server=sql.company.com;Database=myDatabase;Trusted_Connection=True;"
My question is, since the actual .com must go into the string, if you change host companies,
you will have to go through all pages and also change the connection string.
I know there must be a way to declare the string once in like the global.asax and then
refer each page to that string.
I've been hunting around for a sample on how to do this.
Anyone know a good link to refer to?
Thanks,
Zath
View 2 Replies
View Related
Dec 7, 2000
A short explanation: I have 4 locations that will import from text files.
These text files will need to be 'stamped' with a location_id. I'd like to pass the location_id to the dts package via a VB program. The reason for the VB program is I'd like to keep users from the Enterprise Manager. Is this possible to accomplish? Thanks for your help.
View 1 Replies
View Related
Feb 23, 2001
I am executing:
--------------------------------
DECLARE @x AS int
SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
PRINT @x
--------------------------------
@x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:
SET @lcQuery = 'DECLARE @x AS int ' +
'UPDATE ... WHERE .... ' +
'SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
How can I pass the variable @x to the progam from within EXEC (@lcQuery)?
How does EXEC (@lcQuery) execute? In a different space?
Thank you.
View 1 Replies
View Related
Mar 24, 2003
How to set and reference Global Variable in a SQL Task (MS SQL 2000)
:)
View 1 Replies
View Related
Feb 24, 2005
Hi:
A regular permanent table is not an option:
Need to exec a procA which at the end also exec procB.
procA will insert to table A and also generate a intermidate result set to a ##A global table, it works fine at this point. However, when it exec the procB at the end of exec procA, the ##A global table is empty when entering procB.
The key is I want to pass a records set to the procB without using a tableTempB. Does a ## global table should be still alive until procB execution is done?
I also tried use table variable, but it does not look like to accept @tableA
as part of the procB's parameters.
Also tried function, but it could not support a #tempTable within it which will do a dynamic query insertion.
thanks
David
View 4 Replies
View Related
Aug 11, 1998
Hi everyone:
I am creating an sp, in which I check for the existence of a global temp table (using the exists)
statement. If the Exists returns a false, I move on to processing without the temp table. If it
returns a true, I utilize the temp table to do some inserts. I create the temp table when my
application first starts up. The problem that I am facing is that the check for the temp table`s
existence seems to be failing. Is there any other way to check for the existence of a global
temp table??
Any info really appreciated
Thanks
Nisha
View 1 Replies
View Related
Jan 22, 2002
As a newcomer to SQL2000, I need to create a DTS Global Variable that will import a file daily automatically. I've got the DTS working great, but I have to manually execute it every day. The log file name Accounting 02-22-2002.csv changes by the date every day. So the next record set would be Accounting 02-23-2002.csv and so on. Any ideas on how to create this? I think some VBScript is necessary from what I understand. I'm looking at the function DATEADD(d, 1, date).
View 1 Replies
View Related
