Current package reference.

hi

Iam developing a custom ssis source component, in that i need to get the reference of the current package where this component is added. I can get get the reference of the current package using this line of code

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

You cannot get reference to the task or package from a component at run-time. The most you can do is to use variables. This is a deliberate design decision taken by MS early on with SSIS, to prevent self-modifying packages amongst other issues.

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

hi

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

What is wrong with using the VariableDispenser? Why would you want another method? The VariableDispenser is available from the base class you inherit when creating pipeline component, so it is available to you. Yes you have to ock variables, but again what is the problem with doing this?

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

hi

My problem is that i need to get the list of variables in the current package (without knowing their names). I need to list their names in the custom UI of the component as does the OLEDB component. Is there any method to do this?

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

When building a UI, you will be using the IDtsComponentUI class, and notably the Edit method. This passes in a Variables collection which you can use, passing through to your form. You will probably also want the IDtsVariableService, which gives you a nice UI for creating variables as well. For this you use the IDtsComponentUI.Initialize method. One of the parameters is the IServiceProvider, which can be used to get the variable service. Cache the provider in a class level variable so you can access it in the Edit method, and pass through to with your UI. Convert it like this-

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

Why is it not possible to iterate the variables? If I want to capture variables (ie at the time of an error), I have to have a specific list of names that I want to capture for debugging purposes? Is there a list somewhere that lists all the variables that are available? What about variables that are added by other components that could help with debugging when an error is caused later on in the control flow or dataflow?

I'm just missing something completely here. Variables should be public or private and if you have them in a collection, (which they are), you should be able to iterate over them. Moreover, variables are such a basic part of any process, it should be dead simple on how they can / should be used.

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Yes, if you write some code/tasks in an On Error event, you do need to know in advance the variables you wish to read. The whole point is you would set this up at design-time. Do you expect your package to dynamically change during execution? This will not happen, and cannot happen at all in SSIS.

Components will not add any variables at runtime, so design-tme again.

Using variables is very simple. Do you actually have a real-world requirement that you could use to illustrate what you really want to do? You seem to be blustering about a problem that I don't think is there.

|||

Darren,

It baffles my mind that you cannot see why manidas wants to iterate through the variable collection at run time. Let me give some examples of why this would be nice to have and maybe you can then see the light.

1) Let's say I've got a package that pulls some startup variable values from a configuration files and the package is not working as I expect. I would want to see what the variables were at some point in time to help me troubleshoot my problem. Yes, I could write code that explicitly retrieved the values of each and every variable but that could be a lot of code when all you should have to do is write somethign to loop over the collection. Also, let's say I add or remove a variable. Now I have to go to all the places that I placed this code and update it.

2) It's pretty painful right now when you're editing a script (and you are using variables) because you can't get to the variable list while you're in the script editor. It would be nice if you could output all the variables to text and then copy and paste them into your code as you need them.

3) Almost all of my DTS packages get called from a SQL Agent Job so I send an email to myself when I'm done. Right now I use the connection collection to display the connections I'm using in the email. It would be nice to include the variables in that email as well.

Now, after just these three examples off the top of my head, if you can't see why not having access to the variables collection would be useful then either your logic skills are a bit lacking or you're trying to make excuses for something that isn't there but should be.

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

I want to add a dataflow task programmatically in the script task using the current package, Under dataflow task also create a new source and destination adapters,which uses the connection managers created. finally do copy column mapping and execute the dataflow. Is it possible? Did I mean here that it is a self modifying package. The above code gives me the current package reference using the path. Would you please give me hint of code how I can go about adding Dataflow task to the current package.

Thanks

Subhash Subramanyam

Bhavana Anand.

|||The current version of SSIS does not support adding or creating new tasks or data flows to a running package. You would have to generate a seperate package, which you could then call from the package that generates it.|||

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

I don't get what you are asking. Can we clarify that when we refer to runtime, we mean that the package is executing. Design-time is when the user interface is manipulating the package or task. So a task UI can be running, but that is termed design-time for the package and dicussions above.

You can only create variables and manipulate objects at (package) design-time. You cannot create a variable at (package) runtime, so your statement "we are not able to get details of varibales which i create at runtime" does not make sense. You cannot get details, becaue you cannot create them so there are no details to get.

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.