For Loop Expression
Aug 24, 2006
I'm trying to run a data flow task inside of a for loop container.
First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.
So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.
I've set the following:
AssignExpression : @counter = @counter + @inc
EvalExpression : @counter < @max
InitExpression : @counter = 1
To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.
What is the trick? Once I get this, then I can set these variables to other variables.
Thanks in advance.
-Lori
View 6 Replies
ADVERTISEMENT
Apr 30, 2007
Hello all,
I have a question concerning properties, variables, expressions, and their interactions within a for each loop. I'm still hammering out some details with the SSIS package I have been posting about recently, and I came across this bit of what may or may not be odd behavior (and it may only be odd because it's not doing what I expect).
I have a number of execute sql and script tasks inside a for each container. I am using one of the script tasks to flip a couple of variables on and off that I thought I could use to disable or enable later tasks during run time. What seems to be happening is that when I set disable on those tasks to true, I can no longer run them again in later iterations of the loop.
In other words: I can loop through it three times, with disable set to false via an expression - then the fourth iteration evaluates to true and disables the appropriate tasks, and then any later iterations no longer run any of the disabled tasks whether the expression evaluates true or false. Once disabled, they stay disabled, regardless of expression.
Is this a bug, or is there a "proper" way to do this? Am I foolish to assume that a "dynamic" run-time property is actually changeable with each iteration of a loop?
View 5 Replies
View Related
Jun 20, 2007
I have a Foreach Loop Container task. I am using it to loop through some files in a folder. I tried to use an expression to set the folder to a variable called DataDirectory. I did not see a folder expression.
How can I set the folder at runtime?
View 1 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Mar 3, 2006
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
View 1 Replies
View Related
Jul 8, 2006
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
View 18 Replies
View Related
Feb 23, 2006
I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...
My Lookp table is like the following...
Srn srctable desttable
1 SRC1 DEST1
2 SRC2 DEST2
3 SRC3 DEST3
Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....
How do I run the pacakge for each of the rows... ..............................
View 1 Replies
View Related
May 27, 2008
well I have this code. I connect to database and submit data.
now i dont know which expression to use to validate if data is submited redirect to next level else show some error.
protected void Button1_Click1(object sender, EventArgs e) { SqlConnection con = new SqlConnection(@"somecode");
SqlCommand comm = new SqlCommand(); comm.Connection = con; comm.CommandText = "insertuser"; comm.CommandType = CommandType.StoredProcedure;some code... comm.Connection.Open(); comm.ExecuteNonQuery(); con.Close(); }
i dont wanna validate the fields as i can do it using field validator i wanna check if data is inserted in database.
View 3 Replies
View Related
May 28, 2008
Hi
When I try and create this procedure, I get the error below
CREATE PROCEDURE GetUserSearchTypesCount
-- OUTPUT parameter to hold the count.
@UserID int,
@ReturnVal int OUTPUT
AS
SET @ReturnVal = (SELECT TOP 100 percent searchType, COUNT(searchtype) AS SearchCount
FROM ttracksearchresults
WHERE userid=@UserID
GROUP BY searchtype
ORDER BY searchtype)
GO
Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
View 3 Replies
View Related
Feb 7, 2007
I need a statement like:UPDATE tblQuestions SET QuestionNumber = QuestionNumber +1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
(Required output is clear from statement itself)
I tried with
UPDATE tblQuestions SET QuestionNumber = EVAL(QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
and
UPDATE tblQuestions SET QuestionNumber = (QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
I've just started working with SQL. Please suggest correct approach.
View 3 Replies
View Related
Feb 1, 2008
I have the following code in the expression builder for a ExecuteSQL Task in the Control Flow.
"INSERT dbo.StgFileList SELECT ' " + @[User::MyFile] + " ' "
When I evaluate it, it renders as INSERT Staging.StgXMLFileList SELECT ' '
What am I doing wrong in the expression builder?
View 5 Replies
View Related
Feb 20, 2008
Is it possible to nest expressions within a script in a similar fashion to how they're nested within the expression editor?
I have a requirement to strip trailing digits from a string field, so that only two decimal places are left.
e.g.
1.12345 becomes 1.12
12345.6789 becomes 12345.67
I would do this in the expression editor but I need to use Script component because there are a multitude of conditions that need to be evaluated first which I will do inside a Select Case statement.
I'm wrestling with code that looks like
Newratefield = ratefield.Substring(1, Findstring(ratefield, ".", 1) + 2)
but VB doesn't like the 'Findstring(ratefield, ".", 1) + 2)' part.
Any suggestions on what I can do here?
View 3 Replies
View Related
Jan 9, 2007
Hi,
In RunningValue expression, can we able to put the DataSet name.
Thanks
View 17 Replies
View Related
Jul 27, 2007
Hi,
In ssis I would like to map a column from table a to a column in table b
Currently this is how I am doing it using sql.
I believe I have to use a derived column but not sure how to implement if statement inside it.
Thanks
case
when len([Column 5]) = 0 then ''
when lower([Column 5]) = 'unknown' then ''
when isdate([Column 5]) = 1 then substring(ltrim([Column 5]), 1, 4) + substring(ltrim([Column 5]), 6, 2) + substring(ltrim([Column 5]), 9, 2)
else
null
end,
View 4 Replies
View Related
Dec 20, 2006
The Value expression for the textbox €˜Name€™ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set....what does this mean...this an error that I have when i try and build my report i get this error for some reason...how do i fix this???
View 4 Replies
View Related
Sep 28, 2007
Hi,
I need to replace a field that might contain a certain character with another should the value occur.
To explain
If i get a hash # in the field, then I need to replace the whole field with a 0 since its a numeric field
I'm just a bit of a noob still with expressions
Regards
View 3 Replies
View Related
Aug 17, 2007
Hi All,
SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))
i write this expression inside the group footer row, it give a #error when generating report.
please help
Cheers
Nick
View 5 Replies
View Related
Aug 20, 2007
I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]
IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
View 24 Replies
View Related
Jan 12, 2008
Dear All.
Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
Thanks.
Zahyea.
View 3 Replies
View Related
Mar 3, 2008
Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId If @ProdSize = 'XL' BEGIN UPDATE _ProductBatches SET XL = '0' WHERE ProductId = @ProdId END DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here. Thank you, Alec
View 7 Replies
View Related
Mar 4, 2008
Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN execute code with item......... erase itemEND In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry? Thank you, Alec
View 2 Replies
View Related
Sep 21, 2000
hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.
Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?
Help!!!!!!!!!!!!1
View 1 Replies
View Related
Jan 26, 2004
HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;
The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.
can I write like this?
for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}
I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.
tks
View 14 Replies
View Related
Apr 11, 2008
I get the following results on a view.
Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 2 | A1102
99-999 | 4 | AB839
What I would like is the following.
Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 1 | A1102
06-324 | 1 | A1102
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
View 2 Replies
View Related
Apr 18, 2008
I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.
AV
Set NoCount on
Declare @Amt as bigint
Declare @Cont as bigint
Declare @Mark as Bigint
Declare @Total as bigint
Declare @BudgetNodeID as Bigint
Declare @GTotal as bigint
Set @BudgetNodeID ='120'
Set @Amt = 0
set @Cont = 0
set @Mark = 0
set @GTotal = 0
While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin
select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Mark = @Cont * (xBudgetNodeCosts.Markup/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
-- compute the sell
select @Total = @Amt + @Cont + @Mark
-- add to grand total
Select @GTotal = Sum(@Total+ @GTotal)
select @GTOtal
end
View 5 Replies
View Related
Jun 17, 2008
Hi
I want to loop this command with a pause. It must run every 30 min to refresh my query. Can anyone assist me.
select * from BULKPICKXLOC
Thanks
View 4 Replies
View Related
Apr 11, 2006
Exist a funtion that I can use in a SP that do something like the for to next or Do while Loop do?
View 5 Replies
View Related
Jan 30, 2007
I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.
View 19 Replies
View Related
Mar 13, 2007
hello,
i have this SP to tally up my inventory tables.. im finding a way to loop through my table tblitemdetail to get necessary parameter to be insert into my other SP (SP_StkAdj_tbl_alignmt) that should accept this params (from the itemdetail) :-
@ItemID ='',
@ClientID='',
@CustomLotNo ='',
@UDF1=NULL,
@UDF2=NULL,
@UDF3 =NULL,
@UDF4 =NULL,
@UDF5=NULL,
@UDF6 =NULL,
@UDF7 =NULL,
@UDF8 =NULL,
@UDF9 =NULL,
@UDF10 =NULL,
@StockID ='0950-4388',
@RecvOwn ='OWN',
@ConsignorID ='JAB1MY'
EG:i will GROUP BY my itemdetail so it will give me the x records of data with :-
SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID
with the result then, i need to insert the param into the SP:SP_StkAdj_tbl_alignmt so that it perform the calculation.
so i guess this will need some looping from the result set i get from the group by and some Sp calling from main Sp
~~~Focus on problem, not solution~~~
View 20 Replies
View Related
Apr 18, 2007
hi all, ive no idea what's wrong with my while loop nested in IF .. it only work correctly when i remove the while from IF :-
IF @Picktype='FI'
BEGIN
-- Insert data into @Stage to play around
DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))
WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
ELSE
select * from #DataList
END
correct result after i remove the while from inside IF
DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))
WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC
IF @pickType='FI'
BEGIn
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
end
~~~Focus on problem, not solution~~~
View 9 Replies
View Related
