{"id":907,"date":"2012-05-08T06:21:20","date_gmt":"2012-05-08T06:21:20","guid":{"rendered":"http:\/\/bicortex.com\/?p=907"},"modified":"2013-10-08T21:55:03","modified_gmt":"2013-10-08T21:55:03","slug":"how-to-programmatically-archive-file-into-a-directory-using-script-task-in-ssis","status":"publish","type":"post","link":"https:\/\/bicortex.com\/bicortex\/how-to-programmatically-archive-file-into-a-directory-using-script-task-in-ssis\/","title":{"rendered":"How to Programmatically Archive File into A Directory using Script Task in SSIS"},"content":{"rendered":"

In \u2018How to create an XML file using SSIS and MS SQL Server\u2019<\/a> as well as \u2018Generating Files through bcp Utility in SQL Server\u2019<\/a> I showed how to generate files (CSV, XML, TXT etc) using common tools and SQL available with Microsoft SQL Server.\u00a0 These two posts were followed up with \u2018Programmatically Checking If File Exits In SSIS\u2019<\/a> which can provide an extension to the file generating processes. In this post I would like to show how to programmatically archive a file into a directory once it has been created. Although this can be achieved through the use of a \u2018File System Task\u2019 from SSIS Toolbox menu (which also has the ability to copy, delete, create and rename files or directories), a simple C# script can do the job just as nicely. A common scenario for such application would be to archive a file after it was created\/generated, FTP\u2019ed to a remote location. I often find that having an archive of a file which was sent to a client can save me a lot of hassle when the file does not get delivered successfully or a client requests another copy. The script for archiving an CSV file can be found below.<\/p>\n

\r\nusing System;\r\nusing System.Data;\r\nusing Microsoft.SqlServer.Dts.Runtime;\r\nusing System.Windows.Forms;\r\nusing System.IO;\r\n\r\nnamespace ST_2ef9e8dab497461da7eedabd19e69fa4.csproj\r\n{\r\n    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]\r\n    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n    {\r\n\r\n        #region VSTA generated code\r\n        enum ScriptResults\r\n        {\r\n            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,\r\n            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n        };\r\n        #endregion\r\n\r\n        public void Main()\r\n        {\r\n\r\n            DirectoryInfo di = new DirectoryInfo(Dts.Variables["Local_Dir"].Value.ToString());\r\n\r\n            FileInfo[] fi = di.GetFiles("*.csv");\r\n            String filename = fi[0].Name;\r\n\r\n            string sourceFileName = filename;\r\n            string destinationFile = @"\\\\c:\\Destination\\" + sourceFileName;\r\n            string sourceFile = @"\\\\c:\\Source" + sourceFileName;\r\n\r\n            if (File.Exists(destinationFile))\r\n                File.Delete(destinationFile);\r\n            \/\/ To move a file or folder to a new location:\r\n            System.IO.File.Move(sourceFile, destinationFile);\r\n            Dts.TaskResult = (int)ScriptResults.Success;\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
As you can see, the script uses a variable which we need to create before we execute the script as part of an SSIS package and later declare as \u2018ReadOnlyVariable\u2019 in the Script Task properties window as per below:<\/p>\n
\"\"<\/a><\/p>\n
\n
This variable (Local_Dir), previously declared as string, points to the folder where the file is stored before moving it off to a \u2018history\u2019 folder. As for the script, it is quite simple. Firstly, what we do here is creating an array, populating it with files from our Local_Dir variable and declaring paths for a source and destination directories. Next, we check if the file with the same name already exists in the destination folder, deleting it if it does. In this way we can ensure that an exception will not be thrown due to a file with an exactly the same name already sitting in our destination folder. Finally, we move the file to our destination folder.<\/p>\n
That is really it! Now we can use this script in combination with other techniques e.g. ‘generating files through bcp utility’<\/a> and ‘programmatically checking if the file exists script’<\/a> to create a more comprehensive file management solution in SSIS.<\/p>\n","protected":false},"excerpt":{"rendered":"
In \u2018How to create an XML file using SSIS and MS SQL Server\u2019 as well as \u2018Generating Files through bcp Utility in SQL Server\u2019 I showed how to generate files (CSV, XML, TXT etc) using common tools and SQL available with Microsoft SQL Server.\u00a0 These two posts were followed up with \u2018Programmatically Checking If File […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51,50],"tags":[23,25,12,24],"class_list":["post-907","post","type-post","status-publish","format-standard","hentry","category-net","category-ssis","tag-net","tag-c","tag-code","tag-programming"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/907","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=907"}],"version-history":[{"count":18,"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/907\/revisions"}],"predecessor-version":[{"id":1915,"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/907\/revisions\/1915"}],"wp:attachment":[{"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=907"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=907"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=907"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}