Having trouble getting connections

I have a custom Data Flow Destination Adapter that is looking for a particular type of ConnectionManager. I want to check that this connection exists in the Validate method. I first checked the ComponentMetedata.RuntimeConnections, but it was was an empty collection. I am guessing that it gets populated at runtime. Is there anything available for to check at design time?

Another way of asking this would be, Is there a way to programatically select which ConnectionManager gets assigned to a RuntimeConnection? Normally, this is done on the Connections tab of the Advanced Editor.

Thanks,

Graham

You can assign a connection manager at design time to your adapter and check that it is the correct type in the Validate() method. You will need to let your component know that it is expecting a connection manager.

Allan

|||I realize that I can do that by opening the advanced editor on my destination task and selecting the connection; but I dont want to do it that way. I want to Programmatically look for a connection of a specific type. Below is a code snippet of what I tried to do, but that doesnt seem to work. The connection is not set.

//this takes place in the onload handler for my DestinationAdapter form
//connections is passed in from the UI class that implements IDtsComponentUI
ConnectionManager connMan = null;
foreach (ConnectionManager cm in connections)
{
if (cm.InnerObject.GetType() == typeof(ProfileConnectionManager))
{
connMan = cm;
}
}
profConnMan = ProfileConnectionManager.FindProfileConnection(connections);
dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connMan);

|||Is there a reason you don't just get the value of ConnectionManagerType on the properties collection? Also, you're attempting to use a managed code idiom for a connection that may not be managed. Most of the connection managers are native. Is this a custom connection manager? I don't know about the ProfileConnectionManager.|||

You may also want to take a look at IDtsConnectionService.GetConnections and IDtsConnectionService.CreateConnection. Useful for writing UIs. They all work around the connection type that Kirk mentions.

|||ProfileConnectionManager is a custom connection that I have written. I want to allow a custom data flow destination adapter that I have written to look for a ProfileConnectionManager and if one is defined, add it to my CustomDestinationAdapter.ComponentMetaData.RuntimeConnections collection.

In normal situations, this is done at design time by openning the advanced editor and selecting the connection manager from a dropdown. In my situation, I am allowing only one ProfileConnectionManger to be created. So if it exists, I dont want the user to have to select it from the advanced editor; I want that to be done programmatically by me.

When you refer to properties collection, are you saying I should add a property to MyCustomDestinationAdapter.ComponentMetaData.CustomPropertyCollection and assign my custom connection as its value?

thanks,
Graham|||

I would use GetConnections, albeit in a UI, something like this-

foreach (ConnectionManager connectionManager in _dtsConnectionService.GetConnectionsOfType(connectionType))
{
comboBox.Items.Add(connectionManager.Name);
}

connectionType is the string that identifies the type of connection I want. For example if I want ADO.Net SQLClient connections I need to use-

"ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";

It is a bit annoying that I have to use the fully qualified name, but that's what works.

The ConnectionManager also has a CreationName property which I use often for validation, and Kirk suggests a ConnectionType property, so using this you could loop as you where above, and check such a property to see if it is your connection. What you should not do is try and get a .Net Type object for the connection manager, as not all of them are .Net objects.

sql

Having a problem inserting products

I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.

Middle Tier Function (

privatestaticint GetBrandForProduct(clsProduct o)

{

int brandid = -1;// If the brand name comes in blank use the first word of the overstock product

o.BrandName = o.BrandName.Trim();

// if we do not have a brand for this productif (o.BrandName.Length == 0)return -1;Database db =CommonManager.GetDatabase();;try

{

// Get the brand id for this brand name// If it does not exist we will add it and STILL return a brand idobject obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName);string catid = obj.ToString(); *** FAILING LINE ***returnConvert.ToInt32(obj.ToString());

}

catch (Exception ex)

{

throw ex;return -1;

}

return brandid;

}

Stored Procedure: --------------------------------------------------

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)

AS

BEGIN

-- SET NOCOUNT ONSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)-- If we found a record, exit

if@.@.rowcount> 0return-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)

END

Hi Dear,

You have not mention that What is the Exception Message? I will be in better position to answer if you share Exception Message also...

but the One thing that seems wrong in your SP is

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

@.@.rowcount return 0 if select statement didn't find any result......

so i think this check will be like this

if@.@.rowcount = 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

change this thing in your Store Procedure , if problem doesn't solve ..then post the Exception message...

Thank You

Best Regards,

Muhammad AKhtar Shiekh

|||

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

|||

patrick24601:

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

That's what i am saying but there is contradiction in Your SP...it is doing this

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

It is actually inserting when RowCount is greater then 0 ( Not equal to 0)

......

patrick24601:

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

You can try this code,

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endelseSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END

Thanks

Best Regards,

Muhammad AKhtar Shiekh

|||

sorry SP is no correct in above post, Remove the else part in the sp

ALTERPROCEDURE [dbo].[BrandIDGetOrInsert]

-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END|||

i think the problem lies with this:

if (o.BrandName.Length == 0)return -1;

Database db =CommonManager.GetDatabase();

;

Notice the stray ; also the if statement is missing some braces

if (o.BrandName.Length == 0)
{
return -1;
}

Thats what looks wrong to me

has no parameters and arguments were supplied

I'm kind of new at doing something like this. I'm trying to pull data into a results that exists in one of two databases. It works fine with query analyzer
but gives me the error "has no parameters and arguments were supplied" when I try to convert it to a stored procedure.

