Problem With Setting Variable Values In A Loop

Sep 11, 2007

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?

View 14 Replies


ADVERTISEMENT

Setting A Variable In A For Loop

Jan 30, 2008



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?


InitExpression @Counter =0
EvalExpression @Counter == @CurFiscalWeek
AssignExpression @Counter = @Counter + 1

View 1 Replies View Related

Collecting Values Into A Variable In A Loop

Mar 13, 2006

Dear All,

I’m trying to collect values from a query into a single variable within a loop, like so:

WHILE condition is true
BEGIN

SET @intLoop = @intLoop + 1

@myString = @myString + ‘, ‘ + (SELECT companyName FROM @tblTheseComp WHERE id = @intLoop

END

For some reason though the @myString does not collect up the values, but will equal NULL at the end of the loop.

If however I simple do

WHILE condition is true
BEGIN

SET @intLoop = @intLoop + 1

@myString = (SELECT companyName FROM @tblTheseComp WHERE id = @intLoop
END


Then I get the last value from the query as expected.

Can anyone explain why this might be?

Thanks in advance!

View 7 Replies View Related

Assigning Variable Values Via Loop Using Different Datatypes

Apr 10, 2008

Hi all,

I need some help regarding a conversion in a Script Task.

I am reading my variable values from a database with a sql task, the table has two columns, variable and variableValue.
Looping through the recordset and setting the different variables works well, with two links:
http://blogs.conchango.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx
http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx

setting the variable value only works well if the package variable is defined as string, because the db field is a varchar, trying to assign an integer for example brings up an error.

Therefor I tried something like CType:
Dts.Variables("MyVar").Value = CType(MyRecordsetField,String), where the target datatype should be depending on the variable datatype instead of being assigned as a constant.

Could someone give me a hint to handle this?

Thanks in advice!

Cheers
Markus

View 3 Replies View Related

Setting Variable Values From Within A Data Flow

Feb 15, 2008

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.

Thanks!

View 7 Replies View Related

Setting The Global Variable Values In SSIS

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

Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql

Apr 29, 2007

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">        &nbsp;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" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<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>        &nbsp;        <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>  

View 1 Replies View Related

Bulk Insert In Foreach Loop Not Setting Source ConnectionString

Feb 22, 2008

Hello all,

I am running into an issue that seems to be collaborated here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188886&SiteID=1.

To summarize, I have a Foreach Loop that uses a Foreach File Enumerator. The loop writes the file names to a variable which is used in the Expressions property for a flat file's ConnectionString. The flat file is used as a source destination in a Bulk Insert Task that is inside the Foreach Loop. The flat file's connection string is not picking up the file name, resulting in this error: "The specified connection "test.txt" is either not valid, or points to an invalid object. To continue, specify a valid connection."

It doesn't work for a file connection manager or a flat file connection manager.

It does work if I replace the Bulk Insert Task with a Data Flow (flat file source -> ole db destination). It also works if I set the file's connection string manually in a script task.

It looks to me as though the Bulk Insert isn't calling whatever method in the connection manager that reevaluates the property expression. Am I missing something or is this a bug? I looked at the connect site, but couldn't find this particular scenario.

Thanks for any insight you might provide!
Jessica

View 3 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

SETTING VARIABLE

Aug 27, 2006

sry im an idiotbut how do u take like "SELECTbla FROM blar WHERE blam=blfda" and make the thing it "selects" into a variable thnx 

View 5 Replies View Related

Setting A @db Variable

Jul 6, 2004

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'.

View 3 Replies View Related

Setting Variable Value

Jun 21, 2006

hi !

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.

Thanks,

aaks

View 5 Replies View Related

T-SQL (SS2K8) :: Variable Declaration In A Loop

Jul 2, 2015

I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled.

As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable)

I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2)

--Table var declaration in loop
SET NOCOUNT ON
DECLARE @looper INT = 0
WHILE @looper <= 10
BEGIN
DECLARE @ATable TABLE ( somenumber INT )

[Code] ....

View 4 Replies View Related

Variable Inside A Nested Loop

Jul 20, 2005

I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

MERGING Variable In FOR LOOP COntainer

Mar 23, 2007

Hi All,

Seems like a simple task, but been a struggle.

Simply trying to move a group of files from one folder to another folder and renaming the files with the monthyear in the middle of the filename..

I'm using a FOR Loop container and works find. The added complexity is I'm trying to rename the files the same tine and putting the Month Year into the file name.

I guess the struggle is how to get the file name out so I can manipulate it.

I tried creating variable(V_SOURCE) which stores the file path and create another variable(V_FILENAME) to hold the filename. I believe on the expression page of the FOR LOOP editor if I select the the file name and extension radio button, it should put the file name in the V_FILENAME variable.

In my file task trying to join the two variables together in another variable, keep saying my path is wrong with the file task kicks off.

here's the syntaxt

@[User::V_SourcePath] + @[User::V_FILE_NAME]











View 23 Replies View Related

Loop By Passing Variable Into Query

Nov 2, 2007



Hi all,

I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.

The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.

View 3 Replies View Related

Transact SQL :: Can Pass A Variable Value Out While Loop

Nov 3, 2015

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

[code]....

View 3 Replies View Related

Simple DB ? , Getting A Value And Setting As A Variable.

May 12, 2008

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? 

View 4 Replies View Related

Run Time Variable Setting

Aug 25, 2006

Hi,

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

Can some one help me on this

View 3 Replies View Related

Setting Result To A Variable

Jan 16, 2008



Hi,
below is the sql statements for my web service using C#.




Code Block
string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC"

SqlCommand comm = new SqlCommand(sql, conn);




Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?

View 5 Replies View Related

T-SQL (SS2K8) :: Cursor From Variable - Procedural Loop

May 8, 2014

I am using a cursor (i know - but this is actually something that is a procedural loop).

So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.

Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?

View 6 Replies View Related

How To Use A Variable From The For Loop Container As A Input Parameter To A SP

Nov 6, 2006

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

 

MA

View 1 Replies View Related

Using Array Variable In ForEach Loop Container

Mar 1, 2007

Dear all,

I have a problem using SSIS since I was a beginner, the problem is I have to do some data transform from flat files into database. These flat files come from many branches of my office with the file structure like this D:SSISranch_nmfile_nm.txt, in folder SSIS there are many branch_nm folder and each branch_nm folder contains many flat files.

My idea was grab the branch_nm folder into array variable using Script Task and then loop this array variable using ForEach Loop Container to get the file and using it for Flat File connection, but I don't know the way to do it.

May this idea work out for sure ?? How to use array variable, that we previously defined inside Script Task, in ForEach Loop Container ?

Thanks in advance

View 11 Replies View Related

Programmatically Loop Over Variables In The Variable Dispenser?

Jun 16, 2006

In my custom task, I would like to loop over the variables in the variable dispenser, and only modify those that are of a certain type. Is this possible?

Thanks!

View 8 Replies View Related

Using Variable Of Foreach Loop - File Enumerator

Jun 6, 2006

I have a Foreeach loop container that is looking for all the files in a specified directory. For each file in the directory I need to open the file, extract the contents, and pass the contents as a variable for a stored procedure call.

I was able to loop thru the files in the directory and see each file and store the name to a variable.

I was able to use an XML task to open one file via a file connection that connects to a specified file and store the contents in a variable and then execute the SQL task using the variable.

What I am unable to figure out is how to put the two processes together and get the XML task to open the file by using the variable that contains the filename. If anyone could help I would really appreciate it.

Thanks!

GN

View 3 Replies View Related

Setting The Value Of A Variable In A Select Statment...

Oct 19, 2001

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?

pat

View 1 Replies View Related

Setting SQL_VARIANT_PROPERTY Return To A Variable

Jan 31, 2007

Why does this not work?

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'

So I assume I should b able to do the above.

--PhB

View 1 Replies View Related

Setting Datetime Variable W/ Mm/dd/yyyy

Jul 19, 2007

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.

Today is 07/19/2007
Declare @StartDate DateTime
@StartDate = CONVERT(VARCHAR(10), Month(GetDate()) & "/22/" & Year(GetDate()), 101)

So I want @StartDate = '07/22/2007'

What I need is CDate

View 6 Replies View Related

Problem With Setting A Variable In SQL String

Jul 20, 2005

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.

View 5 Replies View Related

Setting Package Variable Using Dtexec

Feb 15, 2008

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

View 1 Replies View Related

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 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

Setting A Variable In A Foreach ADO Enumerator

Sep 13, 2006

Hi,

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.

Anybody tried something like this?

Regards, Martin

View 2 Replies View Related

Setting User Variable Dynamically

May 24, 2007

Hi,

I have a package that uses a variable string (in date format) to execute a package.

I want to modify that variable in such a way that if no value is set for that variable then use system date else use date in varaiable.



Can someone help me out in this.. I know it has something to do with the expression builder but donn know how to do it.



Cheers

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved