Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> > I want to set a server up to be able to reference itself
> > via an alias in a 4-part name. Let us call the server
> > SQLSERVER and its alias BILLING. The motive for this is
> > to be able to, move the so-referenced databases to another
> > server when I want to and reference that truely remote
> > server by the BILLING alias.
> >
> > I have the alias set-up correctly and the server linked
> > via that alias with the proper security contexts.
> >
> > When I query:
> >
> > select * from [billing].master.dbo.sysdatabases
> >
> > I get the resultset I want.
> >
> > When I query:
> >
> > begin tran
> > select * from [billing].master.dbo.sysdatabases
> > rollback tran
> >
> > I get the following error:
> >
> > Server: Msg 7391, Level 16, State 1, Line 2
> > The operation could not be performed because the OLE DB
> > provider 'SQLOLEDB' was unable to begin a distributed
> > transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> >
> > If I use BILLING to alias another server (a truely remote
> > server), the transactioned query works without error.
> >
> > I've came to understand this as a loopback linked server limitation
> > in that loopback linked servers cannot enlist in a distributed
> transaction.
> >
> > But, I made a table, x, on sqlserver.Master with one int column, x
> > I made a stored proc, spx, on sqlserver.Master that takes one parameter
> > and inserts it's value into table x
> >
> > On Sql Server I did the following
> >
> > Use Master
> > go
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 1
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 commit tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 1 and no errors so transaction worked.
> > go
> >
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 2
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 rollback tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 2 and no errors so transaction worked.
> > go
> >
> > begin tran
> > select * from [BILLING].Master.dbo.x
> > -- Saw same error as cited above as expected.
> > if @.@.trancount != 0 rollback tran
> >
> > What is going on? Transactions should have failed in stored proc usage
> > as well as a direct query if this were a loopback failure to enlist a
> > distributed transaction, shouldn't they?
> >
> >
> >
> >
>

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?
Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>
|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> transaction.
>

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> transaction.
>

Lookups and their error flows

So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.

I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.

Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.

Phil Brammer wrote:

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

Phil,

What do you mean with 'Can't do it'. Are you actually receiving an error?

I was able to set that up; two error outputs from lookup transformations going to an Union All; it seems to work; but that was with a simple test scenario I made. Yes, ErrorColumn and ErrorCode were there as well but is up to you to use them or remove them from the pipeline. What is actually no there is the lookup value but I would not expect to be since the lookup fail.

Another way to accomplish something like this is to configure lookups to 'ignore errors' and then at the end to use a conditional split to send the rows having nulls on the lookup values column to the error branch of the dataflow. Just a thought

Rafael Salas

|||User error... You made me think about it again, and when I looked at what I was trying to do, I realized my mistake. Never-the-less, what I was doing was taking the error "redirect" from the lookup and adding a new column with a derived column transformation. This was repeated on the other lookup error flows. Then my mistake (an accident!) was taking the *error* output of the derived column transformations instead of the data flow output and pushing them to the union all transformation.|||

oh, I see!. BTW, in my previous post I meant ignore (instead redirect) error as an alternative way. I corrected it though.

Rafael salas

sql

Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.
Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)

Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.

Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)

Lookup finds match on empty reference table

Hi all,

In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "

I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.

Does anyone have an idea why? I could'nt find anything about that in BOL.

Sbastien.

If the lookup is returning rows, yet now rows exists, it cannot be matching, the returned columns if any will be null. You can however allow this behaviour, by ignoring errors. A failed lookup match is classed as an error, and can be set under the Configure Error Outputs button/screen. See if you have set Ignore for errors.|||Indeed I have configured the error to "Ignore errors" but I thought it will simply avoid the package to fail if it couldn't find any match. I don't want to use Redirect rows either as I have nothing to do with the non matching rows.|||

Sebastian,

The solution here then is quite simple. Use a Conditional Split to filter out the columns that have NULL values in the lookup columns - thereby giving you the rows that yielded a match.

-Jamie

lookup and commit; How it works

Lookup and commit; How it works
I am importing data from text file and I have data lookup to check reference table.

If the reference row doesn't exist I want to add row to reference table, other wise row is added to the detail table.

I am using oledb destination to saving reference table row with rows per batch to 1 and maximum insert commit size to 1.

When I run the package duplicate rows are in grid view. How can duplicates end in there the commit size is 1? Next time the data exists in reference table and should be going to detail not to reference table.

