Setting BufferTempStoragePath From Package Variable
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 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!
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)
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.
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
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...
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:
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?
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
Hi, I have this query basically im trying to set the @db to the current database depending on the quarter. This will be used in a package to change the database that the package will use.
DECLARE @db nVarchar(4000) SET @db = N'TESTDB' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1') THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7') THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END EXECUTE sp_executesql @db, N'@level tinyint', @level = 35
I get the error Incorrect Syntax near 'TESTDB04_Q3'.
I am trying to set the variables value through Execute SQL task and create directory through the FileSystemTask by setting the user variable as source. The User variable has the Directory path to create the directory, but when i set the source as the user variable it complains that its value is empty.
Execute SQL TAsk happens to work correctly when i check the watch window the user variable is set correctly . but value is not setting in the SSIS/Variable window Value.
What is wrong an what method should i use to set the variables value to create directory.
Hi, folks. As part of a larger bunch of code, I am trying to validate some user form input against a database...I've been picking apart code samples and have the following (currently not yet working) code to show for it. I find tons of complex examples out there RE the data controls, and I have those working. But I can't find any examples of just pulling a single record from a DB, sticking it in a variable, and then checking against that variable to get a boolean response. Anyway, here is the code. I am trying to check that a password field filled by the user matches the value located in the DB. I am not married to this code; if someone suggests a better way to do it rather than using a custom validator control, that's fine too:BEGIN CODE SNIPvoid Page_Load(object sender, EventArgs e) { Page.Validate(); SqlDataSource1.SelectCommand = "SELECT Password FROM aspnet_Membership WHERE aspnet_Membership.Email='" + emailID.Text + "'"; }
private void CustomValidator1_ServerValidate(object source, System.Web.UI.WebControls.ServerValidateEventArgs args) { //between this and the next comment is where I have to get the DB Password field value for the current record SqlCommand command = new SqlCommand("SELECT Password FROM aspnet_Membership WHERE aspnet_Membership.Email='" + emailID.Text + "'"); command.CommandType = CommandType.StoredProcedure; sqlConnection.Open(); reader=command.ExecuteReader(); string currentPasswordDb = reader.IsDBNull(reader.GetString("Password"))? null: reader["Password"].ToString(); args.IsValid = false; // Assume False { // Compare db entry against user's entry if (currentPasswordDb == currentPassword.Text) { args.IsValid = true; } } } END CODE SNIP The code above give me the following at runtime:Line 14: SqlCommand command = new SqlCommand("SELECT Password FROM aspnet_Membership WHERE aspnet_Membership.Email='" + emailID.Text + "'");Line 15: command.CommandType = CommandType.StoredProcedure;Line 16: sqlConnection.Open();Line 17: reader=command.ExecuteReader();Line 18: string currentPasswordDb = reader.IsDBNull(reader.GetString("Password"))? null: reader["Password"].ToString();Although I'm sure it won't be the last. I'm a former ASP classic developer, moving to c# and .Net. Any ideas?
I want to execute a BAT file using Execute Process task, where I want to select the file path (directory) dynamically using a variable whose value is set at runtime.
In simple terms I want to send a value to the "Executable" property dynamically
The For Loop will execute a stored procedure that passes a variable each time it loops. I need to set this variable, @FiscalWeek, equal to the variable @Counter. So the first time it loops, the counter will be 1, and the Fiscal Year would be set to 1. The next time it would be two, and so forth. Can I do this in the Expressions section of the For Loop? If so, what would the property be?
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
I have created a SSIS package with a Foreach Loop including a Data Flow Task, which in turn include a Row Count component which pass the row count value to variable with package scope. The variable is used in an Execute SQL Task following the Data Flow Task.
The package executes successfully when executed on its own, but when executed as a child from a parent package (which only include an Execute Package Task) the variable from the Foreach Loop becomes NULL.
There are a lot of other variables in the package receiving values dynamically without any problem, the row count variable however is the only variable in the package that receives a value as part of a Data Flow (and used in following tasks within the Foreach Loop).
Why does the variable become Null? For your information, I am using a variable with package scope and no variables from the parent package are used or passed from the child package to the parent package.
(For your information, we are running the 64 bit version)
I want to be able to have a single select statment:
SELECT TOP 1 Call.JobNum, Call.CallID, Call.Company, Call.LastCallTime FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10) ORDER BY Call.CallID
but with this select statment I also want to set a variable:
declare @variable int
SELECT TOP 1 Call.JobNum, @variable = Call.CallID, Call.Company, Call.LastCallTime FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10) ORDER BY Call.CallID
Now SQL Server does not like this, can not set a variable in a multiple select statment. I NEED to do this all in one step if possible. Any suggestions?
declare @ret varchar(50) DECLARE @X SQL_VARIANT set @X=10 SET @ret=select SQL_VARIANT_PROPERTY(@X,'BaseType')
Just trying to assign the SQL_VARIANT_PROPERTY return value to @ret and it issues an error: Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.
But I don't want to convert I just want to assign the result to the variable.
what's confusding is that you can do this: if(select SQL_VARIANT_PROPERTY(@X,'BaseType'))='int'
I am having the wrost trouble with this today for some dumb reason... Please don't suggest any alternates; this is just a quick example, full code is more elaberate.
Hi,I am having problems setting the value of a variable in a SQL Stringthat I have to create dynamically in my procedure. The code that Icurrently have is as follows:set @sqlStatement='Set @compare_string=' + '(Select ' +@group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' whereIdentity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'exec(@sqlStatement)The error message that I get is as follows:Must declare the variable '@compare_string'.Here @compare_string has already been declared in the procedure and Idon't have a problem using the variable anywhere else but this SQLStatement (when called using the EXEC function).I am not sure why SQL Server can't see the variable declared when usedin a string in conjunction with EXEC. Is this a syntax issue? Any helpon this issue would be greatly appreciated!Thanks in advance.
im using a Foreach ADO Enumerator in my SSIS Package, which iterate on a DataSet from a SQL Task.
I use the "Variable Mappings" on my Foreach Loop to retrieve the values into User-variables. But what i need is a way to combine a value from the DataSet with a User-variable and assign this new value to a new User-variable.
We have one main package from which 7 other child packages are called. We are using ParentPackage variables to assign values for variables and database connections.
While the values from ParentPackage variable get assigned to some of the packages properly, to others it doesn€™t assign the value.
For example: Except for one of the packages the database connection string gets assigned properly to all other packages.
Similarly, in another package one of the variables doesn€™t get assigned. In other packages it is assigned properly.
We have checked all the other property values and they are exactly the same.
We cannot make any head or tail of this erratic behavior.
In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack.
This is the current structure:
SET @MktNbr = 10
WHILE @MktNbr < 90
BEGIN
DECLARE@sqlstmt varchar(1000)
SET @Market = '0' + CONVERT(char(2),@MktNbr)
SET @sqlstmt = 'SELECT (columns) INTO dbo.table' + @Market + ' FROM #table WHERE marketcode = ''' + @Market + ''' IF @MktNbr = 50 BEGIN SET @MktNbr = 51 END ELSE IF @MktNbr = 51 BEGIN SET @MktNbr = 52 END ELSE IF @MktNbr = 52 BEGIN SET @MktNbr = 55 END ELSE IF @MktNbr = 55 BEGIN SET @MktNbr = 60 END ELSE BEGIN SET @MktNbr = @MktNbr + 10 END EXEC (@sqlstmt)
END
I'm probably having a blonde moment, but I'm trying to replace the if statements with this:
SET @MktNbr = CASE WHEN @MktNbr = 10 THEN 20 WHEN @MktNbr = 20 THEN 30 WHEN @MktNbr = 30 THEN 40 WHEN @MktNbr = 40 THEN 50 WHEN @MktNbr = 50 THEN 51 WHEN @MktNbr = 51 THEN 52 WHEN @MktNbr = 52 THEN 55 WHEN @MktNbr = 55 THEN 60 WHEN @MktNbr = 60 THEN 70 WHEN @MktNbr = 70 THEN 80 WHEN @MktNbr = 80 THEN 81 ELSE @MktNbr END
Clearly it's wrong because the proc bombs every time with a duplicate table error.
It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?
I would like to set up a subscription that has two date parameters, I would like the end_date to be today and the start_date to be (today - 1 Month). The interface does not seem to support expressions?
I saw some documentation that said to use defaults in the report but that does not help because I may want multiple subscriptions with different params like 1 person may want (today - 2 months) as the start date ...
Hello There: I am running a data flow within a ForEach loop wherein I am computing a value called QuotaGap. When it is 0 I do not want any further execution of the loop. I am using a Conditional Transform within this dataflow that writes a record to a table only when the QuotaGap is NOT 0. However, I am unable to terminate the execution of the loop as I am still within the dataflow.
Now, the computation of the gap requires a value from another variable called NetPurchases. I tried using an ExecuteSQL task in the control flow but could not figure out how to pass the value of the variable NetPurchases into the select statement to compute the gap. For example, the select statement would read:
select (QuotaUpperLimit - ?) As QuotaGap from <<tablename>>
I tried setting the parameter as an input as well as an output and it did not work.
Then I tried passing the entire SQL as a string within a variable. This does not work either because in order to compute the math QuotaUpperLimit - NetPurchases, both variables need to be integers but then you cannot concatenate integres together, which is what we need to do to create the SQL.
The other reason I am going through these hoops I guess is that I have not figured out a way to set the value of a variable within a data flow. I compute the value for QuotaGap within the dataflow in a ForEach loop but I have no way to pass this result to a variable called QuotaGap without using an ExecuteSQL task or another ForEach Loop.
I have spent hours on this simple issue and so have given up and looking to the good friends in this forum for help.
If what I have stated is not clear please let me know and I will try to clarify things a bit.
Is it possible to set a query result (scalar) to scalar variable. I would like to set a qery result (SELECT COUNT(*) FROM MyTable) to a scalar variable:
DECLARE @temp int
SET @temp = query result...
Is it possible? I couldn't find the way to do that...
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
I need to do something like this in SSIS:From one SQL table I need to get some id values, I am using a simple sql query:Select ID from Identifier where value is not null.I've got this result:As a final result I need to generate and set a variable in SSIS with the final value:
@var = '198','120','ACP','120','PQU'
Which I need to use later in a odbc expression.How can I do this in SSIS?
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.
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.
