1

i have a package in SSIS where i try to put data flow task's into a variable, once the data flow tasks fails.

What is the best way to do that ?

Please share screen shots if you can,

thanks in advance,

erezlale
  • 625
  • 2
  • 6
  • 17
  • Possible duplicate of [SSIS: Use System::TaskName inside the dataflow](https://stackoverflow.com/questions/8123544/ssis-use-systemtaskname-inside-the-dataflow) – Tab Alleman Jun 19 '18 at 15:50
  • i think you're right, could you please help : which type of "script component type" do i need to choose ? Source / Destination / Transformation ? and does it even matter ? and when i enter "edit script", where do i put the function in the code ? is it after " Public Overrides Sub" and before "End Class" ? – erezlale Jun 21 '18 at 06:29

1 Answers1

1

There are only a few steps and involve the use of an expression. Listed below is an example using an Execute SQL Task. Note that I'm applying SQL Server 2008 R2 with BIDS. Later version may differ somewhat.

Steps:

  1. Create a new user variable called "SQLTask" with a Scope of "Package", Data Type "String" and a value of "SQL - Process Employee SQL". (It is a good idea to name your control and data flow tasks clearly. You'll be grateful later when troubleshooting.)
  2. Add an Execute SQL Task into the Control Flow. Double-click on it to view the Execute SQL Task Editor.
  3. On the left side of the Editor window, select Expressions. When you do so you'll see "Misc" with Expressions appearing on the next line underneath.
  4. Select Expressions under Misc and then select the small browse button icon when it appears. This should open the "Property Expression Editor" window.
  5. The Property Expression Editor will show two columns, Property and Expression. Select the empty box under Property and select the drop-down when it appears. Scroll down until you find "Name" and select it.
  6. In the right-column under Expression, select the empty box to the right of Name and then select the small browse button when it appears. This will open the Expression builder window.
  7. In the Expression builder window there will be two list areas at the top, with the one on the right showing "Variables". Open the Variables folder, which should then list mostly System variables. But the one just created will be prefaced with "User" called "User:SQLTask". Drag it into the Expression box below.
  8. Select the "Evaluate Expression button". Doing so should now show "SQL - Process Employee SQL" in the grayed-out "Evaluate value" box. It confirms that it is correctly picking up the variable value.
  9. Select the OK button in the Expression Builder window.
  10. Select the OK button in the Property Expression Editor window.
  11. And select the OK button in the Execute SQL Task Editor window.

While the task name from the variable does not get captured when viewed in the Control/Data flow, it will show up when a failure takes place. But because of this, it would still be a good idea to use clear task names in your SSIS package for yourself and others who need to understand the logic/decisions in the process.

On that note, I always recommend a good consistent naming convention for control and data flow tasks beginning with the recommendations discussed in the following link.

Suggested Best Practices and naming conventions

Hope this helps.

user3662215
  • 526
  • 4
  • 12
  • Hi user3662215 this is a really good Explanation for the opposite of my request :), i actually wanted to capture 'data flow task' name in the package INTO a variable, and not capturing the variable name into task's name. Could you please help with that ? – erezlale Jun 20 '18 at 06:44
  • Sorry about that. In that case, the answer you seek was provided best in the initial comment by Tab Alleman. Have you viewed the link he provided that discusses the use of a System variable for the task? – user3662215 Jun 20 '18 at 19:12