问题描述
我有一个 SSIS包，zip.dtsx.该程序包在serverA上成功运行.我将此包复制到serverB中.但是，当我尝试在serverB上运行zip.dtsx时，它将失败.
I have an SSIS package, zip.dtsx. This package successfully runs on serverA. I copied this package in serverB. However, when I try to run zip.dtsx on serverB, it fails.
zip.dtsx只是读取源文件夹中的文件，将其压缩，然后将压缩文件保存到其他文件夹，然后删除源文件夹中的原始文件.
zip.dtsx just reads a file in a source folder, compresses it, saves the compressed file to a different folder, then deletes the original file in the source folder.
经过一番调查，我发现如果我注释掉C#脚本任务中删除源文件夹中文件的部分.程序包成功运行.
After some investigation, I figured out that if I comment out the part in the C# script task that deletes the file in the source folder. The package runs successfully.
我需要删除源文件夹中的文件.否则，该文件将被重复加载到数据库中.我已经按照建议的，但仍然无法使file.delete成功运行.
I need to delete the file in the source folder. Otherwise, this file will just be repeatedly loaded to the database. I've already re-added the script task references as suggested here, but still I cannot make the file.delete run successfully.
public void Main()
{
String sourcePath = Convert.ToString(Dts.Variables["SourcePath"].Value);
String namePart = Convert.ToString(Dts.Variables["NamePart"].Value);
String destinationPath = Convert.ToString(Dts.Variables["DestinationPath"].Value);
FileStream sourceFile = File.OpenRead(@sourcePath + namePart);
FileStream destFile = File.Create(@destinationPath + namePart);
GZipStream compStream = new GZipStream(destFile, CompressionMode.Compress);
try
{
int theByte = sourceFile.ReadByte();
while (theByte != -1)
{
compStream.WriteByte((byte)theByte);
theByte = sourceFile.ReadByte();
}
}
finally
{
compStream.Dispose();
sourceFile.Close();
destFile.Close();
File.Delete(@sourcePath + namePart);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
更新:
尝试使用完全相同的代码后此处.并发现此代码删除了源文件夹中的文件，因此我尝试更新代码以遵循在链接中删除文件的方式.但是，它仍然无法正常工作.以下是我更新代码的方式.
After trying the exact same code here. and founding out that this code, deleted my file in the source folder, I tried to update my code to follow the way the file was deleted in the link. However, it still did not work. Below is how I updated my code.
String sourcePath = Convert.ToString(Dts.Variables["SourcePath"].Value);
String namePart = Convert.ToString(Dts.Variables["NamePart"].Value);
String destinationPath = Convert.ToString(Dts.Variables["DestinationPath"].Value);
FileStream sourceFile = File.OpenRead(@sourcePath + namePart);
FileStream destFile = File.Create(@destinationPath + namePart);
GZipStream compStream = new GZipStream(destFile, CompressionMode.Compress);
try
{
int theByte = sourceFile.ReadByte();
while (theByte != -1)
{
compStream.WriteByte((byte)theByte);
theByte = sourceFile.ReadByte();
}
}
finally
{
compStream.Dispose();
sourceFile.Close();
destFile.Close();
FileInfo currFileInfo = new FileInfo(@sourcePath + namePart);
currFileInfo.Delete();
推荐答案
我终于知道了.
为了解释整个情况，我们有一个完全运行的sqlserver serverA.我们想在serverB上复制它，以便我们可以在serverB上有一个测试环境.必需的数据库已经在serverB上还原，剩下的就是SSIS包和SQL Server代理作业.
To explain the whole situation, we have a fully running sqlserver, serverA. We want to duplicate this on serverB so that we can have a test environment at serverB. The necessary databases are restored on serverB already, all that's left are the SSIS packages and SQL Server Agent Jobs.
将文件内容加载到数据库的主包(main.dtsx)失败.
The main package (main.dtsx) that loads the contents of the files to the DB was failing.
从Integration Services目录->目录文件夹->右键单击->报告->所有执行，我了解到它在从main.dtsx调用的Zip.dtsx上失败. Zip.dtsx可以压缩访问的文件，对其进行存档并将其从源文件夹中删除.
From Integration Services Catalog -> Catalog folder -> right click -> Reports -> All Executions, I've learned that it is failing on Zip.dtsx that is called from main.dtsx. Zip.dtsx that compresses the file accessed, archives it and deletes it from the source folder.
在Zip.dtsx中处理脚本任务后(我从注释)，我发现它位于脚本任务失败的File.delete()部分.立刻，人们会认为这是一个权限问题.
After playing around with the script task in Zip.dtsx(an idea I got from Kannan Kandasamy's comments), I figured out that it's in the File.delete() part where my script task fails. Instantly, one would think that it is a permission issue.
我的第一个错误是我在Visual Studio上右键单击->执行任务，在执行Zip.dtsx的同时继续执行脚本任务.我一直在我的上一篇文章中捕获运行时错误，但没有意识到因为我正在使用main.dtsx传递给zip.dtsx的包变量.我一直挂在这里，直到弄清楚为什么用硬编码的路径名替换变量后脚本任务才能成功运行.
My 1st mistake is I continued to play on my script task while executing Zip.dtsx by right click -> Execute task on Visual Studio. I kept getting the runtime error screencaptured in my previous post without realizing that I was getting it because I am using package variable passed by main.dtsx to zip.dtsx. I got hung up with this until I figured why the script task runs successfully when I replace the variables with my hardcoded pathnames.
我的第二个错误是用我的硬编码路径替换Zip.dtsx的包变量.直到最后，我才意识到由Zip.dtsx访问的文件夹是serverB中的本地文件夹，并且我正在本地计算机上运行SQL Server代理作业，例如machineA.因此，我将serverB本地文件夹共享给了我的用户帐户.由于某种原因，这使我的程序包严重混乱，因为它不再看到文件夹中的文件，因此我的程序包成功运行，因为它发现文件夹为空.
My 2nd mistake was to replace the package variable of Zip.dtsx with my hard coded paths. Until finally, I realized that the folder accessed by Zip.dtsx is a local folder in serverB and I'm running the SQL server agent jobs in my local machine, say machineA. So, I shared the serverB local folders to my user account. For some reason, that messed up my package badly that it no longer sees the files in the folder thus my package succeeds running because it finds the folder empty.
主要解决方案:
我撤消了对Zip.dtsx所做的更改，并删除了将serverB的本地文件夹共享到我的用户帐户，而是添加了NT Service \ SQL $ Instance以完全控制脚本任务删除源文件夹的源文件夹.文件.请参阅此链接，以检查如何将SQLServer $ Instance添加到文件夹权限设置.
I changed back the changes I did to Zip.dtsx and removed the sharing of serverB's local folders to my user account and instead added the NT Service\SQL$Instance to have full control over the source folder where the script task deletes the files. See this link to check how to add the SQLServer$Instance to the folder permission settings.
我遇到的其他问题是:
-
当从不同的服务器传输软件包并且软件包失败时，我认为.我也做了这一步.但是在serverB中，我找不到Microsoft.SQLServer.ManagedDTS.dll.我所做的是将这个dll从serverA复制到serverB .还要检查系统路径，以查看默认情况下使用的Microsoft SQL Server工具的版本，并确保您在脚本任务中引用了该版本.
When transferring packages form different servers and the packages fail, I think this is also important. I also did this step. But in serverB, I couldn't find Microsoft.SQLServer.ManagedDTS.dll. What I did is I copied this dll from serverA to serverB. Also check your system path to see what version of Microsoft SQL Server tools you are using on default and make sure that is what you reference in your script task.
在调查的某个时间点，我在查看 Integration Services目录的所有执行报告时遇到错误.我通过转到 C:\ Temp 文件夹解决了这个问题.当我双击它进行访问时，出现一个对话框，表明我当前没有访问权限.我单击对话框中的继续"按钮继续.之后，所有执行报告将再次运行.我从SSMS得到的错误是
At some point in time, while investigating, I encountered an error when viewing Integration Services Catalog's All Executions report. I solved this by going to C:\Temp folder. When I double click it to access it, a dialog appeared that I currently do not have access. I continued by clicking the 'continue' button in the dialog. After that, All Executions Report is running again.The error I got from SSMS is,
------------------------------其他信息:
------------------------------ ADDITIONAL INFORMATION:
报告"的定义无效. (Microsoft.ReportViewer.Common)
The definition of the report '' is invalid. (Microsoft.ReportViewer.Common)
编译表达式时发生意外错误.本国的 编译器返回值:"[BC2001]文件'C:\ Windows \ TEMP \ mpgc21o3.0.vb' 找不到.’ (Microsoft.ReportViewer.Common)
An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC2001] file 'C:\Windows\TEMP\mpgc21o3.0.vb' could not be found’. (Microsoft.ReportViewer.Common)
------------------------------按钮:
------------------------------ BUTTONS:
我不确定这对我的案子到底有多重要，但是如果有人遇到相同的问题，我会分享给大家.我是MS SQL Server的初学者，所以我认为这可能会对像我这样的初学者有所帮助.
I'm not sure how much of this really mattered on my case but I'm sharing it in case someone went through the same problem. I am a beginner in MS SQL Server so I think this might help someone who is also a beginner like me.
这篇关于SSIS包无法在file.delete脚本任务上删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！