How can I force a Next Iteration in a ForEach Loop container?
I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).
If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.
Is it possible to force "Next Interation' on the parent container?
Thanks - Covi
Not quite sure what you mean. In what circumstances do you want to 'force teh next iteration'?
-Jamie
sqlI have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate.
What would be the best way to test for that condition?
Cordell,
You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the loop to decide to mail based on the count variable still being zero.
Here are some helpful links to get you started:
About variables - http://msdn2.microsoft.com/en-us/library/ms141085.aspx
Using variables in Script Tasks - http://msdn2.microsoft.com/en-us/library/ms135941.aspx
Precendence Constraint - http://msdn2.microsoft.com/en-us/library/ms141261.aspx
Cheers,
Patrik
thank you Patrik for researching a solution for my need. I thought this is what I would have to end up doing, but wanted to make sure I was not missing something obvious.
It would be nice in the next major update of SSIS that this condition would be provided as an attribute/event to test for in the ForEach Loop tool.
...cordell...
p.s. Is there a place at MSDN to enter feature requests such as this?
|||Cordell,
Feedback can be submitted to http://connect.microsoft.com.
Glad I could help,
Patrik
Hello All,
Is it possible to force a delete of a table even when another program is using that DB, and still has some view data on that specific table.
I know that I can delete tables if another program is just have connection to the DB, but not using the specific table I'd like to delete. Can it be done also on a viewed table?
You cannot delete (or alter) a table if there is an open cursor on it: the engine refuses to do so.
|||Thanks for your answer,
If so, is there a way to force a complete disconnect of a specific DB, then to delete the table?
|||Not that I know of...Hey all,
I am trying to write a function to drop a specific database no matter the connection status. I have tried
con = new ServerConnection(sql);
con.Connect();
Server srv = new Server(con);
srv.KillDatabase("Name");
,and also tried
Database db= Database (srv, "Name");
Database db=srv.Databases["Name"];
db.Drop();
None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.
Thanks.
Hi,
that was worth a Blog entry, there you are :-)
http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,Database db= Database (srv, "Name");
con = new ServerConnection(sql);
con.Connect();
Server srv = new Server(con);
Database db=srv.Databases["Name"];
//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;
db.Drop();
|||Update to all, the SMO classes were changed to the following:collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();
which is pretty close to my suggestion :-) :
s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName)); -Jens.
s.Databases[databaseName].Drop();
Hey all,
I am trying to write a function to drop a specific database no matter the connection status. I have tried
con = new ServerConnection(sql);
con.Connect();
Server srv = new Server(con);
srv.KillDatabase("Name");
,and also tried
Database db= Database (srv, "Name");
Database db=srv.Databases["Name"];
db.Drop();
None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.
Thanks.
Hi,
that was worth a Blog entry, there you are :-)
http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,Database db= Database (srv, "Name");
con = new ServerConnection(sql);
con.Connect();
Server srv = new Server(con);
Database db=srv.Databases["Name"];
//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;
db.Drop();
|||Update to all, the SMO classes were changed to the following:
collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();
which is pretty close to my suggestion :-) :
s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName)); -Jens.
s.Databases[databaseName].Drop();
Hey all,
I am trying to write a function to drop a specific database no matter the connection status. I have tried
con = new ServerConnection(sql);
con.Connect();
Server srv = new Server(con);
srv.KillDatabase("Name");
,and also tried
Database db= Database (srv, "Name");
Database db=srv.Databases["Name"];
db.Drop();
None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.
Thanks.
Hi,
that was worth a Blog entry, there you are :-)
http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,Database db= Database (srv, "Name");
con = new ServerConnection(sql);
con.Connect();
Server srv = new Server(con);
Database db=srv.Databases["Name"];
//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;
db.Drop();
|||Update to all, the SMO classes were changed to the following:collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();
which is pretty close to my suggestion :-) :
s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName)); -Jens.
s.Databases[databaseName].Drop();
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled stored
procedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.
Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the nature
of a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highly
specialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by the
savings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of your
application to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this:
CREATE PROCEDURE ProcName
@.Param int /* ... other parameters */
WITH RECOMPILE
AS /* ... procedure code follows */
If we omit "WITH RECOMPILE", what will be the consequence? Thanks
WITH RECOMPILE can kill an Asp.net application because HTTP is stateless. The better solution is to force SQL Server to put all your stored procs in the procedure cache on start up. There is a stored proc in the Master called SP(system stored proc) Procoption you can use it to auto start all your stored procs. Recompile is modified in SQL Server 2005 you can recompile only the line you need then your solution will be ok for now there are alternatives. See code below the only value for option is Startup and value is true for ON and false for OFF. Hope this helps.
sp_procoption[@.ProcName =]'procedure'
,[@.OptionName =]'option'
,[@.OptionValue =]'value'
Please kindly elaborate more on: "WITH RECOMPILE can kill an Asp.net application because HTTP is stateless." What do "kill" and "stateless" mean here? Thanks again.
|||
You get the best performance if all your stored procs are in the procedure cache all the time WITH RECOMPILE will not allow that, so everytime your stored proc is accessed your user will wait for SQL Server to recompile the stored proc before executing it. In Asp.net some processes will time out before your stored proc will execute. Kill means your code will timeout and stateless means a protocol without state HTTP is one of them. Your users will wait for SQL Server which is session to finish before objects on your pages can be accessed. The first thing to know about stored procs is avoid Recompile even in Windows appilcation. Hope this helps.
Hello,
I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.
I want to generate XML like the following:
<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>
When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?
Here is an example query (does not work, but illustrates what I would like):
select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1
union all
select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1
order by [employee!1!id!element], tag
for xml explicit
I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.
Any ideas?
Thanks!
Trev
I have the same problem. Is there a solution?
Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."
Could someone post an example of how to do it this way please?
Hello,
I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.
I want to generate XML like the following:
<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>
When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?
Here is an example query (does not work, but illustrates what I would like):
select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1
union all
select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1
order by [employee!1!id!element], tag
for xml explicit
I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.
Any ideas?
Thanks!
Trev
I have the same problem. Is there a solution?
Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."
Could someone post an example of how to do it this way please?