The procedure is as follows, any help would be appreciated.

CREATE PROCEDURE sp_getInvoiceNoTest AS

declare @.InVoiceNo as VarChar(30)

delete From Invoice_NBR

Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.Acuity_App.dbo.tarInvoice as a
inner join
Colucw17.Acuity_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.Acuity_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.Acuity_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.Acuity_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@.InvoiceNo'

--Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.CSM_App.dbo.tarInvoice as a
inner join
Colucw17.CSM_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.CSM_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.CSM_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.CSM_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@.InvoiceNo'
GOCREATE PROCEDURE sp_getInvoiceNoTest AS
(
@.InVoiceNo as VarChar(30)
)
as
set nocount on

delete From Invoice_NBR

Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.Acuity_App.dbo.tarInvoice as a
inner join
Colucw17.Acuity_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.Acuity_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.Acuity_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.Acuity_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo=@.InvoiceNo

--Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.CSM_App.dbo.tarInvoice as a
inner join
Colucw17.CSM_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.CSM_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.CSM_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.CSM_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo=@.InvoiceNo
GO|||Using the code you supplied it generated the following error message when I checked the syntax:

. .
Error 170: Line 3: Incorrect syntax near @.InvoiceNo.
Line 11: Incorrect syntax near
Must declare the variable :@.Invo!ceNo:
Must declare the variable @.InvoiceNo.

I added the declare statement but iot still errored.|||drop the AS between @.InvoiceNo As VARCHAR|||Thanks Peso, that was it. You help is greatly appreciated. Hopefully, I'll get the hang of this stuff.

Has anyone seen a 25 TB data warehouse on SQL Server?

Has anyone seen a 25 TB data warehouse on SQL Server? I do not know of
such a thing, but maybe it exists out there.I assume this is a rhetorical loaded question, but someone reading it
might be curious. Just in case they don't think to Google it,

http://www.microsoft.com/resources/...seStudyID=16212
http://germany.emc.com/solutions/he...ta_genomics.jsp

Terraserver is pretty big, but I don't think it's 25 TB.

You'll find these and more if you search Google for
"25 terabytes"+"sql server 2000"
"30 terabytes"+"sql server 2000", etc.

Steve Kass
Drew University

--CELKO-- wrote:

> Has anyone seen a 25 TB data warehouse on SQL Server? I do not know of
> such a thing, but maybe it exists out there.|||>> I assume this is a rhetorical loaded question, but someone reading it might be curious. <<

Nope. I was hoping to find someone who had worked on a SQL Server
project in this size range. I see that a lot of these projects are
expected to grow to x-Terabytes in the future, but are not starting at
that size in the first year.

The reason I am asking is that my first thought is to use Teradata or
SAND, which were meant for VLDB projects.|||
--CELKO-- wrote:

>>>I assume this is a rhetorical loaded question, but someone reading it might be curious. <<
>
> Nope. I was hoping to find someone who had worked on a SQL Server
> project in this size range. I see that a lot of these projects are
> expected to grow to x-Terabytes in the future, but are not starting at
> that size in the first year.

I asked around about this, and a colleague of mine said he
has a financial data warehouse running SQL Server 2000 that
is currently > 30 TB.

I'll pass your "hoping to find someone" comment to him.

SK

> The reason I am asking is that my first thought is to use Teradata or
> SAND, which were meant for VLDB projects.|||--CELKO-- wrote:
>>>I assume this is a rhetorical loaded question, but someone reading it might be curious. <<
>
> Nope. I was hoping to find someone who had worked on a SQL Server
> project in this size range. I see that a lot of these projects are
> expected to grow to x-Terabytes in the future, but are not starting at
> that size in the first year.
> The reason I am asking is that my first thought is to use Teradata or
> SAND, which were meant for VLDB projects.

Contact me off-line Joe. I have hands-on experience with Teradata and
with other RDBMS products in the multi-terabyte range: There are a
number of options.

But keep in mind that if this is in the US you need to keep SarbOx and
the new FTC regs squarely in mind when chosing your approach. You can
do everything right technically and still be in violation of federal
law.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||Joe,

> Has anyone seen a 25 TB data warehouse on SQL Server? I do not
> know of such a thing, but maybe it exists out there.

Sure, I see one every day. And there are other huge data warehouses
out there running on SQL Server. The owners of them just don't
publicize it.

I gotta laugh every time you post that SQL Server is only for little
departmental databases. You really have no idea what some poeple are
doing with SQL Server these days.

Linda|||That was me. Current size is 31 TB running Unisys ES7000 520 and EMC 40TB
total size of the SAN.

GertD@.SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.

"Steve Kass" <skass@.drew.edu> wrote in message
news:aS3ve.12568$eM6.6266@.newsread3.news.atl.earth link.net...
>
> --CELKO-- wrote:
>>>>I assume this is a rhetorical loaded question, but someone reading it
>>>>might be curious. <<
>>
>>
>> Nope. I was hoping to find someone who had worked on a SQL Server
>> project in this size range. I see that a lot of these projects are
>> expected to grow to x-Terabytes in the future, but are not starting at
>> that size in the first year.
>>
> I asked around about this, and a colleague of mine said he
> has a financial data warehouse running SQL Server 2000 that
> is currently > 30 TB.
> I'll pass your "hoping to find someone" comment to him.
> SK
>
>> The reason I am asking is that my first thought is to use Teradata or
>> SAND, which were meant for VLDB projects.
>

Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D