Showing posts with label iam. Show all posts
Showing posts with label iam. Show all posts

Thursday, March 22, 2012

cursor application?

Hi all,
This is not an urgent query merely an interested question. But for
what would I use a Cursor for? I have looked at the on-line help but I
am still unclear why it for what reason it might be employed?
Many thanks
SamTwo reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.

The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database. I can't think of a good example of the second case
offhand.

You might want to check out this book, which has a chapter on using
cursors:

http://www.sql.co.il/books/advtsql/...rver%202000.htm

But notice the chapter title ("Server-Side Cursors -- the SQL of Last
Resort") - using cursors in application code (as opposed to
DBA/management code) is rather unusual, and generally not at all
desirable.

Simon|||Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually.

Simon Hayes wrote:
> Two reasons would be when you need to do something for each row in a
> result set (usually execute a stored procedure), or when you must
> process rows in a specific order.
> The first case is probably most common in DBA/admin scripts, for
> example to write a script which GRANTs execute permission on all stored
> procs in a database. I can't think of a good example of the second case
> offhand.
> You might want to check out this book, which has a chapter on using
> cursors:
> http://www.sql.co.il/books/advtsql/...rver%202000.htm
> But notice the chapter title ("Server-Side Cursors -- the SQL of Last
> Resort") - using cursors in application code (as opposed to
> DBA/management code) is rather unusual, and generally not at all
> desirable.
> Simon|||Hi
Cursors are mainly used to traverse reach row in the result of a query.

If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||sorry but why not use 'where' or 'if' to check each row?|||Exactly. Chandra's statement is a bit ambiguous. Most of the time you
*can* conditionally perform an operation for each row using a WHERE
clause or using some combination of a WHERE clause and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.

The other sensible application for a cursor is the one Simon mentioned
- administrative and management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.

--
David Portas
SQL Server MVP
--|||(chrisandkayenolan@.gmail.com) writes:
> Cursors are a bad idea to use as they hold locks on tables for their
> duration. They are also not necessary as you can use set rowcount 1 to
> loop through records individually.

SET ROWCOUNT 1 is probably the worst form of iteration you can do. That
was what we did back in 4.x days, and it was no fun at all. When you
need to iterate, cursors are indeed the way to do.

Whether cursors hold locks or not, depends on what sort of cursor
you use. The default cursor type is a keyset cursor, a creature I
have never fully understood. Add INSENSITIVE before CURSOR, and you
don't have to worry. The result set for the cursor is fixed once
for all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Wednesday, March 21, 2012

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.

Tuesday, March 20, 2012

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.

Sunday, February 19, 2012

CSV to SQL Server import

Iam an intermediate stage programmer to the world of .Net

Can anyone show me how to import a CSV file to a table in the SQL Server database with ASP.Net and VB.Net or even C#.Net ??

Please post any code snippets that you have.

Thank You.dn,
Do a search on SQL DTS

KJ