Package Parts – First Impressions

Question

Post reply

Package Parts – First Impressions

shubhankarthatte

Mr or Mrs. 500

Points: 589
More actions
April 18, 2017 at 10:47 pm

#322968

Comments posted to this topic are about the item Package Parts – First Impressions

Viewing 9 posts - 1 through 8 (of 8 total)

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

Dave Pendleton SSC-Addicted Points: 405 More actions · Answer 1

The inability to access package variables renders Package Parts pretty useless, actually.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

it has few shortcomings

--from article

The inability to access package variables renders Package Parts pretty useless, actually

--reader comment

I think you meant to say, it has a few shortcomings. And that is generous. Microsoft has at least recognized the need. But the reader comment hits it exactly--without the ability to use package variables, I can't think of a single practical use for it. If it did work with package variables, I would have lots of uses for it. Hopefully the wait won't be too long.

Luxgilby SSCrazy Points: 2097 More actions · Answer 3

The inability to access package variables renders Package Parts pretty useless, actually

I haven't used SSIS for a while but I remember similar behaviour from elder versions (SQL Server 2003). It wasn't possible to assign values to package variables at runtime directly. However it has always been possible to assign values via a script task. I'm convinced that a script task can pass package variables to exposed package part variables.

You should give that a try. If it was possible 10+ year ago, why should it not be possible nowadays?

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 4

from elder versions (SQL Server 2003).

SSIS changed A LOT in the 2005 version. There is no more activeX script. I used to be able to do a lot of things using ActiveX script that I still can't do in VB.Net. In any case, that's quite a leap. You need to be able to access properties to assign values. It might be possible, but it wouldn't be safe to assume that because it was possible in 2003 should mean that it's possible now.

BrainDonor SSCoach Points: 19254 More actions · Answer 5

An interesting article - thank you for posting it.
As already said though, until you can access package variables the use of it is very limited.
However, at least I now know this feature exists and can keep a track of the future development.

Steve Hall
Linkedin
Blog Site

Luxgilby SSCrazy Points: 2097 More actions · Answer 6

There is no more activeX script

That is true. However I (for whatever reason) never used 'ActiveX script task' but the plain 'Script task' with underlying C# code.

Simple exercise:
Create 2 variables of type String: TestSource and TestTarget. Assign a value to TestSource and nothing to TestTarget
Create a 'Script Task' on the package. You need to define read-only and read-write variables on the task properties:

In the C# code('Edit Script' button), you just add this line to your entry point method (default is Main but can be changed on the properties):

Dts.Variables["User::TestTarget"].Value = Dts.Variables["User::TestSource"].Value;

Run the task/package. On exit of the script task, the TestTarget value is the same as TestSource.
You just need to remember to set variable scope correctly.

If variables from package parts can be defined in a package, they can be accessed by package elements so that should work.

adelio.stevanato 21159 SSC Enthusiast Points: 194 More actions · Answer 7

I am trying to understand how Microsoft could introduct something so limited.
ALL our packages are highly parameterised, this includes the database connection details plus a ton of other variables. If there is no way of passing in variables for connections or anything else then for us it is essentially useless.It would have been far better if Microsoft had produced something that was truely usefull instead of something half baked. And it seems to have quite a few issues (errors) as well.

Fly Girl SSCommitted Points: 1560 More actions · Answer 8

Thanks to this post: https://prathy.com/2016/06/my-thoughts-on-ssis-2016-ssis-package-parts-2/ it appears that you can use the Package Parts variables in the Package but you have to manually change the scope of the variables once you pull the PP into the Package.
However, for my organization as well, the separate connection objects are the killer shortcoming. There's no way we're going to manually change the connections on the parts when all the Package connections are being managed/changed via environments on the server.
I'm hoping this will improve in future versions but at the moment what they have out with 2017 is not good enough to be useful.