144 How to handle errors in script task in ssis

hi friends my name is akhil Ahmed and in this particular video tutorial we will see how to handle the errors in script task in ssis so the agenda of today's video tutorial is that we will log the error messages from scriptos to a new flat file for every failure and the error file will have the current date and time in it so that we can easily identify the files for each date so normally what happens if you are using a script task in the ssis packages and if the script has fails then it shows a very generic message and you won't be able to tell like what is the actual reason for the failure of the script was so there can be lots of reason for the failure but the scriptures does not show the actual reason okay so to know the actual reason for the failure we log the error messages from script us to a flat file or you can also look to a SQL Server table as well it's up to you but in this video I will try to lock the error messages from the skip task to a flat file and the flat file will be created every time according to the current date time so that you can easily identify yeah so let's jump to the demo so I got this blank ssis package and let me try to drag and drop the script task into the control flow window so that we can start working on it now let me create one of the variable here and I can call the variable as ID okay and I can set some value here okay so for this particular variable maybe I can set the value as 100 okay now let me right click and configure the script task let me click on the edit script so the script editor window has been opened up and I just want to use the value of the ID variable that I declared okay so I want to use it here so what I can do I can declare local variable maybe I can call it as a int a equal to and then I can just get the value from the variable DTS dot variables okay and in the double quotes I can just copy the name of the variable so my variable name is ID dot value okay and now because this is of type integer so I need to type cos it to integer so I can write convert Dot to int 32 okay and this should be converted to the integer now okay so this is the code that I have written this is just one line of code I just want to show you like how to lock the errors so you can write tons of code and that should be fine so I just written one line of code now let me do one thing let me click on file exit click ok so I'm just saving the code and then we'll try to execute this particular code okay so the code has been saved now let me click on start button and try to execute this Escape task so you can see that the task got failed okay and in the error messages it is not showing any actual reason but it is giving a generic message so we don't know like what is the error message so your Escape task can contains hundreds of thousands of lines of code and if you got this kind of address then you won't be able to tell like at which line it is failing or what is the error message okay so you can't tell so now we don't know like what is the error message so I can close this one so now I want to handle the errors I want to know like why it is failing what is the error message okay I want the error message to be logged somewhere okay so what I can do I can right click on the script as click edit I can reopen the script editor all right so the script editor window has been opened up now we will use the try and catch blocks to handle the errors so let me open this pre-written code so I know these two lines of code and what they will do we can add it in the beginning so the first line of code current date time so this will give you the current date and the time in this particular variable and then I have declared another variable log folder okay so we need to create a log folder somewhere so that when the error will occur so the ssis package will create the log file to that particular folder okay so that right now the log folder will be inside the D files logs okay so in the D files log so right now we don't have a folder so I can create a folder here logs okay and right now the folder is empty now we need to use the try and catch block so whatever code that you think can fail or you can just put all the code inside the dry block okay so we use the try block here and then after the try block we can use the catch block so whatever code that can fill it will be inside the drive block and then inside the cache block there will be the code that will actually handle the error messages so when any exception will occur inside the drive log the control will move to the cache block okay and in the catch block it will create a new file so it seems like it is missing some namespace system.io so I can click on show potential fixes using system.io okay so what it will do it will create a new file every time inside the log folder and the file name will be error log underscore current date time DOT log okay and the actual exception will be written to that particular file so these are the few lines of code that you can add to every script as that you have and it can handle the errors okay so this is the code so I will share this code with you so that you can also use it in your environment as well now let me click on file exit let me save this code and then I can rerun the ssis package so iuc of the script task now let me click on start button and it should execute the SSS package and it should create a new file so right now it runs successfully because it handled the errors okay and in case if you want to fail then you know we can also try to fail it in case of any failure but right now it will run successfully okay so let me close this one and let me go to the logs so you can see a new log file got created here error lock underscore current date time DOT log and if you open this particular log file so you can see the actual error message so this is the error message the element cannot be found in a collection it means that you are trying to use a variable that you have not used inside the read-only variable or read write variable okay so this is the error message for the variables the element cannot be found in a collection okay so now the reason is that we have used the ID variable inside the Escape task but we have not used it inside the read-only variable or redirect variable so let me select it inside the read only variable because we are just trying to read the data from the ID so I can click on OK OK now let me rerun it and now this time it will run successfully and it won't create any log file because there is no error now okay so it runs fine now let me try to you know write some other code and try to fail on some other message so you can see some different message as well okay all right so a script editor window has been opened up and here we have got the value from the ID into local variable a now let me declare another variable and I will call it as int source and I will assign value maybe 1000 to it okay and then I will declare another variable and I will call it as B and B will be X source and divided by a okay I can call actually this a with the ID because this is the ID so that we won't get confused okay so now what we will do we will divide this 1000 with the ID that will provide from the ssis variable okay and the final value will be inside the variable B now let me try to show this value whatever will be the divider in the message box okay so that we can see the value message box dot show and what is the value I can call it like final value from B is enter we can show the value B dot I can do two string okay so this is how it will work so now when I will execute the package so 1000 will be divided by 100 and the final value will be 10 okay so let's see how it will work and then we will try to set the value of the ID to 0 so that it can also fail and we can get a divide by zero error okay so that's the final plan so let me click on start button and it should give the value as 10 in the message box so you can see that final value from B is 10 because we pass the value as 100 you know in the um ID variable now if I pass the value as 10 so it will give the value as 100 okay you got it so it got the value 100 now let me try to pass the value as 0 and this time it should fail so this time it did not shown any message because it got failed and you can see the error message in the logs folder so in new file got created here 850 the second file this one and if I open this particular file so you can see that attempted to divide by zero okay so you can easily know like at which line it is failing so you can get the actual error message you know using this particular method and for every failure a new file will be created okay and right now it runs successfully and in case if you think that in case of a failure the script should also fail then we can try to fail it as well you know in case of failure so let me show you how we can fill the script task in case of a failure but it will also lock the error message okay all right so if you come to this particular code inside the cache block so in case of the failure the control will move to the cache block okay so inside the catch block what we will do we can copy this particular code and we will put it here and now instead of the success we will set it to failure okay and now you need to write return here okay so that it will fail it and it will be written so I can click on file exit okay now let me try to rerun the SSS package so now you can see that because it went to the cache exception so that's why it got filled and it filled the script task as well and in case of the good value from the ID and in case of the normal behavior when there won't be any error message then the task will run successfully so this time it should return the 100 value final value from B is 100 okay so this is working fine so I will share all the code that I used here and you can download it from the link in the description of the video so I think that's it for today's video thank you guys for watching the video and if you like the video then please click the like button do subscribe to our Channel press the Bell icon and click on all so that you will be notified every time I upload a new video thank you so much

Channel: Learn SSIS

Views: 2,173

Keywords: How to handle errors in script task, How to handle errors in script task in ssis, How can we handle errors in SSIS?, Can we debug the Script task in SSIS?, How do I debug a Script component in SSIS?, how, How, to, handle, errors, in, script task, ssis, SSIS, sql server integration services, msbi, learn ssis

Id: pmXIbq_U_L0

Length: 11min 23sec (683 seconds)

Published: Thu May 04 2023