{"id":881,"date":"2012-04-17T08:19:46","date_gmt":"2012-04-17T08:19:46","guid":{"rendered":"http:\/\/bicortex.com\/?p=881"},"modified":"2013-10-08T21:58:49","modified_gmt":"2013-10-08T21:58:49","slug":"programmatically-checking-if-file-exits-in-ssis","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/programmatically-checking-if-file-exits-in-ssis\/","title":{"rendered":"Programmatically Checking If File Exits In SSIS"},"content":{"rendered":"

In \u00a0\u2018Generating Files through bcp Utility in SQL Server\u2019<\/a> post I showed how to create a simple CSV file and output it onto a file system. In this post I will demonstrate how to programmatically check if the file exists in the directory where we intended to place it (it enables us to take action if the file has not been created as expected). This can be incorporated into a process of creating files via bcp utility as its extension; for example, if the file has not been created notify operator(s) or perform some other action.<\/p>\n

Firstly, we will create a variable named FileExists (you can call it anything you want as long as you stick to the name throughout the process) of Boolean type, set the scope to \u2018Package\u2019 and set its value to FALSE. This variable will store our result and we will use it in a VB.NET script as well as precedence constraints.<\/p>\n

\"\"<\/a><\/p>\n

Next, ensure that our FileExists variable has been declared in the \u2018Script Task Editor\u2019 for \u2018ReadOnlyVariables\u2019 field as per below and that the language selected is set to Microsoft Visual Basic 2008.<\/p>\n

\"\"<\/a><\/p>\n

We are now ready to create a simple VB.NET script which populates the FileExists variable (setting to TRUE or FALSE) depending on whether the file that we wish to check for existence \u00a0is there or not.<\/p>\n

\r\nImports System\r\nImports System.Data\r\nImports System.Math\r\nImports Microsoft.SqlServer.Dts.Runtime\r\nImports System.IO\r\n\r\nPublic Class ScriptMain\r\n\r\n    Enum ScriptResults\r\n        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success\r\n        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n    End Enum\r\n\r\n    Partial Public Class ScriptMain\r\n        Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n\r\n        Public Sub Main()\r\n\r\n            Dim directory As DirectoryInfo = New DirectoryInfo("c:\\")\r\n            Dim file As FileInfo() = directory.GetFiles("*.csv")\r\n\r\n            If file.Length > 0 Then\r\n                Dts.Variables("User::FileExists").Value = True\r\n            Else\r\n                Dts.Variables("User::FileExists").Value = False\r\n            End If\r\n\r\n            Dts.TaskResult = ScriptResults.Success\r\n         End Sub\r\n    End Class\r\nEnd Class\r\n<\/pre>\n
All the above script does is checking if the file exists on the c:\\ drive and then populating our FileExists SSIS package variable with either a value of \u2018TRUE\u2019 if it does and \u2018FALSE\u2019 if it doesn\u2019t. This can then be used by controlling the flow of the subsequent package containers based on the logic applied in the precedence constraint which follows the script task. In order to redirect the flow, let\u2019s create two dummy \u2018Execute SQL Task\u2019 containers, link them to our \u2018Script Task\u2019 and populate them with some dummy SQL code (two dashes i.e. — will do for this exercise). Next, we will apply a simple logic in the expression field of the two \u2018Precedence Constraints\u2019of to how to proceed with the execution. In the first one, set the \u2018Evaluation Operation\u2019 to Expression and type in the following expression in the \u2018Expression\u2019 field: @FileExists==TRUE as per below.<\/p>\n
\"\"<\/a><\/p>\n
The second \u2018Precedence Constraint\u2019 will look almost identical with the exception of the actual expression. Here, we change it to @FileExists==FALSE. When finished, you should see two blue squares next to our \u2018Precedence Constraints\u2019 and the actual connections should change their color to blue as well (SQL Server 2008 R2). So far, the part of the package we have just developed should look like this.<\/p>\n
\"\"<\/a><\/p>\n
To check if the package works, all we need to do is to put a CSV file in the target directory (c:\\ drive in our case) and the execution should follow the path of the \u2018Precedence Constraint\u2019 where the expression was set to \u2018@FileExists==TRUE\u2019. If we delete the file, the execution should go with the other Precendence Constraint. Those two scenarios are pictured below.<\/p>\n
\"\"<\/a><\/p>\n
This can be further developed by incorporating other tasks and actions e.g. if @FileExists==FALSE (meaning that no CSV file was found), \u2018Send Mail Task\u2019 can be run to notify operator(s).<\/p>\n
To check if the package works, all we need to do is to put a CSV file in the target directory (c:\\ drive in our case) and the execution should follow the path of the \u2018Precedence Constraint\u2019 where the expression was set to \u2018@FileExists==TRUE\u2019. If we delete the file, the execution should go with the other Precendence Constraint. Those two scenarios are pictured above.<\/p>\n","protected":false},"excerpt":{"rendered":"
In \u00a0\u2018Generating Files through bcp Utility in SQL Server\u2019 post I showed how to create a simple CSV file and output it onto a file system. In this post I will demonstrate how to programmatically check if the file exists in the directory where we intended to place it (it enables us to take action […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51,5,50],"tags":[23,12,24,49,13,22],"class_list":["post-881","post","type-post","status-publish","format-standard","hentry","category-net","category-sql","category-ssis","tag-net","tag-code","tag-programming","tag-sql","tag-ssis","tag-vb-net"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/881","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=881"}],"version-history":[{"count":21,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/881\/revisions"}],"predecessor-version":[{"id":1917,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/881\/revisions\/1917"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=881"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}