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 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.
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.
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;
}
UPDATE:
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();
I've finally figured this out.
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.
The main package (main.dtsx) that loads the contents of the files to the DB was failing.
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.
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.
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.
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.
The main solution:
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.
Other issues I encountered are:
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.
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,
TITLE: Microsoft SQL Server Management Studio
An error occurred during local report processing. (Microsoft.ReportViewer.WinForms)
------------------------------ ADDITIONAL INFORMATION:
The definition of the report '' is invalid. (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:
OK
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.