Problem referring to a SSIS variable inside Script task

  • Hi,

    I have a very simple Foreach loop container, where it specifies very simple Foreach File enumerator and refers to a specific directory.

    I've set the Variable mappings to a user variable FileName.

    Inside the Foreach Loop container, I have a simple Script task where it calls visual basic script. I am stuck on the line, which I believe is causing the problem but couldn't figure out why:

    Dim reader As System.IO.Streamreader = New System.IO.StreamReader(Dts.Variables.Item("FileName").Value.ToString)

    I've also tried to make a simple messagebox call, and it also failes with same error message.

    System.WIndows.Forms.MessageBox.Show(Dts.Variables("FileName").Value.ToString)

    The error message says somethinglike this:

    Error: System.Reflection.TargetInovacationException: Exception has been thrown by the target of an invocation. ---> Microsoft.sqlServer.Dts.Runtime.DtsRuntimeException: THe element cannot be found in a collection. This error happens when you try to retrieve and element from a collection on a container during execution of the package and the element is not there.

    ...

    I am completely stuck and would apprecaite any help! It's probably very obvious for some of you. 🙂

    Oh, the value of the FileName seems okay to me before the script task was executed during debug. The value shows correctly the FileName path as I expected.

  • So you're using the Script Component as the source? Have you considered using a flat file conection manager instead?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • It looks like the script task cannot find the variable.

    Check that the variable is spelled correctly. Remember, variable names are case sensitive.

    If that does not work check the scope of the variable. Check that you do not have a variable with a scope of just the script task.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • The second step on the script task editor has two lines wherein you can enter ReadOnlyVariables and ReadWriteVariables.

    Did you prefix the variable names with User:: or System::?

    I had to do so to make it work.

    HTH,

    IanO

  • Thanks a lot for the replys!

    I couldn't believe that I forgot about ReadOnlyVariables and ReadWriteVariables and spent hours of debugging inside the script task. It's my second time using it and I totaly forgot about the ReadOnlyVariables and ReadWriteVariables. Thanks a lot for pointing it out!

    Now I have a second question:

    Inside the Foreach Loop, I have a File system task after the script task and it tries to move the file to another directory. But I got an error message saying "The Process cannot access the file because it is being used by another process."

    First of the file, the file wasn't used by another process. Second, how can I skip the file even if the error occurs? Currently the FailPackageOnFailure and FailParentOnFailure are both set to false for the File System task.

    Thanks again!

  • I'll ask again, is there a reason why you need to use a Script component as your source to read from a flat file? It sounds to me like you're adding in a level of complexity when you could just use the flat file source adapter.

    As far as your error goes, create an OnError Event Handler and set the Propogate system variable to false in the Event Handler. This will prevent an error from bubbling up and erroring out your for each loop. Instead of failing the for each object, it will just stop execution of that iteration and begin at the beginning of the for each with the next iteration.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Thanks, John! Sorry for the late reply. I had been on vacation.

    The reason I used the Script component is because the file is not delimited. It's a text log file. To my understanding, the flat file source adapter is for delimited or columnized files.

    Regarding the OnError Handler you suggested, I tried to put the "Script Task" on the OnError Handler. However, the available list in the readwritevariables doesn't have System::Propogate variable in its dropdown list. Therefore, even though I coded:[font="System"] Dts.Variables["System::Propagate"].Value = false; [/font]in my script, it wouldn't work. Would you please give more specifics or an example? I do want to skip the failed iteratiion and continue on the next for the loop.

    Thanks again!

  • Ah, thanks for the clarification. I just wanted to make sure that you weren't just using the Script Component because it was there. You never know what someone's experience level is and therefore their motives behind doing things a certain way. Because of that, I find it easier to just ask why instead of assuming there is a good reason.

    Yes, if you have to parse a non-delimited text file, a Script Component is the best way to do it.

    As for the OnError Handler, you don't need another script task to set the Propogate variable value. Here's how to prevent an error from halting your package execution. These steps need to be done for each Control Flow object inside of your For Each container:

    1. Left-click task to set the focus on it.

    2. Go to the Event Handlers tab.

    3. Ensure that the Event Handler drop-down box has the OnError event selected. By default, the screen will be grayed out with a blue message "Click here to create an 'OnError event handler for executable 'Your Task Name'" in the center of the screen.

    4. Mouse click anywhere in the grayed out screen.

    5. At this point, you could add in tasks that would fire in the event of your task returning an error. You can configure it to send e-mail notifications, log error values to a table, or a host of other actions. For what we want to do; however, you don't need to drag any tasks into the view. Instead, click on the variables tab. This will display all of the variables that you have access to inside the Event Handler. You'll want to make sure you select the 'Show System Variables' option.

    6. Find the variable labeled Propagate (you'll notice it is scoped to the OnError event handler). Set the value to 'False'.

    7. At this point, any error generated by this task will halt the execution of the For Each loop's current iteration. The For Each loop will continue execution with the next item in the collection. Keeping this value set to 'True' (the default) will completely halt the execution fo the For Each container.

    Hope this helps, let me know if you have any questions or concerns.

    John

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • John, Thank you so much for taking your time to write the detailed instruction! It worked great! I had no idea that you could directly set variables like that!

    Thank you !!!

  • Hi,

    I am developing the SSIS package to parse a non-delimited file.

    Can you please help me on the script which is to be written to parse the file ?

    Thanks

  • touchmeknot123 (7/15/2009)


    Hi,

    I am developing the SSIS package to parse a non-delimited file.

    Can you please help me on the script which is to be written to parse the file ?

    Thanks

    This should be set up as a new thread. When you do so, please post some sample data (showing the format of the source data and the desired output format). Wading straight into designing a script may be overkill ... but without seeing some sample data, we cannot give good advice.

    Also, please explain further what you mean by 'parse'.

    Phil

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi John Rowan!

    I have a similar problem. I did what you said about the Propogate variable = false on all tasks inside the foreach container. But the execution still halts, it doesn’t go to the next item iteration of the loop. Am I missing something?

    =======================================================================

    ForEach Loop

    DataFlowTask(OnErorr Propogate=false)

    ==>success>

    DataFlowTask(OnErorr Propogate=false)

    ==>success>

    FileSystemTask(OnErorr Propogate=false)

    ==>success>

    SendEmailTask(OnErorr Propogate=false)

    Problem: My SMT could fail with bad email address and to me that’s ok. For example: the first item in the collection has a bad email. I have a OnError event SendeEmailTask here to handle when the task fails. It executed the OnError SendEmail task fine. Then, the SQL task was executed. Then the whole package failed. It never went to the next item in the collection. I want my package to still go on to the next item in the collection but it stops the whole package. I added the propogate to all the tasks in the foreach container like you mentioned to do, but it stops on the first item of the collection because the email is bad.

    ==>success>

    SQL Task

    =======================================================================

  • The reason it isn't found in the collection is because it is not in the collection. Go to the data sources listed in the Solution Explorer that point to that database and delete it. Then recreate it. When you do, it will ask you to put that data source in as a new collection item (unless it is already there and you wouldn't get that message if it was).

    Jamie

  • I have got dialogue box added to SSIS package,through script at the end,that populates when process is completed.

    But it throws the below error when called from SSMS job “Script Task: Error: Exception has been thrown by the target of an invocation”

    Script has just below code is in C#

    System.Windows.Forms.MessageBox.Show(“Process has completed”);

    Please clarify.

  • Just wanted to comment, I had same problem and this worked like a charm....Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply