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 Date help

Hello DBA's:

I have a linked server to DB2. The date field in my DB2 source files is of the format 1031222 (7 character). The 1 indicates 21th century.Year 2000 and beyond. 03 is year 2003,12 is the month, 22 is the day. Now this date is the current date. Tommorow would be 1031223.

Records are added to this file on a daily basis with the current date. Now I need to query this file on a daily basis using my linked server. An example query would be.

Select *
From DB2LinkedServer.Filename
where Date = CurrentDate.

Now How do I implement this current date. I am not sure how do I include the function getdate() and get to the CurrentDate format of my source data.

ThanksDECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate()) ),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate|||Thanks a lot. That helped.
Originally posted by Brett Kaiser

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate()) ),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate|||Brett,

You had sent the me this piece of code pertaining to the current date help topic.

Bretts Code:

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)

+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate

When you execute the above code for month < 10 or/and day < 10 you get - 1041 7 for Jan 7,2004. I want 1040107.

So I changed the above code to -

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)

+ CASE WHEN SUBSTRING(CONVERT(char(2),DATEPART(mm,GetDate())), 1,2) < 10
THEN '0'+CONVERT(char(2),DATEPART(mm,GetDate()))
ELSE CONVERT(char(2),DATEPART(mm,GetDate()))
END

+ CASE WHEN SUBSTRING(CONVERT(char(2),DATEPART(dd,GetDate())), 1,2) < '10'
THEN '0'+CONVERT(char(2),DATEPART(dd,GetDate()))
ELSE CONVERT(char(2),DATEPART(dd,GetDate()))
END
SELECT @.bogusDate

--

However the day part doesn't change. I have been able to modify the month. Can you help me fix the code.

Thanks for your help.

Vivek

Originally posted by Brett Kaiser

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate()) ),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate|||My Bad:

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (mm,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (dd,GetDate())),2)
SELECT @.bogusDate

HAPPY NEW YEAR|||Thanks Brett. Happy New Year to you too.
Originally posted by Brett Kaiser
My Bad:

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (mm,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (dd,GetDate())),2)
SELECT @.bogusDate

HAPPY NEW YEAR

Currency Variables?

Starting in my control flow, I execute a data flow that populates a recordset via SQL 2005 Stored Proc. One of the columns in source table is a currency type.

Back to the control flow, I have a for each container that includes an execute sql task that updates or inserts records into another table. I get precision or data type issues since I can not assign the package variable to a currency data type. The only way I can get this to work is if I convert the currency column in my data flow to a string and then cast the variable in my update/insert sql task. Any suggestions?

Thanks....Gary

One suggestion would be to map the currency type in the recordset to a variable of type Object in the ForEach container's Variable mappings tab.

Then, when running the Execute SQL task in the ForEach container, use that Object variable in the parameter mapping tab with a DataType of CURRENCY.

No casting is needed.|||That works as described...Thanks!

Currency

hi,
i would like to write a sql function which takes 3 parameters.
1. Source currency type
2. destination currency type
3. Amount to be converted
I need the dynamic rates.
Does anybody hav any idea about how to go with this.
Thanks in advance,
Vinu
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Are you using SQL Server 2005 ? Therefore you could use Webservices to
call an currency service synchronously or async. by storing the data
in a currency table. That sure is possible with SQL 2000 but it MORE
easier with SQL 2005.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Currency

hi,
i would like to write a sql function which takes 3 parameters.
1. Source currency type
2. destination currency type
3. Amount to be converted
I need the dynamic rates.
Does anybody hav any idea about how to go with this.
Thanks in advance,
Vinu
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1Are you using SQL Server 2005 ? Therefore you could use Webservices to
call an currency service synchronously or async. by storing the data
in a currency table. That sure is possible with SQL 2000 but it MORE
easier with SQL 2005.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Cube structure to be "redone" everytime on changing Data Source in Analysis Services20

Hi Folks,

I have a strange discovery. This use to work fine with AS 2000.

Here's the problem : I have a AS 2005 cube "Cube1", existing with all the functionality. I want to test some things - so I did cube "Save As" tstCube1. Now tstCube1 is my test cube. I changed the data source in tstCube1 to a "new" SQL database ( this new database is exactly the same schema structure as compared to the schema that original Cube1 refers). Here's what I see now in tstCube1 -- all the calculated members, relationships in data source view, all hierarchies -- all gone. I don't see them in tstCube1, although I see them in original Cube1.

Do I have to redo the cube structure everytime I change a datasource. This could be a potential nightmare - because on migrating cubes from Dev -> Test--> Production, I have to change the data sources all the time. Does this mean everytime I change the data source -- the whole cube has to be redone.

Your suggestions are most welcomed. Thanks for any ideas...

-- Savvy

There is a difference between moving to a new data source and editing the existing data source to point to a different database. I edit the data source connect string all of the time.

_-_-_ Dave

|||Thanks Dave. That worked..

Cube structure to be "redone" everytime on changing Data Source in Analysis Services20

Hi Folks,

I have a strange discovery. This use to work fine with AS 2000.

Here's the problem : I have a AS 2005 cube "Cube1", existing with all the functionality. I want to test some things - so I did cube "Save As" tstCube1. Now tstCube1 is my test cube. I changed the data source in tstCube1 to a "new" SQL database ( this new database is exactly the same schema structure as compared to the schema that original Cube1 refers). Here's what I see now in tstCube1 -- all the calculated members, relationships in data source view, all hierarchies -- all gone. I don't see them in tstCube1, although I see them in original Cube1.

Do I have to redo the cube structure everytime I change a datasource. This could be a potential nightmare - because on migrating cubes from Dev -> Test--> Production, I have to change the data sources all the time. Does this mean everytime I change the data source -- the whole cube has to be redone.

Your suggestions are most welcomed. Thanks for any ideas...

-- Savvy

There is a difference between moving to a new data source and editing the existing data source to point to a different database. I edit the data source connect string all of the time.

_-_-_ Dave

|||Thanks Dave. That worked..

Cube showing slightly different data than in data source...?

Hello,
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I am making a basic assumption here about the measure Hit_ID which is also
part of your key --> that you are COUNTING hits.
Try Count Distinct within Analysis Services instead of teh default Count. If
that does not work, I suggest introducing a measure within your fact table
called count_hits or something which will have a value of 1 (one) for every
unique combination of your keys. Then use this with the Sum aggregate
function to define your measure within the cube.
Let us know if it worked.
Thanks and hope this helps.
Rangarajan Suresh
www.picarossolutions.com
"Neile" wrote:

> Hello,
> I am very perplexed with this problem I'm having. I work for a website-based
> company and my job is to create a data warehouse based on usage of the site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse database.
> I then use Reporting Services and MDX to create reports based on this usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all hits
> are originally recorded) with hits in the final Warehouse database to see if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of the
> companies have a higher hit count, i.e. show more hits, than actually exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!

Cube showing slightly different data than in data source...?

Hello,
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I presume your measure is a Count of HitID to have the number of hit by
users...
How many rows are loaded when you process the cube? (normally this number =
number rows in the database)
There is any join between the Hit table and the others? (joins created by AS
to load the cube; see the query executed by AS)
does the user appear twice in the database?
if your users are under the company and if 1 user can appear in more then 1
company, then you can count twice (or more) the same hit.
"Neile" <Neile@.discussions.microsoft.com> a crit dans le message de news:
E05E7B92-22C7-4811-9DDF-B1C9C25EEBD0@.microsoft.com...
> Hello,
> I am very perplexed with this problem I'm having. I work for a
> website-based
> company and my job is to create a data warehouse based on usage of the
> site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of
> data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse
> database.
> I then use Reporting Services and MDX to create reports based on this
> usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all
> hits
> are originally recorded) with hits in the final Warehouse database to see
> if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of
> the
> companies have a higher hit count, i.e. show more hits, than actually
> exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table
> called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!
>

Cube Model

After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.
This guy has a solution:
http://codebetter.com/blogs/brendan.tompkins/archive/2007/02/28/SQL-2005-Report-Models-from-an-Analysis-Server-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.

Cube Model

After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.This guy has a solution:
http://codebetter.com/blogs/brendan...-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8
C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.

Cube Datasource is not on SQL Server

Hi,

is it possible to define a Cube on SQL Server 2005 where the data source itself is on a DB2 or Oracle Database? I would like to have the Cube to collect the data from the DB2 Database to build the Cube. The Cube itself can copy some data to the SQL Server if necessary. What I'm trying to avoid is to have an ETL Process to copy over everything to the SQL Server and then build a Cube on top of it. It is currently very difficult to synchronize the data from the DB2 to the SQL Server. It is also not possible to go over a time period as it can happen that you get new records for the last year. We have to work with a lot of data (several million per day).

My goal is to find an intelligent Solution to make sure that the Cube has all data.

Thanks.

Ertan

Analysis Services supports building cubes by directly connecting to several relational databases. Oracle , DB2 are on the list of supported sources. Please refer to the books online to exact list of supported DB versions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CSV file as a data source

Hi,
Can a CSV file be used as a data source for Microsoft Analysis Services 2005?

Rgds
hari

I tried it, i couldn't get it to work. I even tried being sneaky and linking the csv in an access mdb then using the jet provider, but it didn't seem to like that either..

C|||You can create SSIS pipeline, which will read CSV's and then uses AS Processing Task to load data into partitions. I have done it in the past, and while it is not a smooth ride (especially when your partitioning scheme of the cube is not trivial), it can be made work.|||

Ahh...thanks guys!!
I will try your suggestions.

CSV file as a data source

Hi,
Can a CSV file be used as a data source for Microsoft Analysis Services 2005?

Rgds
hari

I tried it, i couldn't get it to work. I even tried being sneaky and linking the csv in an access mdb then using the jet provider, but it didn't seem to like that either..

C
|||You can create SSIS pipeline, which will read CSV's and then uses AS Processing Task to load data into partitions. I have done it in the past, and while it is not a smooth ride (especially when your partitioning scheme of the cube is not trivial), it can be made work.|||

Ahh...thanks guys!!
I will try your suggestions.

Crystl32.ocx with vb .net

Hi all,

I've created the report with Seagate crystal reports with the source to the database through a stored procedure (with ODBC). The stored procedure contains input parameter that I want to sent it to this report from .Net through Crystal report Control Crystl32.ocx.
I installed this control into .net but I don't how can I pass the parameter which I'm writing into textbox to report that contains stored procedure as a data source.

== Some code with Crystl32.ocx that I'm using to call the report:

With cr2
.ReportFileName = Application.StartupPath + "\klient_info.rpt"
.RetrieveDataFiles()
.DiscardSavedData = True
.ParameterFields(1) = "@.KlientID;" & TextBox2.Text & ";False"
.WindowState = crptMaximized
.Action = 0

End With

With the code above I'm passing into crytal report @.klientID - what I'm writting into textbox2. But the problem is that I don't want crystal report to prompt again for inserting input parameter.

Thanks,
ProBani.With the code above I'm passing into crytal report @.klientID - what I'm writting into textbox2. But the problem is that I don't want crystal report to prompt again for inserting input parameter.

are you sure that you only have one input parameter and that it is ParameterFields(1). My guess is that Crystal either expect another paramter or that the one your inputting isn't correct. That's why you get prompted to input another param.