Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem referring to a SSIS variable inside Script task Expand / Collapse
Author
Message
Posted Monday, June 29, 2009 1:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 8:47 PM
Points: 55, Visits: 132
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.
Post #743911
Posted Monday, June 29, 2009 2:02 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
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 - by Jeff Moden
Post #743934
Posted Monday, June 29, 2009 2:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 1,510, Visits: 8,472

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

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.
Post #743938
Posted Monday, June 29, 2009 2:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 30, 2010 3:18 PM
Points: 14, Visits: 74
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
Post #743994
Posted Tuesday, June 30, 2009 11:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 8:47 PM
Points: 55, Visits: 132
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!
Post #745091
Posted Wednesday, July 1, 2009 9:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
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 - by Jeff Moden
Post #745507
Posted Thursday, July 9, 2009 6:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 8:47 PM
Points: 55, Visits: 132
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: Dts.Variables["System::Propagate"].Value = false; 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!
Post #750788
Posted Friday, July 10, 2009 9:44 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
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 - by Jeff Moden
Post #751200
Posted Friday, July 10, 2009 1:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 8:47 PM
Points: 55, Visits: 132
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 !!!
Post #751381
Posted Wednesday, July 15, 2009 3:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:19 PM
Points: 67, Visits: 317
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
Post #753855
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse