Foreach Loop - Value back to 0

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Catching Events I have added the event handler at the top level ( Package) for errors events and information events indeed and, as i said before, the flow to execute in this events does not execute. May i have to set some propertie to enable event catching Tag: Foreach Loop - Value back to 0
  - 2
    - Vista SQL 2005 SP2 client to Windows XP SQL 2005 (non-sp2) workstation. Apparently, this is not a firewall issue. You may need to run your SQL Management Studio from an elevated windows. Just right click and select run as administrator should work. Tag: Foreach Loop - Value back to 0
  - 3
    - Trigger(its very urgent) Khilil: There is a serious design problem at play here. Everytime that you update your update your EMPLOYEES table you are doing an update of the count of employees in the table. I would speculate that this is going to put an exclusive table lock on the table. If that is the case, THIS IS VERY BAD! This will make this a single threaded update that will be prone to deadlocking. I am speculating that you will do a table lock because most likely, "Employee_id" is the primary key of the table. Please, provide the table definition for this table. Tag: Foreach Loop - Value back to 0
  - 4
    - Cant find data source hi, If the sql compact edition provider is correctly installed, you may see in menu Tools -> Connect to Database... into DataSource list box. (Microsoft SQL Server Compact Edition 3.1) Once you have it, then add reference System.Data.SqlCeReference to your project. If you didn't find any assembly called as shown (System.Data.SQlCeReference ) then your sqlce is not correctly installed. keep me informed,, regards Tag: Foreach Loop - Value back to 0
  - 5
    - Unable to Edit or Create SSIS packages or Maintenance Plans Hello Martin, Were you able to solve the problem I have similar symptoms - I could create SSIS packages earlier, but now I am getting similar error while opening an existing package or creating a new one. Tag: Foreach Loop - Value back to 0
  - 6
    - Select expression Type I meant that both of the values in the expression are integers so sql server would default to an integer result. Tag: Foreach Loop - Value back to 0
  - 7
    - 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: Foreach Loop - Value back to 0
  - 8
    - 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: Foreach Loop - Value back to 0
  - 9
    - 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: Foreach Loop - Value back to 0
  - 10
    - 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: Foreach Loop - Value back to 0
  - 11
    - 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: Foreach Loop - Value back to 0
  - 12
    - 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: Foreach Loop - Value back to 0
  - 13
    - 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: Foreach Loop - Value back to 0
  - 14
    - 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: Foreach Loop - Value back to 0
  - 15
    - 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: Foreach Loop - Value back to 0
  - 16
    - 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: Foreach Loop - Value back to 0
- Next
  - 1
    - 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: Foreach Loop - Value back to 0
  - 2
    - 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: Foreach Loop - Value back to 0
  - 3
    - 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: Foreach Loop - Value back to 0
  - 4
    - 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: Foreach Loop - Value back to 0
  - 5
    - 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: Foreach Loop - Value back to 0
  - 6
    - ForEachLoop task not behaving as expected 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 Tag: Foreach Loop - Value back to 0
  - 7
    - 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: Foreach Loop - Value back to 0
  - 8
    - 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: Foreach Loop - Value back to 0
  - 9
    - 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: Foreach Loop - Value back to 0
  - 10
    - 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: Foreach Loop - Value back to 0
  - 11
    - 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: Foreach Loop - Value back to 0
  - 12
    - 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: Foreach Loop - Value back to 0
  - 13
    - 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: Foreach Loop - Value back to 0
  - 14
    - 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: Foreach Loop - Value back to 0
  - 15
    - 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: Foreach Loop - Value back to 0
  - 16
    - 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: Foreach Loop - Value back to 0

MrHat

Hi,

Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data.

Somewhere inside the Foreach Loop, you set an int variable MyInt to a value.

My question:

Is it possable when each iteration begins to set MyInt to 0

Thanks in advance.

Top

Re: Foreach Loop - Value back to 0

Phil Brammer

Is the foreach loop evaluating the same variable, or are you using the variable for something else

Script tasks can assign values to variables.

Top

Re: Foreach Loop - Value back to 0

MrHat

Can you set a value of a variable inside an expression

Top

Re: Foreach Loop - Value back to 0

Phil Brammer

MrHat wrote:

Can you set a value of a variable inside an expression

No.

Top

Re: Foreach Loop - Value back to 0

Benorama

But you could set it by running "SELECT = 0" inside an Execute SQL task at the start of your loop, and assigning that output parameter to your variable.

Top