none
SSIS Script Task - Not Getting Executed RRS feed

  • Question

  • Hello All,

    I have a script task in a for each loop container that calculates the filesize, and if the filesize is > 0 the subsequent tasks gets executed.

    Here the code inside the script task is not getting executed and hence the file size is empty/0 and the control is not going to the subsequent tasks.

    is this something to do the framework ? can anyone please help my solve this issue.

    Thanks,
    Rahul.

    Thursday, October 10, 2019 4:27 PM

All replies

  • Hi Rahul,

    Please share the following:

    • SSIS package Control Flow design. A screen shot of it will be useful.
    • SSIS Script Task source code.


    Thursday, October 10, 2019 4:50 PM
  • Hello,

    thanks for the response.below is the code and screenshot.

    public void Main()
            {
                try
                {
                    string segment_separator = "";
                    string field_separator = "";
                    string component_separator = "";
                    string ValidEDILayout = "0";
                    string EDIHeaderValue = "";

                    var receivedFile = (string)Dts.Variables["User::strFileNameFull"].Value;
                    var fileInfo = new System.IO.FileInfo(receivedFile);


                    if (fileInfo.Length > 0)
                    {
                        string raw = File.ReadAllText(receivedFile);
                        EDIHeaderValue = raw.Substring(0, 3);
                        ValidEDILayout = "1";

                        if (EDIHeaderValue != "ISA")
                        {
                            ValidEDILayout = "0";
                        }

                        if (EDIHeaderValue == "ISA")
                        {
                            segment_separator = raw.Substring(105, 1);
                            field_separator = raw.Substring(3, 1);
                            component_separator = raw.Substring(104, 1);
                        }



                    }

                    Dts.Variables["User::intFileSize"].Value = fileInfo.Length;
                    Dts.Variables["User::strFieldDelimiter"].Value = field_separator;
                    Dts.Variables["User::strSegmentDelimiter"].Value = segment_separator;
                    Dts.Variables["User::strComponentDelimiter"].Value = component_separator;
                    Dts.Variables["User::blnValidEDILayout"].Value = ValidEDILayout;


                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                catch (Exception e)
                {
                    Console.WriteLine($"The file could not be opened: '{e}'");
                }
            }


    Thursday, October 10, 2019 4:56 PM
  • Hi Abhinav,

    I can see you are debugging already so did the receivedFile variable get the proper file ?

    I guess it did not.


    Arthur

    MyBlog


    Twitter

    Thursday, October 10, 2019 5:07 PM
    Moderator
  • Hello Arthur,

    i have set the break point  but the control never goes inside the script task or the break point never gets hit.

    that's the major problem and since it doesn't goes in , subsequent tasks never gets executed.

    -Rahul

    Thursday, October 10, 2019 5:10 PM
  • Hi Rahul,

    Check it out the following c# code.

    It checks for file existence, file attributes, error handling, logging, etc.

    bool rc = true;
    
    try
    {
    	var receivedFile = (string)Dts.Variables["User::strFileNameFull"].Value.ToString();
        var fileInfo = new System.IO.FileInfo(receivedFile);
    				
    	if (fileInfo.Exists)
    	{
    		// Get file creation date
    		Dts.Variables["User::FileCreationDate"].Value = fileInfo.CreationTime;
    
    		// Get last modified date
    		Dts.Variables["User::FileLastModifiedDate"].Value = fileInfo.LastWriteTime;
    
    		// Get size of the file in bytes
    		Dts.Variables["User::FileSize"].Value = fileInfo.Length;
    	}
    	else
    	{
    		rc = false;
    		// Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
    		Dts.Events.FireWarning(1, Dts.Variables["System::TaskName"].Value.ToString()
    			  , string.Format("File '{0}' does not exist", fileInfo.FullName)
    			  , "", 0);
    	}
    
    }
    catch (Exception ex)
    {
    	rc = false;
    	// Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
    	Dts.Events.FireError(18, Dts.Variables["System::TaskName"].Value.ToString()
    		  , ex.Message.ToString()
    		  , "", 0);
    }
    
    if (rc)
    {
    	Dts.TaskResult = (int)ScriptResults.Success;
    }
    else
    {
    	Dts.TaskResult = (int)ScriptResults.Failure;
    }


    Thursday, October 10, 2019 5:46 PM
  • i don't have problem with the code, it just that the control never goes inside the script task
    Thursday, October 10, 2019 6:32 PM
  • i don't have problem with the code, it just that the control never goes inside the script task

    Hi Rahul,

    1. I shared a much more robust code.
    2. You need to check Foreach Loop Container settings.
      Please share as a screen shot.


    Thursday, October 10, 2019 6:36 PM
  • thanks for your response,but there is no issue with for each loop container.it just the script task doesn't go inside its code
    Thursday, October 10, 2019 8:05 PM
  • Hi Rahul,

    IMHO, you have  a problem with the debugging in VS. Just forget about it for now.

    You need to check the SSIS logging.

    The c# code I shared with you does exactly that.

    Thursday, October 10, 2019 9:07 PM
  • Hi Rahul,

    May I know how do you edit your Foreach Loop Container?

    Please check if you can find files in the specified folder.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 11, 2019 7:45 AM