gets inserted twice

Gets inserted twice int TBL_NOTICES??

--------------------------
DECLARE @.IDRess integer, @.FromDate dateTime, @.ToDate dateTime, @.LateCounter integer

SET @.FromDate = GETDATE() - 30
SET @.ToDate = GETDATE()

DECLARE LateCountCursor CURSOR
FOR
SELECT DISTINCT IDRess, COUNT(Late) AS LateCount
FROM TBL_EXTERNAL_ENTRY
WHERE (DateInvolved BETWEEN GETDATE() - 30 AND GETDATE()) AND (Late = 1)
GROUP BY IDRess
ORDER BY IDRess

OPEN LateCountCursor

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM LateCountCursor
INTO @.IDRess, @.LateCounter

IF @.LateCounter >= 1
BEGIN
DECLARE @.late int, @.dept int, @.sup int, @.poste int, @.patron int
SELECT @.late = (SELECT ID_NOTICE FROM TBL_NOTICE_TYPE WHERE NoticeName = 'Late')
DECLARE RessCursor CURSOR
FOR
SELECT IDDepartement, IDContremaitre, IDPoste, IDPatron FROM TBL_RESSOURCES WHERE IDInterne = @.IDRess
OPEN RessCursor
IF @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM RessCursor
INTO @.dept, @.sup, @.poste, @.patron
END
CLOSE RessCursor
DEALLOCATE RessCursor

INSERT INTO TBL_NOTICES (IDInterne, IDDepartement, DateInfraction, DateAvis, IDMotif, NotesMotif, IDSuperieur, IDPoste, IDRedacteur, IDPatron, InscDossier, SuspSansSolde, Congediement, Autres)
VALUES (@.IDRess, @.dept, @.ToDate, @.ToDate, @.late, 'SQL Server LateCheck Job', @.sup, @.poste, 99999, @.patron, 0, 0, 0, 0)

DECLARE @.msg varchar(100)
SET @.msg = 'Infraction de retard trop frquents pour employee # ' + @.IDRess
EXEC [master].[dbo].xp_startmail
EXEC [master].[dbo].xp_sendmail @.recipients = 'gdo',
@.message = @.msg,
@.subject = 'Infraction [RETARD]'
END

END
CLOSE LateCountCursor
DEALLOCATE LateCountCursor

--------------------------

Also, does anyone know about a good T-SQL Editor with wich I could step through the execution of a function...

gdogdo

the query you have posted won't even write two records. It won't actually do anything.

Your 'FETCH NEXT FROM LateCountCursor ' line comes after your 'WHILE @.@.FETCH_STATUS = 0' line, so the @.@.FetchStatus will be -1 (try print @.@.Fetch_Status to see). Because it is -1, the compiler will not even enter the while loop, therefore none of the code will be performed. Your 'FETCH NEXT FROM LateCountCursor ' line should come before the 'WHILE @.@.FETCH_STATUS = 0' line to ensure that the code in the while loop is actually implemented.

As far as I know there is no tool that will allow you to step through TSQL code. SQL Server creates a query plan for the code before it is actually exectuted, and the sequence of events in that query plan does not neccesarily match the sequence of code.|||Lwaker,
Actually, @.@.FETCH_STATUS could be 0 depending on what
the status of the last FETCH was before gdo's code block was
started. Since @.@.FETCH_STATUS is global to all cursors in a
connection, it might be valid upon entry to tis code block.

I agree the condition checking of @.@.FETCH_STATUS needs to
be changed as you mentioned. Gdo might get two inserts performed
one call, then zero the next with the current setup.|||If I understand correctly... 'Fetch Next' is required before the while loop and also inside the while loop after the insert statement.

thanks,

By the way, about something else...

we installed outlook on the server which has SQL Server in order to use SQL Mail.

