0

We have a fully running database server, say serverA, whose data are refreshed daily. We want to duplicate this database on a different server, says serverB, so that we have a test environment. The databases has been restored to serverB.

Like serverA, we want serverB's data to be refreshed daily also, so the tests we conduct on serverB can be said as fully accurate since they will have the same data as serverA. We deployed the SSIS packages used in serverA in serverB and copied the SQL Server Agent Jobs in serverB also.

I am trying to modify these jobs and packages so that they can run smoothly on serverB, I'm changing directory paths, server names, etc.

Now, there is this job that always fails because of a package, zip.dtsx. zip.dtsx retrieves files from directoryA, compresses them and saves the compressed file to directoryB, then deletes the file in directoryA. However, I cannot figure out why it's having a runtime error.

zip.dtsx has a script task named Zip files.

The script language is Microsoft Visual C# 2010

The ReadOnlyVariables set are User::DestinationPath, User::NamePart, User::SourcePath,$Package::filename

The script is,

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;
        }

The error I'm getting, when I execute the task in Microsoft Visual Studio -> Right click Script Task object -> Execute task enter image description here

I am not familiar with Microsoft Visual C# and I have just also begun using SSIS packages, so I'm really at a loss.

UPDATE: I tried commenting out different lines in the C# script. Finally, when I commented out File.Delete(@sourcePath + namePart);, the job calling zip.dtsx has succeeded. However, I am not sure why I'm having an error with this line. I'm not sure if it is because of permissions or any other else.

Krish
  • 319
  • 4
  • 19
  • Can you check script task invocation itself is failing or it is failing to invocation of GZipStream? – Kannan Kandasamy Oct 14 '16 at 09:01
  • Did you added "using System.IO; using System.IO.Compression"? – Kannan Kandasamy Oct 14 '16 at 09:02
  • Hi @Kannan Kandasamy, yes there is at teh top of the script. `#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; using System.IO.Compression; #endregion` – Krish Oct 14 '16 at 09:25
  • How can I check if it is in invocation of GZipStream it is failing? – Krish Oct 14 '16 at 09:26
  • You can try commenting GZip code and check whether the plain script task is running fine? – Kannan Kandasamy Oct 14 '16 at 09:28
  • I commented GZipStream and I'm still encountering the runtime error. Also, I've seen this [link](http://stackoverflow.com/questions/28997381/runtime-error-exception-has-been-thrown-by-the-target-of-an-invocation-from-sc). I already removed and re-added the references but stil same runtime error occurs. – Krish Oct 14 '16 at 09:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/125686/discussion-between-kannan-kandasamy-and-krish). – Kannan Kandasamy Oct 14 '16 at 09:39
  • This has been answered in my other [post](http://stackoverflow.com/questions/40081471/ssis-package-fails-on-file-delete-of-script-task). – Krish Oct 18 '16 at 04:44

0 Answers0