Get a List of Output Columns on Script Transformation

I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?

I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?

I was able to get a specific column by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

MsgBox("Column Count: " & o.OutputColumnCollection.Count)

MsgBox("Colum: " & o.OutputColumnCollection.FindObjectByID(139).Name)

But Is there a way to just loop through all the ids? I have to know the id to stick in findobjectbyid(x). Can i do a loop and have x = all the ids? Additionally the 88 is hard code value... is there a way to get the current or specific output set?

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postion.

|||

Ok... sorry about the multiple posts here... but I got some more logic to work...

I was able to get able to get columns by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

For Each col As IDTSOutputColumn90 In o.OutputColumnCollection

MsgBox("Name: " & col.Name)

Next

So the updated question is this...

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postions value. I can't find how to set the column value equal to something while i loop through it. Additionally... is there a reference or good way to get the data type? When i use the col.datatype is returns a number vs description. I want to have logic when it set the value to do coverts based on the data type.

Generating an audit trail in SQLS 2000

Hi,
Following on from a recent post regarding Identity fields with David Portas,
I was discussing a few issues with a collegue and an interesting issue was
raised.
Namely, creating an audit trail in which every row is assigned a unique,
gapless sequential reference. For example, our accountancy package (Sage)
has an audit trail in which every transaction has a numbered reference. Each
number is unique and there are no missing numbers. Obviously some
transactions are cancelled, others may not be assigned in exactly the order
that they were created, etc etc.
I'm just wondering how I would achieve such a thing in SQLS. From talking
with David Portas in my last thread I understand that IDENTITY is not the
way ot achieve this, but I'm curious as to how exactly to achieve this in a
multi-user concurrent environment?
For example, have a unique ID table in which numbers are assigned as they
are required - although I imagine that this would require extensive use of
transactions and locking...
Alternatively would be to have an ON INSERT trigger which simply assigns the
last row reference plus one to the reference field?
Any pointers / sites / etc?
I appreciate that this is a big vague but it's something that I can see
being very useful in the near future.
Any and all advice is gratefully received.
Regards
Chris.One possible solution is at
http://solidqualitylearning.com/blo...04/04/446.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Chris Strug" <hotmail@.solace1884.com> wrote in message
news:%23r5dC30PFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Following on from a recent post regarding Identity fields with David
Portas,
> I was discussing a few issues with a collegue and an interesting issue was
> raised.
> Namely, creating an audit trail in which every row is assigned a unique,
> gapless sequential reference. For example, our accountancy package (Sage)
> has an audit trail in which every transaction has a numbered reference.
Each
> number is unique and there are no missing numbers. Obviously some
> transactions are cancelled, others may not be assigned in exactly the
order
> that they were created, etc etc.
> I'm just wondering how I would achieve such a thing in SQLS. From talking
> with David Portas in my last thread I understand that IDENTITY is not the
> way ot achieve this, but I'm curious as to how exactly to achieve this in
a
> multi-user concurrent environment?
> For example, have a unique ID table in which numbers are assigned as they
> are required - although I imagine that this would require extensive use of
> transactions and locking...
> Alternatively would be to have an ON INSERT trigger which simply assigns
the
> last row reference plus one to the reference field?
> Any pointers / sites / etc?
> I appreciate that this is a big vague but it's something that I can see
> being very useful in the near future.
> Any and all advice is gratefully received.
> Regards
> Chris.
>
>|||If you don't mind having gaps in numbers i.e reserve IDs before you
write. Sticking the next available ID in a one row table is good
performance wise. You only lock one very small table rather than the
table you are inserting data into.
The processes that will write can take their time because they have
their unique IDs already and so don't stop other writes reserving
their IDs. Also you can reserve 100 IDs very easily at no cost just by
adding 100.
But you will get gaps, after reserving ID if your write fails.

Generate Schema

Hello, how do I generate a schema of a database in sql server 2005? I want to
see a graphic representation of all the table and fields, what the primary
keys are, relathionships, and so forth.
thanks
toney
You can also use Generate Script. In SSMS, right click on a database,
Tasks\Generate Scripts...
Ekrem ?nsoy
"toney" <toney@.discussions.microsoft.com> wrote in message
news:DEF49EE6-73D2-4251-AEDB-AF861B39E928@.microsoft.com...
> Hello, how do I generate a schema of a database in sql server 2005? I want
> to
> see a graphic representation of all the table and fields, what the primary
> keys are, relathionships, and so forth.
> thanks
> --
> toney
>
|||Ekrem, how would the Generate Scripts wizard generate a visual
reporesentation of the structure of the DB? I see how it creates samples code.
toney
"Ekrem ?nsoy" wrote:

> You can also use Generate Script. In SSMS, right click on a database,
> Tasks\Generate Scripts...
> --
> Ekrem ?nsoy
>
> "toney" <toney@.discussions.microsoft.com> wrote in message
> news:DEF49EE6-73D2-4251-AEDB-AF861B39E928@.microsoft.com...
>
|||On Nov 8, 5:02 pm, toney <to...@.discussions.microsoft.com> wrote:
> Hello, how do Igeneratea schema of a database insqlserver 2005? I want to
> see a graphic representation of all the table and fields, what the primary
> keys are, relathionships, and so forth.
> thanks
> --
> toney
You can create a database diagram to show relationships. if you
expand the database node in OE, you'll see a "database diagrams"
folder. right click on that to create a diagram.
also you might find this tool useful: http://www.elsasoft.org

Generate custom primary key fields

Hi all,

I've recently dabbled into the world of Stored procedures and Triggers but am having some issues trying to implement some functionality.

What I want to accomplish is to either create a SP or Trigger (I'm still trying to figure out the prime differece between them) that takes values in, concatenates them, adds an increment and then saves it into the assigned column.

Say for example I have a company name, and a region
Name:'Traders Inc"
Region:KEN (Kenya).

What I want my sp to do is take the first four letters of the name, add a dash, concat the region and add an increment starting from 001 thereby giving me TRAD-KEN-001. Incase I put in another entry, it should do the same and start it at 001 as well. Only when the first two variables are the same should the number be incremented. I.e if we have another entry
Name: Tradine Jewelers",
Region: Kenya;

It should calculate and give me a value TRAD-KEN-002. This is what I want my sp to do.

I can experiment and get the concats etc to work but my confusion kicks in on how I integrate 'If' statements and how SQL actually manipulates vinputted values and where (and how) I get the value of the last increment.

Values will be passed in from my Business Logic Layer but I am totally lost after that i.e. Should I use a SP or Trigger, how do I get the values into one column, how do I integrate the IF statemnets to get the result that I am getting. The only part I understand is the Insert but thats about it.

Any help on flow, functions and commands would be wonderful with where the GO and EXECs should go in and how it will store variables temporarily.

Thanks in Advance.

I'd just like someone to set me off in the right direction.

Thanks.

|||

Here is one idea, perhaps still 'half baked', so take it as a seed, not a fully grown solution.

Column for Name
Column for Region
Column for Sequence

Column for Key Value (Concatenated fields); this column will have a UNIQUE INDEX -which will serve 'almost' as good as a Primary Key -but will allow the initial INSERT to succeed even with NULL values.

Use a Trigger (the difference between a Trigger and a Stored Procedure is that a Stored Procedure executes when called, whereas a Trigger automatically executes upon change to data in a table.)

Create an FOR INSERT Trigger. When a new row is inserted, search for the max(Sequence) WHERE MyTable.Name = inserted.Name AND MyTable.Region = Inserted.Region. Add one to that value, put it in the Sequence Column, and then concatenate the three columns together to make your key value. Store in the Key value column. (It may be necessary to use an INSTEAD OF Trigger rather than a FOR INSERT Trigger.)

I hope that this helps point you in a direction that will work for you.

|||Hi,

Assuming the table structure is like
create table table1 (AreaName varchar(100), Region varchar(100), CodeGenerated varchar(100))

You can use something like this or you can convert it into trigger.

CREATE PROCEDURE InsertData
@.AreaName VARCHAR(100),
@.Region VARCHAR(100)
AS
BEGIN
DECLARE @.CodeGenerated VARCHAR(100), @.Counter varchar(10)

SET @.CodeGenerated = substring(@.Areaname, 1, 4) + '-' + substring(@.Region,1,3) + '-'

SELECT @.Counter = ISNULL(MAX(REPLACE(CodeGenerated,@.CodeGenerated, '')),0)+1 FROM Table1
WHERE AreaName = @.AreaName AND Region = @.region

SET @.CodeGenerated = UPPER(@.CodeGenerated) + REPLICATE('0',3-len(@.Counter)) + convert(VARCHAR,@.Counter)

INSERT table1 VALUES(@.AreaName, @.Region, @.CodeGenerated)
END

-- Code to execute stored procedure
EXEC InsertData @.AreaName = 'Traders Inc', @.Region = 'KEN'

SELECT * FROM table1

--Neeraj--|||

Awesome. Thanks alot for your responses.

Neeraj, by looking at the code and my newbie databases status, you just saved me 1 or 2 days of trial and error with this one.Code works perfect. Thanks a million!

I need to get my hands on some good sql books. Any book recommendations or links which I can start off with?

|||Hi kundalani,

Good to know that your problem is solved.

As per forums guidelines you should close the topic if it is able to solve your problem :)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17861&SiteID=1