Server: Windows 2k and SQL Server 2k (They do not use Exchange Server, don't know if it has something something to do with it...)

The address book has been imported in outlook but we do not see outlook in the combobox of SQL Mail Configuration?

Any ideas?

gdo

Get the Highest value.

Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!select max(number) as m from daTable|||thanks a bunch!!!!|||select max(number) as m from daTable
Truly, an elegant piece of coding. Sheer genious for its blend of brevity and functionality. I shall have to use this in my next project.

Get the 3 max age

Hi,
I have a table with
ID int,
Name varchar,
Age int
how could i make an stored procedure to get the three old persons from
database?
--
Thanks
Regards.
JosemaTry:
select top 3 * from TheTable
order by Age desc|||Wouldn't it make more sense to store date of birth rather than age? How
will you keep the Age column up to date?
SELECT TOP 3 WITH TIES id, name, age
FROM YourTable
ORDER BY age DESC
Presumably there could be several people with the same age so you may
well get more than 3 rows returned. Do you have a rule for the 3 you
want in the event of ties? You can add other columns to the ORDER BY
clause to narrow down the selection.
David Portas
SQL Server MVP
--|||Thanks bd,
Regards.
Josema
"bd" wrote:

> Try:
> select top 3 * from TheTable
> order by Age desc
>|||> Wouldn't it make more sense to store date of birth rather than age? How
> will you keep the Age column up to date?
Heh... run a job every day that joins against a linked server (e.g. the IRS
database) on SSN, and checks if their DOB has the same day and month as
today. I'm sure it gets even more interesting on Feb. 29. :-)|||>> Heh... run a job every day that joins against a linked server
And for those who are into astrological beliefs, the job has to be scheduled
to run every minute or perhaps every sec!
Anith

Get records count from SQL cursor

Have you thought of declaring a int then adding one to it
per record ?
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan

>--Original Message--
>Hi experts,
>I have created a SQL cursor for records processing in a
stored procedure. I
>probably can use the @.@.Cursor_Rows function in order to
obtain total rows of
>record contained inside the cursor. But once I declare
the cursor as
>FAST_FORWARD, it always return me -1. I need to declare
the cursor as
>FAST_FORWARD as it really helps in tuning the
performance. Else my stored
>procedure will take longer time to execute.
>Any other way I can use to get the total records being
returned? I've tried
>to signal another SQL statement to perform the records
count but this seems
>to create redundant overhead. I believe if I can do
anything to existing
>cursor without having extra Select Count statement, it
would help to reduce
>unnecessary processing and shorten the overall time
required.
>Really appreciate for any advice or suggestion. Thanks a
lot.
>.
>I've tested to append 1 to an int variable each time while looping the curso
r
but the effect is not so significant to boost the performance...Anyway,
thanks for the suggestion...
"Peter The Spate" wrote:

> Have you thought of declaring a int then adding one to it
> per record ?
> Peter
> "Status quo, you know, that is Latin for "the mess we're
> in."
> Ronald Reagan
>
> stored procedure. I
> obtain total rows of
> the cursor as
> the cursor as
> performance. Else my stored
> returned? I've tried
> count but this seems
> anything to existing
> would help to reduce
> required.
> lot.
>

Get one row from detail/child table

Hi,

-- ddl
/*
create table #tmp (col1 int);
insert into #tmp values(1);
insert into #tmp values(2);
insert into #tmp values(3);

create table #tmpChild (col1 int, fkCol int, Num int);
insert into #tmpChild values(1,1,3);
insert into #tmpChild values(2,1,2);
insert into #tmpChild values(3,2,1);
*/

-- get parent and child data (outer join)
select *
from #tmp t Left Outer JOIN #tmpChild tC
ON t.col1 = tC.fkCol

-- resultset
/*
/col1 col1 fkCol Num
---- ---- ---- ----
1 1 1 3
1 2 1 2
2 3 2 1
3 NULL NULL NULL

(4 row(s) affected)
*/

-- desired resultset
/*
/col1 col1 fkCol Num
---- ---- ---- ----
1 1 1 3
-- eleminate next row, want only Max(Num) row from the child tbl with
same FK (parentID)
-- 1 2 1 2
2 3 2 1
3 NULL NULL NULL
*/

In other words, desired results is as follows:
/*
/col1 col1 fkCol Num
---- ---- ---- ----
1 1 1 3
2 3 2 1
3 NULL NULL NULL
*/

How to accomplish this task? ENV: MS SQL Server 2000

TIAAdd the following to the end of your query:

...
AND tC.Num = ( SELECT MAX( t2.Num )
FROM #tmpChild t2
WHERE t2.fkCol = tC.fkCol ) ;

--
Anith

Get next unique ID from a table before insert @@identity / Sequence

How do I get the next int value for a column before I do an insert in
MY SQL Server 2000? I'm currently using Oracle sequence and doing
something like:

select seq.nextval from dual;

Then I do my insert into 3 different table all using the same uniqueID.

I can't use the @.@.identity function because my application uses a
connection pool and it's not garanteed that a connection won't be used
by another request so under a lot of load there could be major problems
and this doens't work:

insert into <table>;
select @.@.identity;

This doesn't work because the select @.@.identity might give me the value
of an insert from someone else's request.

Thanks,

BrentOn 16 Mar 2005 14:58:25 -0800, brent.ryan@.gmail.com wrote:

>How do I get the next int value for a column before I do an insert in
>MY SQL Server 2000? I'm currently using Oracle sequence and doing
>something like:
>select seq.nextval from dual;
>Then I do my insert into 3 different table all using the same uniqueID.
>I can't use the @.@.identity function because my application uses a
>connection pool and it's not garanteed that a connection won't be used
>by another request so under a lot of load there could be major problems
>and this doens't work:
>insert into <table>;
>select @.@.identity;
>This doesn't work because the select @.@.identity might give me the value
>of an insert from someone else's request.
>Thanks,
>Brent

Hi Brent,

Create a stored procedure that starts a transaction, inserts into the
first table, retrieves the identity value used (with SCOPE_IDENTITY, the
recommended method in SQL Server 2000), uses it to insert data into the
other two table, then commits the transaction (or rolls it back if
anything went wrong).

Calling the server three times for three inserts is not only incurring
the overhead of more roundtrips then necessary, you also run the risk of
getting corrupted data: if one insert fails and the others succeed,
you'll have incomplete data in your database. Always include related
modifications in a transaction. And if each call to the database can use
a different connection, then the complete operation, from start to end
of transaction, needs to be done in one call, as transactions are tied
to the connection.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(brent.ryan@.gmail.com) writes:
> insert into <table>;
> select @.@.identity;
> This doesn't work because the select @.@.identity might give me the value
> of an insert from someone else's request.

No, @.@.identity is local to the connection, so it cannot be someone
else's value. Well, if you submit to queries and close your connection
in between, it won't work, but that would be poor practice anyway.

Hugo's suggestion of using a stored procedure is an excellent idea.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 17 Mar 2005 22:57:45 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (brent.ryan@.gmail.com) writes:
>> insert into <table>;
>> select @.@.identity;
>>
>> This doesn't work because the select @.@.identity might give me the value
>> of an insert from someone else's request.
>No, @.@.identity is local to the connection, so it cannot be someone
>else's value. Well, if you submit to queries and close your connection
>in between, it won't work, but that would be poor practice anyway.
>Hugo's suggestion of using a stored procedure is an excellent idea.

Excuse me for butting in here, Erland, but there is one 'little'
problem that I have found with @.@.IDENTITY that I can't see referred to
anywhere, and that anyone relying on it should know about, and that is
that @.@.IDENTITY can return unexpected values in certain circumstances.

In the supplied example:

insert into <table>
select @.@.identity

BEAWRE!
If there is a trigger fired during the insert on <table>, and the
trigger performs an insert itself, then @.@.IDENTITY will return the ID
from the Trigger's insert, not the <table> insert.

This caused me many to lose much more hair than I can afford!

It behaves this way in SQL Server 7, and 2000.

Here is a script to create a test data base:
(Make a new blank database, I called it "Test")

=============================
/****** Object: Table [dbo].[MainTable] Script Date: 18/03/2005
3:10:38 PM ******/
CREATE TABLE [dbo].[MainTable] (
[MainTableId] [int] IDENTITY (1, 1) NOT NULL ,
[LongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TriggerTable] Script Date: 18/03/2005
3:10:39 PM ******/
CREATE TABLE [dbo].[TriggerTable] (
[TriggerTableId] [int] IDENTITY (666, 1) NOT NULL ,
[TriggerRowLongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TriggerTable] WITH NOCHECK ADD
CONSTRAINT [PK_TriggerTable] PRIMARY KEY CLUSTERED
(
[TriggerTableId]
) ON [PRIMARY]
GO
/****** Object: Stored Procedure dbo.Test_sp Script Date:
18/03/2005 3:10:39 PM ******/
CREATE PROCEDURE dbo.Test_sp
AS
INSERT INTO MainTable (LongName) VALUES ('TestLongName')
SELECT @.@.IDENTITY
GO
/****** Object: Trigger dbo.MainTable_Trigger1 Script Date:
18/03/2005 3:10:39 PM ******/
CREATE TRIGGER MainTable_Trigger1
ON dbo.MainTable
FOR INSERT,UPDATE,DELETE
AS
INSERT INTO TriggerTable (TriggerRowLongName) VALUES ('Stuff')
GO
=============================

Then, if one executes [Test_sp] in Query Analyser,

EXEC Test_sp

the returned @.@.IDENTITY is not 1, as you would expect, (this is ID of
the new MainTable row), but 666, which is the ID of the row inserted
via the trigger!
(I seeded this table's identity to begin at 666, in order to show up
clearly)

I would be interested if you were aware of this tiny problemette.|||Michael Gray (fleetg@.newsguy.spam.com) writes:
> Excuse me for butting in here, Erland, but there is one 'little'
> problem that I have found with @.@.IDENTITY that I can't see referred to
> anywhere, and that anyone relying on it should know about, and that is
> that @.@.IDENTITY can return unexpected values in certain circumstances.
> In the supplied example:
> insert into <table>
> select @.@.identity
> BEAWRE!
> If there is a trigger fired during the insert on <table>, and the
> trigger performs an insert itself, then @.@.IDENTITY will return the ID
> from the Trigger's insert, not the <table> insert.

Yes, this is a correct observation. For this reason, you should use
scope_identity() instead. This function was introduced in SQL 2000.

scope_identity() returns the most recently generated IDENTITY in the
current scope, that is a trigger, stored procedure, block of dynamic
SQL etc.

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

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

Get Next Number

I have a table called e_next_number that holds a variety of different IDs
currently in use. The columns are NumberType (nchar) and ID (int). My
question is, what is the best way to insure a inquire number is returned,
currently my stored procedure looks like this:
CREATE PROCEDURE _GetNextNumber
@.NumberType nchar(10),
@.NextNumber int OUTPUT
AS
SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype = @.NumberType
UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
return
TIADisregard, I posted in the wrong group. Sorry.
"MikeB" <m@.nospam.com> wrote in message
news:uj6gBrHrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
>currently in use. The columns are NumberType (nchar) and ID (int). My
>question is, what is the best way to insure a inquire number is returned,
>currently my stored procedure looks like this:
> CREATE PROCEDURE _GetNextNumber
> @.NumberType nchar(10),
> @.NextNumber int OUTPUT
> AS
> SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype => @.NumberType
> UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
> return
> TIA
>
>

Get Next Number

I have a table called e_next_number that holds a variety of different IDs
currently in use. The columns are NumberType (nchar) and ID (int). My
question is, what is the best way to insure a inquire number is returned,
currently my stored procedure looks like this:
CREATE PROCEDURE _GetNextNumber
@.NumberType nchar(10),
@.NextNumber int OUTPUT
AS
SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
@.NumberType
UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
return
TIA
Disregard, I posted in the wrong group. Sorry.
"MikeB" <m@.nospam.com> wrote in message
news:uj6gBrHrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
>currently in use. The columns are NumberType (nchar) and ID (int). My
>question is, what is the best way to insure a inquire number is returned,
>currently my stored procedure looks like this:
> CREATE PROCEDURE _GetNextNumber
> @.NumberType nchar(10),
> @.NextNumber int OUTPUT
> AS
> SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
> @.NumberType
> UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
> return
> TIA
>
>

Get Next Number

I have a table called e_next_number that holds a variety of different IDs
currently in use. The columns are NumberType (nchar) and ID (int). My
question is, what is the best way to insure a inquire number is returned,
currently my stored procedure looks like this:
CREATE PROCEDURE _GetNextNumber
@.NumberType nchar(10),
@.NextNumber int OUTPUT
AS
SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
@.NumberType
UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
return
TIADisregard, I posted in the wrong group. Sorry.
"MikeB" <m@.nospam.com> wrote in message
news:uj6gBrHrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
>currently in use. The columns are NumberType (nchar) and ID (int). My
>question is, what is the best way to insure a inquire number is returned,
>currently my stored procedure looks like this:
> CREATE PROCEDURE _GetNextNumber
> @.NumberType nchar(10),
> @.NextNumber int OUTPUT
> AS
> SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
> @.NumberType
> UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
> return
> TIA
>
>

Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.

Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.

Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.