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.
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.
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?
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.
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
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 )
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.
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.
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.
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.
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.
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.
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 ' '
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.
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)
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???
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]
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.
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
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
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?
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.
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)
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.
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
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
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
