CODEWITHSUNDEEP
ssis
Waller
Waller

Reputation: 1873

SSIS changing variable scope

How do I create a variable with a scope? It's just giving me a default which I do not want, and I'm unsure how to change. This should be simple, and a Google search leads me to believe this is quite a common problem but with no easy solutions.

imgur: https://i.sstatic.net/BQk37.jpg

Upvotes: 6

Views: 32550

Answers (6)

LukaszP
LukaszP

Reputation: 21

How about:

  1. View code of *.dtsx package
  2. Change DTS:ObjectName for desired one.

Will this lead to any issues? At the moment it seems ok but i can't run package yet to check this.

Upvotes: 1

Sudheer
Sudheer

Reputation: 21

We can change the scope of the variable in 2008 BIDS.PFB the steps I did for changing the scope of the variable from the package level to the task/container level.

1)Right click on the package.dtsx file from the Solution explorer and click on the "View code"

2)Search for the variable for which you wanted to change the scope. Copy the XML code related to that variable.

3)Search for the control for which you wanted to use this variable and copy the xml code after completion of any tag(Many tags are related to a task/container).

4)Remove the origial copied code.

5)Save this file

6)Open the package and the scope of the variable would have been changed

I am not sure If it impacts the package or not, but it worked for me.

Kindly let me know If it will impact the package or not if any one knows

Upvotes: 2

Gary Melhaff
Gary Melhaff

Reputation: 61

As someone noted, this changes with 2012. I just got bit by that and note that the 2012 documentation did NOT get updated. It still says variables are scoped to what you have selected which is NOT true. But when you're on the variables window, note the 2nd from the left icon at the top - this is the "Move Variable" button that works quite nicely to allow you to move the scope of the selected variable. BiXpress also has similar function but it's buggy in that it won't pick up OnPostExecute scope making their version very limited.

Upvotes: 2

Anuja
Anuja

Reputation: 61

click on new variable button, add variable name and then click on Move Variable Button right next to add variable. and from there select Executable , to which you want to set a scope to. Hope this helps.

Upvotes: 6

rvphx
rvphx

Reputation: 2402

Well thats the way BIDS has been designed. You create on the control flow element on which you want to create the variable. And if you want to create the variable on the package level, you click on an empty space on the control flow pane. Thats the only way to assign scopes for variables using BIDS. Havent looked at the BIDS Helper, but BIDS itself is capable enough of doing such things.

Upvotes: 0

Waller
Waller

Reputation: 1873

Note: Scope of the variable is set based on the container you have focus before clicking the new variable button. Though BIDS Helper can help you change the scope of the variable very easily that’s the 6th button provided by BIDS Helper on top of the variable pane.

Found the above answer, not very helpful way of doing things.

Upvotes: 3

Related Questions