尝试使用SSIS遍历表列表并从中删除表 [英] Trying to loop through, and delete from, a list of tables using SSIS
问题描述
我目前正在为数据库建立归档和清除过程.我从几天前离开的一名员工那里继承了这项任务-在过去的几个月里(很少)他一直在从事这项工作，所以我不得不花很多时间来回顾他的步骤，以便能够说话并试图弄清楚如何他已经完成了所有这些工作.我们正在使用排定的任务来执行归档过程，而我大部分已准备好进行部署.但是，我们正在使用SSIS包来处理清除过程，而且我之前从未创建或修改过SSIS包，因此我遇到了一些问题，坦率地说，我什至不知道该从何说起.故障排除.
从本质上讲，我们有一个表用于存储将要存档(最终清除)的表的列表，该表还记录了需要清除这些表的顺序，以避免留下任何孤立的记录或FK冲突.然后，SSIS包的目的是加载根据清除顺序排序的表列表，然后循环遍历每个表，并根据WHERE子句引用每个记录的存档日期从表中删除.我认为我遇到的问题之一是表名不能用作SQL命令中的参数，尽管尝试了几种方法来解决此问题，但我仍然无法使其正常工作.
根据我在其他地方所读的内容，我试图创建一个包含要执行的SQL语句的变量，然后选择该变量作为SQL语句的源，但是我仍然没有弄清它.我想我可能是从包含SQL命令的foreach循环中错误地提取了表名，但是我不确定自己做错了什么.
如果有帮助，我将在下面使用两个SQL命令发布图像.我也可以发布foreach循环，或者发布与这两个命令有关的不同信息.我再次为我的问题的含糊之处表示歉意，我只是对SSIS不够熟悉，但实际上并不知道可以提供哪些信息.不幸的是，我没有足够的代表直接发布图像，所以我只提供链接.
清除命令SQL命令
删除SQL命令
感谢您的回答，尽管我仍然不太确定如何解决此问题，但我已经解决了该问题.激怒了，我决定懒散地尝试删除负责执行清除的SQL命令，然后以相同的方式重新创建它.莫名其妙地解决了这个问题.
就像我在问题中说的那样，我对SSIS包的形成方式一无所知，但我想在幕后会存储很多元数据和其他信息.我认为自从我接手另一个人正在从事的项目以来，我所做的更改就有可能以某种方式使工作陷入困境.显然是这种情况.无论如何，再次感谢您的帮助.
I am currently working on establishing an archive and purge process for our database. I inherited this task from an employee who left a few days ago - he'd been working on it (infrequently) for the past several months so I've had to spend quite awhile retracing his steps so to speak and trying to figure out how he had this all set up. We are using a scheduled task to perform our archiving process and I have that part mostly ready to deploy. However, we're using an SSIS package to handle the purge process, and I've never created or modified an SSIS package before so I'm running into some issues that I'm frankly not really even sure where to start with in terms of troubleshooting.
Essentially, we have a table that we use to store the list of tables which will be archived (and eventually purged), which also records the order that the tables need to be purged in to avoid leaving any orphaned records or FK conflicts. The purpose of the SSIS package then is to load the list of tables, ordered according to the purge order, then loop through each of those tables and delete from them based on a WHERE clause referring to the archive date of each record. I think one of the problems I'm running into is that table names can't be used as parameters in an SQL command, and despite trying a few ways to work around this I can't quite get it to work right.
Based on what I've read elsewhere I tried to create a variable containing the SQL statement I'm trying to execute, then selecting that variable as the source for an SQL statement, but I'm still not getting it right. I think I may be pulling the table names from the foreach loop containing the SQL command incorrectly but I'm not sure what I'm doing wrong.
I'll post images below of the two SQL commands I'm using if that helps. I can post the foreach loop as well, or different information pertaining to either of the commands. Again I apologize for the vagueness of my question, I'm just not really familiar enough with SSIS yet to actually know what information would be useful to provide. I unfortunately don't have enough rep to post the images directly so I'll just provide links.
Purge order SQL command
Delete SQL command
Thanks for the answers, I've solved the problem though I'm still not quite sure how. Exasperated, I decided on a lark to try deleting the SQL command responsible for performing the purge, then re-creating it the same way. Bafflingly this fixed the problem.
Like I said in the question, I don't know much at all about how SSIS packages are formed but I imagine there is a lot of metadata and other information stored behind the scenes. I figured since I was picking up a project someone else had been working on there was a chance that the changes I made had gummed up the works somehow. Apparently this was the case. At any rate thanks again for the help.
这篇关于尝试使用SSIS遍历表列表并从中删除表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