Funny but this was just answered in another thread about the lookup cache. The dataflow works on buffers not rows so the lookup transform gets a whole buffer to process at one time. It then looks up every row in this buffer and after that passes it on downstream. So any row with the same key in that buffer will be looked up in the same manner and sent to the same output for processing. No setting on another transform can change this. Furthermore, depending on the cache type the lookup will load all its cache into memory (full and partial cache) so it will never (or not reliably) be updated by any changes to the underlying data.

HTH,

Matt

|||Well, I can guess that. How to fix this? add another transform? where? after lookup?|||

Well, you can't fix it. This is the design of the dataflow. You can try some workarounds such as setting the lookup to no cache mode or the dataflow's max rows to a low number (note that setting it to 1 doesn't work because the dataflow does rounding in order to not waste memory). However, none of these are certain to work in all cases. The only option is to write your own custom or script component that has logic to circumvent the buffering.

Matt

|||

HI, I have similar problem. Setting lookup to no cache or the dataflow to low number of max row did not work at all. I ended up building my own asynchronous sript component in order to achieve what the lookup or SCD wizard should be able to do.

Ccote

|||

We are currently investigating a ton of ideas around lookup, and this pattern is one we are taking into consideration.

However, I do want to point out that adding such a pattern requires us to add more logic to the lookup component and we are somewhat cautious about that. SSIS components are typically very tightly scoped in their functionality - design patterns are built with several different smaller components rather than in one more complex "catch all component." Lookup us an example of a component that does one fairly isolated function - complete integration patterns are built around it using other components.

The pattern we are dicussing here is actually more complicated than might first be thought - especially when one looks at the possibility of errors creeping in through system problems or bad data. (I have never in my career seen a data integration process where unexpected errors of all sorts did not creep in, so I tend to be cheerfully pessimistic in my designs.)

I'll recap the current requirement as follows, to make it clear for other readers:

We want to lookup the key from an incoming record in a reference table.|||

Great!!! It's really helpful. I have broken the process into two steps; first step add to reference table and next step lookup works(the way it should).

I can understand the import will be slow if every row is commited before look up, but there should be option, sometime it's should work logically and of course slow is not a word these days with powerful machines.

Thanks again Donald for your response .

Looking for Embedded SQL for COBOL toolkit

Does anybody know where i can find "Microsoft Embedded SQL for COBOL
Programmer's Reference" or "Microsoft Embedded SQL for COBOL Programmer's
Toolkit" ?
I have Microsoft COBOL 5.0 and MicroFocus COBOL 3.4 and would like to access
SQL databases from COBOL.
I've tried eBay, Amazon, Google etc.
Any help would be greatly appreciated.
TonyTony Girgenti (antoniongirgenti@.comcast.net) writes:

quote:

---

> Does anybody know where i can find "Microsoft Embedded SQL for COBOL
> Programmer's Reference" or "Microsoft Embedded SQL for COBOL Programmer's
> Toolkit" ?
> I have Microsoft COBOL 5.0 and MicroFocus COBOL 3.4 and would like to
> access SQL databases from COBOL.

---

I have no idea where you can find it, but there is probably a good reason
why you can't: this product is a dead-end, and you cannot use all
functionality in SQL Server with it. The only reason to use embedded SQL
with SQL Server is because you are working with a legacy product.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||How rediculous of me. I'll just forget about all of the ideas i have and
become an SQL Server MVP.
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns947781F94337Yazorman@.127.0.0.1...

quote:

---

> Tony Girgenti (antoniongirgenti@.comcast.net) writes:
Programmer's[QUOTE]
> I have no idea where you can find it, but there is probably a good reason
> why you can't: this product is a dead-end, and you cannot use all
> functionality in SQL Server with it. The only reason to use embedded SQL
> with SQL Server is because you are working with a legacy product.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

---

|||That wasn't very nice Tony Here is a link for COBOL.net stuff though -
http://www.c-sharpcorner.com/cobolnet/code.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"Tony Girgenti" <antoniongirgenti@.comcast.net> wrote in message
news:eGHZhk83DHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> How rediculous of me. I'll just forget about all of the ideas i have and
> become an SQL Server MVP.
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns947781F94337Yazorman@.127.0.0.1...
> Programmer's
reason[QUOTE]
>

---