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.

|||

If you just want to read the variables from the User Interface then it is easiest to just the "Variables vars" variable that should have been passed through to the UserInterface form in the constructor.

Then you can simply refrence the package paramaters either by iterating through them or using their name.

e.g. vars["Name_Of_Parameter"].Value

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.

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.

sql

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.

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.

|||

If you just want to read the variables from the User Interface then it is easiest to just the "Variables vars" variable that should have been passed through to the UserInterface form in the constructor.

Then you can simply refrence the package paramaters either by iterating through them or using their name.

e.g. vars["Name_Of_Parameter"].Value

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.

Current executing task

Hi everyone

I'm trying to write a monitoring application for SSIS packages deployed on my machine. I know I can look at running packages via the DtsRuntime.Application object's GetRunningPackages method. Does anyone know if there is any way one can view tasks in that package that are currently being executed?

Cheers

Sachin

Wow! Brilliant! I was hoping for at least one reply... I guess I have to question whether this is possible or not. :)|||

You can, but it comes down to implementing the IDTSEvents interface, and/or overriding the packageEvents class.

You will then override the the function's OnPreExecute/OnPostExecute in your execution + monitoring application.

This "get current running tasks" would be much eaiser if there were not in bug in Application.GetRunningPackages() in which in InstanceId (the unique job execution identifier) does not match the execution id in the logs. If it did, you could call get running Packages, and then use logging to determine what was running and be close to on the money.

To me, the bug is analogous to logging the SQL Server spid in SQL profiler, but having it never match "select spid from master..sysprocesses".

|||

sachin.rao wrote:

Wow! Brilliant! I was hoping for at least one reply... I guess I have to question whether this is possible or not. :)

You posted at 17:09 and then sent this reply at 09:20 on the next working day. GIve people a chance! Also, nobody is required to answer you know.

As jaegd said, you can derive this by looking at the OnPreExecute/OnPostExecute events.

-Jamie

|||

Ok. Thanks jaegd.

Jamie - I realize that nobody has to answer. No offense intended. It was just that 50-odd people had viewed the topic and there hadn't been a response, so I was just thinking out aloud. That's all.

Cumulative log

Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS.

thoughts?

thanks

What do you mean "cumulative"? If you use a table to store your error records, you can pull in that table and send the records in an e-mail.

Though, if the records are going to be high, you may want to pull the records out to a text file and then attach that file in the Send Mail task. A better approach would be to house everything in a table and write a report against it.|||

how do you pull in different tables? via sql task? and what if you have more 1 table? you would only want to send one email with all the bad records from different tables.

|||Just create one error table. Log all of the bad records to that table and then later select from that table.|||

well all tables have different structures so a single table would not work. any other ideas?

|||If you want to combine them, you are going to have to make the structures similar at some point, right? You could use a dataflow with multiple sources to combine the tables into a flat file, then email that as an attachment.|||

I don't see an straight forward way to get all those rows into a single file/email. Perhaps, it may be easier to include just the key column(s); in such way the structure is the same for all the tables.

Cube won't process in SSIS, but will process thru Mgmt Studio?

Hello,

SSAS newbie here. I have an AS database called "AS_Sales" with numerous cubes, including one called "CP Sales". It has one measure group (also called "CP Sales") that is partitioned by fiscal quarter.

I can right-click the cube in management studio and hit "Process" and it indicates that everything completes successfully. However, I set up an Analysis Services task in SSIS to do the same thing, and I receive errors.

SSIS package consists of two AS tasks -- Refresh Dimensions & Reprocess Cube. The task fails during the dimension reprocessing, with the following error messages:

Information: 0x40016041 at AS_Sales_Cubes_Refresh: The package is attempting to configure from the XML file "E:\SSIS\Config\AS_Sales_Cubes_Refresh.dtsConfig".

SSIS package "AS_Sales_Cubes_Refresh.dtsx" starting.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2001_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2003_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR2' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2007_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2006_QTR1' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Task failed: Rebuild Dimension Structure

SSIS package "AS_Sales_Cubes_Refresh.dtsx" finished: Failure.

It only seems to be failing for a handful of the partitions, which makes the problem all that more confusing. Does anyone have any ideas about what I'm doing wrong?

Thanks in advance,

Jamie

if you have defined your attribute relationships as rigid you cannot use refresh for your dimensions. Try full process of each dimension and see what happens. Place the processing(full) of the dimensions before processing the cubes/measure groups.

HTH

Thomas Ivarsson

Cube Processing Error - Urgent - Thanks

First when I processed cube's (Process Data) using SSIS package I got "Memory error"

"Memory error: Allocation failure : Not enough storage is available to process this command."

I changed "OLAP\ Process \ BufferMemoryLimit" to 30% . I have /3GB setup. and I am using AS 2005 - 32 bit.

When I rerun the package I got olap storage engine error

Errors in the OLAP storage engine: An error occurred while processing the 'Scenario Analysis 2006' partition of the 'Scenario Analysis' measure group for the 'Scenario Analysis' cube from the Risk Reports database.

I don't know what's that, so I run cube process (Full) from Management Studio and got error below while reading data process stoped with following popup message

The trace has been stopped
Unexpected end of the file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body soap: Envelope. Line 13180, position 1. (System.Xml)

Thanks for any help - Ashok

Please check if you're running out of disk space...

Cube Processing Error - Urgent - Thanks

First when I processed cube's (Process Data) using SSIS package I got "Memory error"

"Memory error: Allocation failure : Not enough storage is available to process this command."

I changed "OLAP\ Process \ BufferMemoryLimit" to 30% . I have /3GB setup. and I am using AS 2005 - 32 bit.

When I rerun the package I got olap storage engine error

Errors in the OLAP storage engine: An error occurred while processing the 'Scenario Analysis 2006' partition of the 'Scenario Analysis' measure group for the 'Scenario Analysis' cube from the Risk Reports database.

I don't know what's that, so I run cube process (Full) from Management Studio and got error below while reading data process stoped with following popup message

The trace has been stopped
Unexpected end of the file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body soap: Envelope. Line 13180, position 1. (System.Xml)

Thanks for any help - Ashok

Please check if you're running out of disk space...

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Problem

I have a cube in which I am putting the data on a regualar basis by running a SSIS package.
I am currently storing my data in partitions of 15 days. I store the datetime upto which I have transferred the data into the cube in a seaprate table and after it I transfer the updated data from my fact table into the cube.E.g Suppose last datetime stored is 15 Aug ,I will transfer the data into the cube from the fact table which has been in the fact table after 15 aug.
But the problem I am facing now is that the newly updated data can contain some of the data of the previous data (of the same datetime)which has been moved to the cube earlier.

So now The problem is want to move only that updated data from the fact table which is not present the cube.
Please help .......any help is welcome....

Hi,

Not to fimilar with this technique, but one way is to process your cube using and incremental update then setup the configuration to only select the records that have been updated.

http://technet.microsoft.com/en-us/library/ms188966.aspx

http://technet.microsoft.com/en-us/library/ms189431.aspx

Hope that helps a bt

Matt

|||Sorry if I misunderstand, but what about SUM() using GROUP BY in your partition select statement? That would sum any duplicates.

Cube Name Changed After Processing (but I didn''t do anything)

Hello,

I have a cube that has been working fine for a while. Then I re-ran the SSIS to bring in refreshed data (which I've done many times before) and reprocessed the cube. I never changed the name of the cube or anything like that (at least on purpose).

Now when I open SSRS to run a report, it says that it cannot find the cube. I do have a Perspective in there, but I wasn't using it. Previously it had the cube's name Stage and the perspective's name Perspective. Now I see Perspective and Perspective 1.

Any idea what could have caused this and what I can do to change it back?

Thank you.

-Gumbatman

Hi,

Re-processing the cube will not rename the cube, something else should have happened.

To double check the cube name, open BI Development Studio -> File -> Open Analysis Services Database: this will connect to the database; open the cube editor and check (in the properties grid) what the cube name is. Or you can use SQL Management Studio and bring up the cube Properties dialog.

If the cube was renamed indeed from 'Stage' to 'Perspective 1', you can track what happened (who changed what) if you had the FlighRecorder tracing enabled (that logs all the commands sent to server, but usually it's not on for performance reasons).

Adrian

|||

Adrian,

I checked out the properties and the name of the cube is still "Stage".

When I open the cube Browser in BIDS I can chose either Stage or Perspective. However, when I want to build a new report in Reporting Serivces (sorry I forgot to mention that) I only get the choices of Perspective or Perspective 1.

In RS, it was always Stage or Perspective and after I did the processing it changed to Perspective or Perspective 1.

Any ideas of where I should look? I have no idea if I had the recorder thing on to trace this. It is only on my laptop for development.

Thank you for the help.

-Gumbatman

Cube Name Changed After Processing (but I didn''t do anything)

Hello,

I have a cube that has been working fine for a while. Then I re-ran the SSIS to bring in refreshed data (which I've done many times before) and reprocessed the cube. I never changed the name of the cube or anything like that (at least on purpose).

Now when I open SSRS to run a report, it says that it cannot find the cube. I do have a Perspective in there, but I wasn't using it. Previously it had the cube's name Stage and the perspective's name Perspective. Now I see Perspective and Perspective 1.

Any idea what could have caused this and what I can do to change it back?

Thank you.

-Gumbatman

Hi,

Re-processing the cube will not rename the cube, something else should have happened.

To double check the cube name, open BI Development Studio -> File -> Open Analysis Services Database: this will connect to the database; open the cube editor and check (in the properties grid) what the cube name is. Or you can use SQL Management Studio and bring up the cube Properties dialog.

If the cube was renamed indeed from 'Stage' to 'Perspective 1', you can track what happened (who changed what) if you had the FlighRecorder tracing enabled (that logs all the commands sent to server, but usually it's not on for performance reasons).

Adrian

|||

Adrian,

I checked out the properties and the name of the cube is still "Stage".

When I open the cube Browser in BIDS I can chose either Stage or Perspective. However, when I want to build a new report in Reporting Serivces (sorry I forgot to mention that) I only get the choices of Perspective or Perspective 1.

In RS, it was always Stage or Perspective and after I did the processing it changed to Perspective or Perspective 1.

Any ideas of where I should look? I have no idea if I had the recorder thing on to trace this. It is only on my laptop for development.

Thank you for the help.

-Gumbatman

Cube Name Changed After Processing (but I didn''t do anything)

Hello,

I have a cube that has been working fine for a while. Then I re-ran the SSIS to bring in refreshed data (which I've done many times before) and reprocessed the cube. I never changed the name of the cube or anything like that (at least on purpose).

Now when I open SSRS to run a report, it says that it cannot find the cube. I do have a Perspective in there, but I wasn't using it. Previously it had the cube's name Stage and the perspective's name Perspective. Now I see Perspective and Perspective 1.

Any idea what could have caused this and what I can do to change it back?

Thank you.

-Gumbatman

Hi,

Re-processing the cube will not rename the cube, something else should have happened.

To double check the cube name, open BI Development Studio -> File -> Open Analysis Services Database: this will connect to the database; open the cube editor and check (in the properties grid) what the cube name is. Or you can use SQL Management Studio and bring up the cube Properties dialog.

If the cube was renamed indeed from 'Stage' to 'Perspective 1', you can track what happened (who changed what) if you had the FlighRecorder tracing enabled (that logs all the commands sent to server, but usually it's not on for performance reasons).

Adrian

|||

Adrian,

I checked out the properties and the name of the cube is still "Stage".

When I open the cube Browser in BIDS I can chose either Stage or Perspective. However, when I want to build a new report in Reporting Serivces (sorry I forgot to mention that) I only get the choices of Perspective or Perspective 1.

In RS, it was always Stage or Perspective and after I did the processing it changed to Perspective or Perspective 1.

Any ideas of where I should look? I have no idea if I had the recorder thing on to trace this. It is only on my laptop for development.

Thank you for the help.

-Gumbatman

csvde from Execute Process Task not returning groupType

When I run csvde from within an SSIS package's Execute Process Task, it does not return the groupType attribute, but when I run it directly from a command prompt, it does return that attribute. The csvde swiches are set the same in both cases:

-t 3268 -u -f allGroups.csv -d "DC=corp,DC=microsoft,DC=com" -r "(objectClass=group)" -l "groupType,mail,member"

The header line in the output file produced by the package is:

DN,member,mail,member;range=0-1499

while the header line in the file produced when csvde is run directly is:

DN,member,groupType,mail,member;range=0-1499

Has anyone else encountered this behavior?

Thanks,

Ron Rice

How do you have the Execute Process task setup?

Also, you may want to specify a full path to the -f flag.|||

Phil,

Unfortunately, making the -f switch a full path did not change the results.

I also tried changing the order of the attributes in the -l switch to "mail,groupType,member". At first I thought this had worked, because it did return the groupType attribute, but then I noticed that the "mail" attribute was not returned after making this change!

So for whatever reason, when I execute csvde from a package, the first attribute in the -l switch is not returned.

Thanks,

Ron

|||

Drop the double quotes around the -l flag parameters.

-l mail,groupType,member

|||

Phil,

Removing the double quotes from around the -l switch list of attributes did fix the problem. Thanks!

I wonder why csvde would behave differently when run in an Execute Process task versus being run directly from a command prompt. Also, I tried running it using T-SQL and xp_cmdshell, and that had the same problem. And I seem to remember running into issues concerning double quotes when I ran the bcp command from an Execute Process task, as well.

Whatever the ultimate cause of the problem, now that I know the work-around I am a happy camper!

Ron

|||

Rice31416 wrote:

Whatever the ultimate cause of the problem, now that I know the work-around I am a happy camper!

Ron

It's not really a work around. According to the csvde page, double quotes are not required. (See the examples at the bottom.)

http://technet2.microsoft.com/WindowsServer/en/Library/1050686f-3464-41af-b7e4-016ab0c4db261033.mspx