|
|
You're in the
sql server dts
group:SSIS - Column Iteration in Script Component
sql server dts:
When you say iterate? and what do you mean by a recordset row?
You can loop over a recordset in the Control Flow. You can attach a source to the Script component, have it ast as a transform and you can perform operations on the row. What exactly do you want to do as there may be better ways to do it then that script component. -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - You thought DTS was good. here we show you the new stuff. www.konesans.com - Consultancy from the people who know [quoted text, click to view] "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message news:9527CDF1-685A-4A9F-8AAB-E69F37230418@microsoft.com... > Is it possible to iterate through the columns (or fields) in a recordset > row > using the script component? I am working with a table that has over 50 > columns of similar type data fields and need to perform some calculations > on > the fields prior to normalizing the table.
Is it possible to iterate through the columns (or fields) in a recordset row
using the script component? I am working with a table that has over 50 columns of similar type data fields and need to perform some calculations on
Currently we are looping through the rows in the script component from a sql
table as the source. Some of the columns are modified as we move through the transformation, basically unpivoting the data (using the unpivot control works but some things still require some code). Because there are so many columns (e.g., Interval_1, Inteval_2,...., Interval50) it would be great if we could refer to the fields as row(colno).value instead of row.Interval_1. I looked around and found some examples using the pipeline buffer but we couldn't get it to work. Of course part of the problem is that we can't figure out how to do line-by-line debugging yet. Does this make any sense yet? [quoted text, click to view] "Allan Mitchell" wrote:
> When you say iterate? and what do you mean by a recordset row? > > You can loop over a recordset in the Control Flow. > > You can attach a source to the Script component, have it ast as a transform > and you can perform operations on the row. > > What exactly do you want to do as there may be better ways to do it then > that script component. > > > -- > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.SQLDTS.com - The site for all your DTS needs. > www.SQLIS.com - You thought DTS was good. here we show you the new stuff. > www.konesans.com - Consultancy from the people who know > > > "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message > news:9527CDF1-685A-4A9F-8AAB-E69F37230418@microsoft.com... > > Is it possible to iterate through the columns (or fields) in a recordset > > row > > using the script component? I am working with a table that has over 50 > > columns of similar type data fields and need to perform some calculations > > on > > the fields prior to normalizing the table. > >
Thanks. But what about the line-by-line debugging of the script component?
[quoted text, click to view] "Allan Mitchell" wrote:
> I have to say that I have never used an ordinal number for columns simply > because although in DTS it was faster, it was always prone to being > invlidated and if you can do the ordinal number thing in SSIS I would > imagine it suffers the same fate. > > > -- > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.SQLDTS.com - The site for all your DTS needs. > www.SQLIS.com - You thought DTS was good. here we show you the new stuff. > www.konesans.com - Consultancy from the people who know > > > "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message > news:4DB0773B-F6F1-4E0E-9F01-FA90A59F2745@microsoft.com... > > Currently we are looping through the rows in the script component from a > > sql > > table as the source. Some of the columns are modified as we move through > > the > > transformation, basically unpivoting the data (using the unpivot control > > works but some things still require some code). > > Because there are so many columns (e.g., Interval_1, Inteval_2,...., > > Interval50) it would be great if we could refer to the fields as > > row(colno).value instead of row.Interval_1. I looked around and found some > > examples using the pipeline buffer but we couldn't get it to work. Of > > course > > part of the problem is that we can't figure out how to do line-by-line > > debugging yet. > > > > Does this make any sense yet? > > > > > > > > > > "Allan Mitchell" wrote: > > > >> When you say iterate? and what do you mean by a recordset row? > >> > >> You can loop over a recordset in the Control Flow. > >> > >> You can attach a source to the Script component, have it ast as a > >> transform > >> and you can perform operations on the row. > >> > >> What exactly do you want to do as there may be better ways to do it then > >> that script component. > >> > >> > >> -- > >> > >> > >> > >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > >> www.SQLDTS.com - The site for all your DTS needs. > >> www.SQLIS.com - You thought DTS was good. here we show you the new > >> stuff. > >> www.konesans.com - Consultancy from the people who know > >> > >> > >> "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message > >> news:9527CDF1-685A-4A9F-8AAB-E69F37230418@microsoft.com... > >> > Is it possible to iterate through the columns (or fields) in a > >> > recordset > >> > row > >> > using the script component? I am working with a table that has over 50 > >> > columns of similar type data fields and need to perform some > >> > calculations > >> > on > >> > the fields prior to normalizing the table. > >> > >> > >> > >
I have to say that I have never used an ordinal number for columns simply
because although in DTS it was faster, it was always prone to being invlidated and if you can do the ordinal number thing in SSIS I would imagine it suffers the same fate. -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - You thought DTS was good. here we show you the new stuff. www.konesans.com - Consultancy from the people who know [quoted text, click to view] "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message news:4DB0773B-F6F1-4E0E-9F01-FA90A59F2745@microsoft.com... > Currently we are looping through the rows in the script component from a > sql > table as the source. Some of the columns are modified as we move through > the > transformation, basically unpivoting the data (using the unpivot control > works but some things still require some code). > Because there are so many columns (e.g., Interval_1, Inteval_2,...., > Interval50) it would be great if we could refer to the fields as > row(colno).value instead of row.Interval_1. I looked around and found some > examples using the pipeline buffer but we couldn't get it to work. Of > course > part of the problem is that we can't figure out how to do line-by-line > debugging yet. > > Does this make any sense yet? > > > > > "Allan Mitchell" wrote: > >> When you say iterate? and what do you mean by a recordset row? >> >> You can loop over a recordset in the Control Flow. >> >> You can attach a source to the Script component, have it ast as a >> transform >> and you can perform operations on the row. >> >> What exactly do you want to do as there may be better ways to do it then >> that script component. >> >> >> -- >> >> >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) >> www.SQLDTS.com - The site for all your DTS needs. >> www.SQLIS.com - You thought DTS was good. here we show you the new >> stuff. >> www.konesans.com - Consultancy from the people who know >> >> >> "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message >> news:9527CDF1-685A-4A9F-8AAB-E69F37230418@microsoft.com... >> > Is it possible to iterate through the columns (or fields) in a >> > recordset >> > row >> > using the script component? I am working with a table that has over 50 >> > columns of similar type data fields and need to perform some >> > calculations >> > on >> > the fields prior to normalizing the table. >> >> >>
Debugging the Script component as a transform is not currently possible. it
will allow you to set a breakpoint but when the Data Floe task runs the breakpoint is silently removed. I have asked MS to make this more apprent and less silent. -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - You thought DTS was good. here we show you the new stuff. www.konesans.com - Consultancy from the people who know [quoted text, click to view] "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message news:92D351A3-F4E5-4F32-96B2-09806E21A278@microsoft.com... > Thanks. But what about the line-by-line debugging of the script component? > > > "Allan Mitchell" wrote: > >> I have to say that I have never used an ordinal number for columns >> simply >> because although in DTS it was faster, it was always prone to being >> invlidated and if you can do the ordinal number thing in SSIS I would >> imagine it suffers the same fate. >> >> >> -- >> >> >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) >> www.SQLDTS.com - The site for all your DTS needs. >> www.SQLIS.com - You thought DTS was good. here we show you the new >> stuff. >> www.konesans.com - Consultancy from the people who know >> >> >> "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message >> news:4DB0773B-F6F1-4E0E-9F01-FA90A59F2745@microsoft.com... >> > Currently we are looping through the rows in the script component from >> > a >> > sql >> > table as the source. Some of the columns are modified as we move >> > through >> > the >> > transformation, basically unpivoting the data (using the unpivot >> > control >> > works but some things still require some code). >> > Because there are so many columns (e.g., Interval_1, Inteval_2,...., >> > Interval50) it would be great if we could refer to the fields as >> > row(colno).value instead of row.Interval_1. I looked around and found >> > some >> > examples using the pipeline buffer but we couldn't get it to work. Of >> > course >> > part of the problem is that we can't figure out how to do line-by-line >> > debugging yet. >> > >> > Does this make any sense yet? >> > >> > >> > >> > >> > "Allan Mitchell" wrote: >> > >> >> When you say iterate? and what do you mean by a recordset row? >> >> >> >> You can loop over a recordset in the Control Flow. >> >> >> >> You can attach a source to the Script component, have it ast as a >> >> transform >> >> and you can perform operations on the row. >> >> >> >> What exactly do you want to do as there may be better ways to do it >> >> then >> >> that script component. >> >> >> >> >> >> -- >> >> >> >> >> >> >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) >> >> www.SQLDTS.com - The site for all your DTS needs. >> >> www.SQLIS.com - You thought DTS was good. here we show you the new >> >> stuff. >> >> www.konesans.com - Consultancy from the people who know >> >> >> >> >> >> "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in >> >> message >> >> news:9527CDF1-685A-4A9F-8AAB-E69F37230418@microsoft.com... >> >> > Is it possible to iterate through the columns (or fields) in a >> >> > recordset >> >> > row >> >> > using the script component? I am working with a table that has over >> >> > 50 >> >> > columns of similar type data fields and need to perform some >> >> > calculations >> >> > on >> >> > the fields prior to normalizing the table. >> >> >> >> >> >> >> >> >>
Log it on MSDN Product Feedback.. The pre-generated code does not allow
this, but it would be a simple change to include an Item indexer. In fact it is there, but not exposed, and you cannot edit the generated code to fix it, although this would probably be a bad idea since it would get overwritten. [quoted text, click to view] "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message news:4DB0773B-F6F1-4E0E-9F01-FA90A59F2745@microsoft.com... > Currently we are looping through the rows in the script component from a > sql > table as the source. Some of the columns are modified as we move through > the > transformation, basically unpivoting the data (using the unpivot control > works but some things still require some code). > Because there are so many columns (e.g., Interval_1, Inteval_2,...., > Interval50) it would be great if we could refer to the fields as > row(colno).value instead of row.Interval_1. I looked around and found some > examples using the pipeline buffer but we couldn't get it to work. Of > course > part of the problem is that we can't figure out how to do line-by-line > debugging yet. > > Does this make any sense yet? > > > > > "Allan Mitchell" wrote: > >> When you say iterate? and what do you mean by a recordset row? >> >> You can loop over a recordset in the Control Flow. >> >> You can attach a source to the Script component, have it ast as a >> transform >> and you can perform operations on the row. >> >> What exactly do you want to do as there may be better ways to do it then >> that script component. >> >> >> -- >> >> >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) >> www.SQLDTS.com - The site for all your DTS needs. >> www.SQLIS.com - You thought DTS was good. here we show you the new >> stuff. >> www.konesans.com - Consultancy from the people who know >> >> >> "Gary Michaels" <GaryMichaels@discussions.microsoft.com> wrote in message >> news:9527CDF1-685A-4A9F-8AAB-E69F37230418@microsoft.com... >> > Is it possible to iterate through the columns (or fields) in a >> > recordset >> > row >> > using the script component? I am working with a table that has over 50 >> > columns of similar type data fields and need to perform some >> > calculations >> > on >> > the fields prior to normalizing the table. >> >> >>
I have a similar issue, I have 26 columns in the input buffer and based on
some conditions I need to copy the data from the input0buffer to an output1buffer. I would like to be able to loop through the input buffer with a for loop similar to what would be used in ado "for i = 0 to rs.fields.count -1" etc. to copy them instead I am currently doing the outrow.addrow() : outrow.field = inrow.field. I am very new to .Net and though I may be wrong it seems a huge miss to me. [quoted text, click to view] "Gary Michaels" wrote:
> Is it possible to iterate through the columns (or fields) in a recordset row > using the script component? I am working with a table that has over 50 > columns of similar type data fields and need to perform some calculations on
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |