ForEachLoop task not behaving as expected

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - The dreaded subreport could not be shown error I seem to have run across the feared "Error: Subreport could not be shown" problem. I have very carefully checked everything I could find on the issue, but nothing seems to help--and I must admit, I'm at my wits' end here (though it's probably something absurdly simple). I have a main report with one subreport. I am running ASP.NET, with a ReportViewer control on my ASPX page. The main report and subreport are both local reports, and pull data from SQL data sources. Initially, I had the parent report pass a parameter to the subreport, but in the interest of keeping things simple I removed that--even though I verified that my handler for the subreport processing was valid and was obtaining valid values for the parameter. Here's the applicable code from the CodeBehind's class: Code Snippet DataView vw; protected void Page_Load(object sender, EventArgs e) { vw = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.None); rptAllCustomers.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(ProcessSubReports); } protected void ProcessSubReports(object sender, SubreportProcessingEventArgs e) { //vw.RowFilter = "SALES_INDEX = " + e.Parameters["SaleIndex"].Values[0]; e.DataSources.Add(new ReportDataSource("SALESConnectionString", vw)); } And the applicable ASPX code: Code Snippet <rsweb:ReportViewer ID="rptAllCustomers" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="100%"> <!%--<localreport reportpath="App_GlobalResources\SalesByProduct.rdlc"> </localreport>--%> <localreport reportpath="App_GlobalResources\BySale.rdlc"><datasources> <rsweb:ReportDataSource DataSourceId="SqlDataSource1" Name="SalesData_SALES"></rsweb:ReportDataSource> </datasources> </localreport> </rsweb:ReportViewer> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sales %>" SelectCommand="SELECT * FROM SALES WHERE SALE_DELETED = 'false'"> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:sales %>" SelectCommand="SELECT * FROM CUSTOMER_PRODUCTS"> </asp:SqlDataSource> Through debugging and other means, I have found the following: 1. The data being passed through vw in the ProcessSubReports is valid. 2. The subreport runs perfectly when placed into its own ReportViewer 3. The reports are all in the App_GlobalResources folder 4. The main report references a subreport by the name of SalesByProduct 5. App_GlobalResources/SalesByProduct.rdlc is the name of the report file I should be able to provide more information on request. Anyone have any clues Tag: ForEachLoop task not behaving as expected
  - 2
    - Looking for a component This control is not officially published. However, you might want to try playing with Microsoft . DataTransformationServices . Design . Controls. DtsComponentTreeView from Microsoft.DataTransformationServices.Design.dll. I am not sure if you will be able to configure it externally, but there is a chance for it. Certainly, there are no guaranties we will not break your code in the future if you use the undocumented control. Good luck, Bob Tag: ForEachLoop task not behaving as expected
  - 3
    - Can we use Excel file (source) for Bulk Insert Task Can an excel file be used as a source for a Bulk Insert Task in SSIS instead of a delimited flat file Tag: ForEachLoop task not behaving as expected
  - 4
    - Identity Primary Field in Merge Replication Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity. What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e On Server 1 - Values start from 1 then 2,3,4 etc etc Server 2 - 24001, 24002, 24003 etc etc Server 3 - 46001, 46002, 46003 etc etc Server 4 - 68001, 68002, 68003 My question is what happens when these ranges eventually conflict Do they automatically gain a different range such as 142 001, 142 002 etc etc Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app. cheers Tag: ForEachLoop task not behaving as expected
  - 5
    - An internal error occurred. [ ID = 3639 ] on delete query Sounds like your sdf file has become corrupted. Try to run a Compact/Repair on the db. You can also try to send the sdf file to me, and I will have a look at it with SSMS. Tag: ForEachLoop task not behaving as expected
  - 6
    - JULY CTP (WORKSTATION COMPONENTS FAIL INSTALL) OK, good luck with that and sorry it's not easier. My guess if you nuked those things is that the MSI registry isn't cleaned up. Unfortunately I don't know of a tool to clean this up.... Maybe someone else will jump in. -Richard Tag: ForEachLoop task not behaving as expected
  - 7
    - Great New Resource for Books Online Information The SQL User Edutcation Team has been working hard with our teams here to get better/more/good information out to our users. We’ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback: http://search.live.com/macros/sql_server_user_education/booksonline We want to generate as much traffic as possible here so that we can see if this is useful to our users. We’d love to hear back from everyone we can! Tag: ForEachLoop task not behaving as expected
  - 8
    - VERY slow stored procedure performance on datetime parameter A couple of suggestions: Use SQL Profiler to capture the Plans for the different styles of invocation. The Showplan All and Showplan XML events are under the Performance group. This should let you see any difference in the plans between the fast and slow versions. Try rearranging the procedure arguments to see whether it is the datetime parameters or the parameters at position 11 or whatever that are the problem. It may be that there is some strange boundary effect at the point these parameters are in the list. Were there any changes to the procedure or the tables around the time the problem arose Also are you saying that if you have a local variable and assign the parameter value to that and use the variable it is okay , but if you use the parameter directly then it is slow If this is the true it seems to suggest a difference between their storage/access. I'm not sure what we can do about this, though in this case you have at least found a workround with the variable. Tag: ForEachLoop task not behaving as expected
  - 9
    - query format date Hi I wrote query to get date from database SQL 2000 server. Sometime I get different date format not MMDDYYYY but number as Julian calendar Do you know what wrong with this How can I wrote SQL so that all date will be MMDDYYYY Thanks Daniel Tag: ForEachLoop task not behaving as expected
  - 10
    - How do i start playing with sql server??? Hi all, please help me!! I've downloaded and installed the express edition file (SQL Server 2005 Express Edition with Advanced Services - 234mb) but all I'm getting on the program menu are the configuration tools! Something appears to be missing - can someone shed any light on this THanks in advance Tag: ForEachLoop task not behaving as expected
  - 11
    - Foreach Loop - Value back to 0 MrHat wrote: Can you set a value of a variable inside an expression No. Tag: ForEachLoop task not behaving as expected
  - 12
    - How to set douplicate id in sql server 2005? Rafael Salas wrote: I don't understand; how is that a column is a PK but you don't want to insert any value on it a PK has to have a value, right It could either be an identity, or he needs to assign a value to it. Tag: ForEachLoop task not behaving as expected
  - 13
    - Programmatically aborting a package Okay, I need to expound. I have created a Windows service that executes SSIS packages. Many of these are long-running, perhaps for hours. If an operator realizes that something is wrong after submitting a package, I'd like to allow him to send a cancel request to the service and then have the service stop the package. What I do NOT want to do is code every single precedence constraint to check for an "abort" flag (and I'm not sure I'd even know how to set such a flag once the package has started without putting an MSMQ task or the like between each executable--yuck!). Could I simply kill the thread that is executing the package That seems "dirty," and I was hoping for a cleaner method. Tag: ForEachLoop task not behaving as expected
  - 14
    - SQL 2005 Management Studio Query Designer suggestions / questions Dave, First of all, if you have suggestions for the SQL Server developers at Microsoft, I can assure you they want to hear them, but please post them at the SQL Server product feedback site. Here's the link: http://go.microsoft.com/fwlink/ linkid=34178 While I'm no fan of the query designer (you can see my suggestions and bug reports on the product feedback site), I can reply to some of your observations: 3) I would call this a bug, but not exactly an inconsistency bug. Open Table is simply the wrong place to write a query. (The query designer is wrong, too, I think, but Open Table is really wrong!) Either there should be a "New Query" item in the context menu, or the documentation should tell you to open a blank query editor window and choose Design Query in Editor from there. 4-5) You do raise a good issue that there seems to be more than one query designer tool. Instead of putting effort into making these separate tools consistent, they should remove all query designer features from Open Table (and fix the documentation) so no one mistakes or misuses that tool as a query designer. 6) It's not so obvious. First of all, if * is checked, what do you want to see in the criteria pane (Think of programming the designer - keeping the checkboxes in 1-1 relationship with the rows of the criteria pane is much easier than having a special case for the * checkbox.) And it's good practice not to use *, but list all column names explicitly instead, at least in production code. 7) Yes, this is confusing, but a query editor window can contain all kinds of things beyond just a single query. Again, this is a consistency issue for the programmers - it's work with little benefit to parse what's in the editor just to see if it is a single query or not, so that if it's a single query, it opens in the designer. If you do post your suggestions to the product feedback site, perhaps you could post links to your suggestions here. That way more people might learn about the product feedback site, and people reading this thread might visit your suggestions and vote on them. Votes make a difference to Microsoft when they prioritize non-critical issues. Steve Kass Drew University http://www.stevekass.com Tag: ForEachLoop task not behaving as expected
  - 15
    - Error on Date Data During Insert All the error messages: [Flat File Source [1]] Error: Data conversion failed. The data conversion for column "extraction_date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". NOTE: OutputColumnWidth is zero and "grayed out" in the Flat File Connection Manager Editor [Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component. [Flat File Source [1]] Error: An error occurred while processing file "C:\work\CAPS\SQL\UNL\unloader\cont100.txt" on data row 1. [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038. [DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039. Tag: ForEachLoop task not behaving as expected
  - 16
    - Auto Filter in 2005 Reporting Services Hi, In reporting services it is really a lot more work since you need to include each special value like 'All' and 'Top 10' in the list of parameters which means using unions etc. It is possible although not advisable. Cubes are made for these kind of things and are optimized to browse through data. Greetz, Geert Geert Verhoeven Consultant @ Ausy Belgium My Personal Blog Tag: ForEachLoop task not behaving as expected
- Next
  - 1
    - The variable "System::LocaleID" is already on the read list. Did you by chance copy packages as a foundation for creating other packages Or of all the packages you execute with your custom service, were they all created from scratch Tag: ForEachLoop task not behaving as expected
  - 2
    - Import and Export Data Wizard missing in SQL Server 2005 Express Edition Hi Steve, There is no supported Import/Export functionality in any version of SQL Express. Some people have managed to get the old Import/Export wizard onto their computers, but it has not been tested and it is not supported. If you go down this path, you do so at your own risk. BCP on the other hand is tested and supported. Mike Tag: ForEachLoop task not behaving as expected
  - 3
    - Query problems - Group By and Latest date Hi, apologies but i need one more piece of advice on this subject. If i want to include a column with a serial number of the part that has been replaced, how would i do that. As soon as i add it, it needs to be part of an aggregate function or the group by clause. When it becomes part of the group by clause it then duplicates the part again. Any ideas Thanks, Grant Tag: ForEachLoop task not behaving as expected
  - 4
    - Reporting Service Cache I enable cache for that rdl say 60 min What does this mean You enabled shapshot caching You don't have to do anything to specifically enable user session caching. However, I haven't tried filtering only though. If it still doesn't work for you I will take a look. Tag: ForEachLoop task not behaving as expected
  - 5
    - Newbie Parameter Problem Sorry, please ignore my last post. I fixed it by adding the parameters to the second dataset. (They were not defined). Thanks! Tag: ForEachLoop task not behaving as expected
  - 6
    - Backup Database Remove N (from N' @MyParameter). Pass only @MyParameter Check this. declare @backupfilename varchar ( 100 ) set @backupfilename = 'D:\master.bak' Backup database master to disk = @backupfilename Madhu Tag: ForEachLoop task not behaving as expected
  - 7
    - RESTORE DATABASE 1 - Try executing SSMS as the administrator account. You know, right click - Run as administrator, then try to do the restore from SSMS 2 - If point 1 did not work, then execute first: restore headeronly from disk = ... This will give you the list of backup sets in that device, the type, etc. Choose the backup set you are interested in (position), let us say 3, then execute: restore filelistonly from disk = ... with file = 3 to see the list (logical and physical) files in that backup set, then apply the "restore database" and use the logical names with the option "move" to move the physical file names to a specific folder. Let us know how it goes. AMB Tag: ForEachLoop task not behaving as expected
  - 8
    - problem with recursive cte query SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy. Its mentioned here : http://msdn2.microsoft.com/en-us/library/ms345137.aspx Can you suggest any alternate option Tag: ForEachLoop task not behaving as expected
  - 9
    - Request for feedback - Aliases have been deprecated for some time and will be removed soon Alias were superseded by roles in SQL Server 2000, for more information you can visit the following BOL pages: · Deprecated Database Engine Features in SQL Server 2005 (under “Features Not Supported in the Next Version of SQL Server” ): http://msdn2.microsoft.com/en-us/library/ms143729.aspx · Create User (TSQL): http://msdn2.microsoft.com/en-us/library/ms173463.aspx · Create role (TSQL): http://msdn2.microsoft.com/en-us/library/ms187936.aspx Hopefully these links will help on the migration process to use roles, and it will also help to clarify the nature of the planned change in the next release of SQL Server. Thanks a lot, for your feedback. -Raul Garcia SDE/T SQL Server Engine Tag: ForEachLoop task not behaving as expected
  - 10
    - Help with cupe partitioning/processing Hello guys I have an issue with one of our cubes which i hoped someone could help me with... The problem is a historical cube which is currently growing quite drastically, and includes daily history from 2004 until today. The cube is just processed full every single day, and this means processing 80+ million rows every day, when actually most of the data is not changed at all. I decided I want to split my cube in data for 2004, 2005, 2006, 2007 and then the current month. Once the current month is over then i would merge it with the year to date partition and create a new partition for the new month. My problem is how to implement the processing in a simple way Right now the processing is done in SSIS by just processing full the db, so I guessed i have to make 2 tasks, one to process full all the dimensions first , and one to process full the cubes afterwards in which i process for this specific cube only the latest partition. But this means at the end of the month I would have to change the package manually, since i need to merge some partitions and then process full only the newest partition again... Is there any simpler way of doing this Is my approach correct in the partitioning way and processing full of only the newest partition, except at the end of the month when i would need to merge and process the ytd partition Tag: ForEachLoop task not behaving as expected
  - 11
    - Problem running SSIS from C# If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error. Tag: ForEachLoop task not behaving as expected
  - 12
    - MDX Sum distinct across specific dimensions Here's a more detailed description of the suggested model, which uses many-many dimensions: Suppose this is the "TitleRole" fact table and measure group, with dimensions Group, Title and Role: GroupID Title Role ConditionCount 11682 Director Operations Licensing Manager 2 11683 Director Operations Licensing Manager 3 11683 Director Operations Product Manager 3 There is a 2nd "Group" fact table and measure group (as suggested above), with [ConditionCount] "sum" measure - this could be the Group dimension table: GroupID ConditionCount 11682 2 11683 3 This measure group relates directly to the "Group" dimension, but has a many-many relation with the Title and Role dimensions, via the intermediate "TitleRole" measure group. Now, if the Title: "Director Operations" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 5. And if the Role: "Product Manager" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 3. Tag: ForEachLoop task not behaving as expected
  - 13
    - OleDB Source and Parameters skrewed wrote: Hi Rafael, Sorry for the delay in my response, I was travelling. Thanks for your answer - this has worked. Great! skrewed wrote: Be wary of Data Types - for example, variable data type is a string, haven't found a good way to cast the data type in the expression without causing errors (cast operator documentation not very good) And you won't find a way to cat it. The expression editor expects a string ALWAYS. You just need to make sure that you get the expect result when you evaluate the expression. skrewed wrote: This is pretty cryptic when you think about it - placing a variable into a query required me to build an expression...seems complicated for what I'm tyring to achieve It can look complicated but it is a very efective and powerfull feauture to set dynamic values to objetcs' properties. skrewed wrote: Also, "Evaluate expression" button/function - glad this is there, a must have piece of functionality I agree, it is verry helpful. There are some request from the users to include and upfront visula indicator when a property is being affected by an expression. Right now, the only way know if there is an expression is digging into the expression property. Tag: ForEachLoop task not behaving as expected
  - 14
    - searching similar records within a table Hi, thanks for the query. The query returns the output as the same calldetailid that was passed Code Block CURRENT OUTPUT 6532 1 456456 Code Block DESIRED OUTPUT '22654392' , 'SA023', 'Bobby' , 33 I tried fetching the records using r2 and rd2 in the select, but it says The multi-part identifier "r2.requestid" could not be bound. Any clues plz help thanks. Tag: ForEachLoop task not behaving as expected
  - 15
    - How to Create Assembly function using dll files in SQL Server 2005??? Yes, I understand your problem from your first post. So once again: 1. what is the exact error message you are getting 2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance. The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance. Niels Tag: ForEachLoop task not behaving as expected
  - 16
    - Database user and Corresponding login Thanks guys its indeed a good piece of info!!! Tag: ForEachLoop task not behaving as expected

sadie519590

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 ---> script2 ---> script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Top

Re: ForEachLoop task not behaving as expected

Phil Brammer

Do you have the tasks hooked together by precedence constraints (The green arrows )

Top

Re: ForEachLoop task not behaving as expected

K108

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

Top