loosing connections - closed by server

Hello,
We experience problem with SP2. We have large application based on .NET
combining several MS products and technologies where SQL Server 2005 is used
as a database. We use BizTalk 2006 servers, IIS6 for web applications and
others. All operate on the same SQL Server running in clustered environment.
SQL Server machines run Windows Server 2003 R2 Enterprise x64 Edition SP1,
SQL Server 2005 x64 Standard Edition SP1. Application server machines run
Windows Server 2003 Enterprise (32bit) SP2.
We have installed SP2 for Windows and SP2 for SQL together on staging
cluster and after some time of successful run we have installed it into
production environment too. However in production we unpredictably received
error below raising out of requests from BizTalk components, web applications
and also clients accessing database directly.
A transport-level error has occurred when sending the request to the server.
(provider: TCP Provider, error: 0 - An existing connection was forcibly
closed by the remote host.)
The error was raised in different times from different applications.
Production environment is used by more clients but all together there just
tens of clients. The most critical errors were from BizTalk as it failed to
connect to both BizTalk and applications databases. As the behavior was
business critical we decided to uninstall both SP2 (SQL and Windows) from
production machines. Then the error disappeared but this solution is rather
temporary fix. We havenâ't found much of relevant information and are not able
to even say if the problem is caused by SP2 for system, SP2 for SQL or
combination of both. Anyway we would need SP2 to solve at least another
performance issues with WMI.
Did anyone face the same issue? Any idea for solution?
Thanks
eXavierIt happens to me when I close the connection (via programmatically or from
SSMS' s Query Editor)
--
Ekrem Ã?nsoy
"eXavier" <eXavier@.nospam.nospam> wrote in message
news:91FB542D-9E3E-4D8A-93A2-B756656C9E99@.microsoft.com...
> Hello,
> We experience problem with SP2. We have large application based on .NET
> combining several MS products and technologies where SQL Server 2005 is
> used
> as a database. We use BizTalk 2006 servers, IIS6 for web applications and
> others. All operate on the same SQL Server running in clustered
> environment.
> SQL Server machines run Windows Server 2003 R2 Enterprise x64 Edition SP1,
> SQL Server 2005 x64 Standard Edition SP1. Application server machines run
> Windows Server 2003 Enterprise (32bit) SP2.
> We have installed SP2 for Windows and SP2 for SQL together on staging
> cluster and after some time of successful run we have installed it into
> production environment too. However in production we unpredictably
> received
> error below raising out of requests from BizTalk components, web
> applications
> and also clients accessing database directly.
> A transport-level error has occurred when sending the request to the
> server.
> (provider: TCP Provider, error: 0 - An existing connection was forcibly
> closed by the remote host.)
> The error was raised in different times from different applications.
> Production environment is used by more clients but all together there just
> tens of clients. The most critical errors were from BizTalk as it failed
> to
> connect to both BizTalk and applications databases. As the behavior was
> business critical we decided to uninstall both SP2 (SQL and Windows) from
> production machines. Then the error disappeared but this solution is
> rather
> temporary fix. We havenâ't found much of relevant information and are not
> able
> to even say if the problem is caused by SP2 for system, SP2 for SQL or
> combination of both. Anyway we would need SP2 to solve at least another
> performance issues with WMI.
> Did anyone face the same issue? Any idea for solution?
> Thanks
> eXavier|||Hello,
Based on the symptom, it seems to be related to a known issue if you have
network adapter uses the Broadcom 5708 chipset. This problem occurs because
the TCP/IP offload functionality is enabled on the server. The TCP/IP
offload functionality is enabled by the Windows Server 2003 Scalable
Networking Pack.
To resolve this problem, ask the hardware vendor to determine whether they
have the hardware vendor has the following updates to resolve this problem.
- The latest BIOS update for the server.
- The latest firmware update for the network adapter.
- The latest driver update for the network adapter.
To work around this problem, you may try to disable the TCP Chimney Offload
feature.
To disable the TCP Chimney Offload feature, please follow these steps:
1. Click Start, click Run, type cmd , and then press ENTER .
2. At the command prompt, type the following command, and then press ENTER
Netsh int ip set chimney DISABLED
Note You do not have to restart the server after you run this command.
If the performance of Windows Server 2003 decreases after you disable the
TCP Chimney Offload feature, follow these additional steps:
1. Click Start, click Run, type Regedit , and then click OK.
2. Locate the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
3. Double-click the EnableTCPChimney registry entry.
4. In the Edit DWORD Value dialog box, type 0 in the Value data box, and
then click OK.
5. Double-click the ParametersEnableRSS registry entry.
6. In the Edit DWORD Value dialog box, type 0 in the Value data box, and
then click OK.
7. Double-click the EnableTCPA registry entry.
8. In the Edit DWORD Value dialog box, type 0 in the Value data box, and
then click OK.
9. Restart the server.
If you have any updates or comments, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter,
Thanks a lot for your support. Turning off the TCP Chiemney really helped to
solve the problem. Now we have Windows SP2 installed and application runs
well. Thanks!
We also tried to apply the latest bios, firmware and drivers for Broadcom
5708S NICs that are integrated into our blade servers but the offloading bug
is not yet fixed so the only solution at a time is to disable this feature.
Best regards
eXavier
""Peter YangMSFT]"" wrote:
> Hello,
> Based on the symptom, it seems to be related to a known issue if you have
> network adapter uses the Broadcom 5708 chipset. This problem occurs because
> the TCP/IP offload functionality is enabled on the server. The TCP/IP
> offload functionality is enabled by the Windows Server 2003 Scalable
> Networking Pack.
> To resolve this problem, ask the hardware vendor to determine whether they
> have the hardware vendor has the following updates to resolve this problem.
> - The latest BIOS update for the server.
> - The latest firmware update for the network adapter.
> - The latest driver update for the network adapter.
> To work around this problem, you may try to disable the TCP Chimney Offload
> feature.
> To disable the TCP Chimney Offload feature, please follow these steps:
> 1. Click Start, click Run, type cmd , and then press ENTER .
> 2. At the command prompt, type the following command, and then press ENTER
> .
> Netsh int ip set chimney DISABLED
> Note You do not have to restart the server after you run this command.
> If the performance of Windows Server 2003 decreases after you disable the
> TCP Chimney Offload feature, follow these additional steps:
> 1. Click Start, click Run, type Regedit , and then click OK.
> 2. Locate the following registry subkey:
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
> 3. Double-click the EnableTCPChimney registry entry.
> 4. In the Edit DWORD Value dialog box, type 0 in the Value data box, and
> then click OK.
> 5. Double-click the ParametersEnableRSS registry entry.
> 6. In the Edit DWORD Value dialog box, type 0 in the Value data box, and
> then click OK.
> 7. Double-click the EnableTCPA registry entry.
> 8. In the Edit DWORD Value dialog box, type 0 in the Value data box, and
> then click OK.
> 9. Restart the server.
> If you have any updates or comments, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Loosing Bold when exporting to Excel

Hi,
I have a report that bolds font in a table cell based on an expression.
This works fine when I browse the report in reporting services. When I
export the report to Excel - all of the bold text returns to normal weight
text. Any ideas to get around this?
Thanks,
MelissaMelissa, this seems to be a hot topic this week!
I've seen three other posts on this already. This is the same as the
Excel font colour from expressions problem.
Whatever formatting the first cell evaluates too, the whole column is
set too. As far as I can tell, there is no way round this.
I've raised it as a bug with MS
Chris
Melissa wrote:
> Hi,
> I have a report that bolds font in a table cell based on an
> expression. This works fine when I browse the report in reporting
> services. When I export the report to Excel - all of the bold text
> returns to normal weight text. Any ideas to get around this?
> Thanks,
> Melissa|||Thanks Chris for the response!
Melissa
"Chris McGuigan" wrote:
> Melissa, this seems to be a hot topic this week!
> I've seen three other posts on this already. This is the same as the
> Excel font colour from expressions problem.
> Whatever formatting the first cell evaluates too, the whole column is
> set too. As far as I can tell, there is no way round this.
> I've raised it as a bug with MS
> Chris
> Melissa wrote:
> > Hi,
> >
> > I have a report that bolds font in a table cell based on an
> > expression. This works fine when I browse the report in reporting
> > services. When I export the report to Excel - all of the bold text
> > returns to normal weight text. Any ideas to get around this?
> >
> > Thanks,
> > Melissa
>

Loop Through Flat Files Based On A Date Range

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

One way is to move the files to an archive directory when you're done with them. That way you only pick up the files you need to process. Just throwing that idea out there.|||Thanks for your response. One thing that I have to be prepared for is that I may have to 'reload' data. The date range for any reloading could be anything.|||Well, you could (and perhaps should?) stage the data from the flat files in a staging table. Then, once in the staging table you can keep n number of loads around, all marked by either the load date, or a unique load number. After n number of loads, you can delete the old data. Each row in the staging table would also contain the source file name.

Or, you could simply load a table with the load date and a filename. That way, you've got a table that tells you which files to grab for a given load.
Load File
1 xyz.txt
1 fhs.txt
2 jfb.txt

The above still assumes that you "archive" the files into a subdirectory or somewhere.|||The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.|||

cdun2 wrote:

The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.

Right, but the point is that your first data flow simply loads the data from the files AS IS into a table. Then in the data flow that you use today, you point them to the staging table instead of the flat file(s).|||Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.|||

cdun2 wrote:

Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.

You're going to have to add the column in the flat file source anyway, would be one argument.

Regardless, this is just one way to tackle this problem. There are other ways I'm sure.|||

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

|||

cdun2 wrote:

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

If this is the case, then the staging table example still works. In your CONTROL flow, you'd use a foreach loop to spin through the files. Each filename can be put in a variable, which can be used in a data flow inside the foreach loop. Then, using a derived column in the data flow, you'd have access to the variable that contains the current filename.|||

cdun2 wrote:

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

Cdun2,

This is an alternative approach to the issue of processing only the files that are within a start/end date range. It uses Expression and precedence constraints:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

BTW,

Just to save extra work down the road; i would recomment to import all the columns of the files from the begining.

|||Thanks again for your input. I'll dig a little deeper into this next week. As far as bringing in all of the columns of data, the attempt is to aggregate so that we can meet a reporting need, and reduce the number of rows of data that we need to store. Its a resource/performance issue for us. I can't bring in any more than I need.|||

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.|||

Phil Brammer wrote:

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.

Yes, that makes sense. Thanks again.

|||

I took a look at the example here; http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

Thank you for providing this example. I am having a difficult time making the bridge between the precendent constraint, and the Data Flow Task where the content of the qualifying flat files are inserted into the SQL Server table.

Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

Lookup transformation problem

Hello all,

I needed to lookup some table values based on a join of two fields...

I've configured the lookup transform to get the values via a SQL statement to minimize loading time.

However, when creating the relationships between the input columns and the lookup columns I receive following error:
input column [BATCH_ID] has a datatype which cannot be joined on

I've checked both input and lookup columns, both are of type DT_R8... Both columns in the different tables do have the same datatypes

Any idea how to solve this problem?

Thanks in advance

DT_R8 is a floating point data type, it is an approximate value which means it cannot be joined on. Try a decimal data type instead.|||

Hello,

Thanks for your reply but how can I change datatypes fetched directly from a SQL statement in the lookup query?

|||

lookup supplied many functions can cast datatype

i think you also can use convert or cast in sql statements

|||

Hello,

I've used Dataconversion and SQL functions to perform the requested conversions...

works fine however when executing the package it fails when loading the cache of the lookup components...
this is the message I get from the progress window:

[GET HOLDING_ID [2998]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01722: invalid number ".

and

next line:

[GET HOLDING_ID [2998]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

However, I never changed the SQL command of the transform in the advanced tab...

Any Idea?

Thanks in advance

Lookup transformation based on a csv file

My lookup data is in a csv file, not a table. Is there a way to get the Lookup transformation to use the csv file as the source 'table'? Obviously the alternative is to load the file into a SQL Server table and use that, but I want to keep it simple if possible.

The lookup component can only use OLE DB sources. You might be able to construct a query with OPENROWSET to read the text file via OLE DB, but that doesn't seem like the simple solution. You might also be able to use a Merge Join in Left Outer mode instead of a Lookup, which would let your source the right side from a flat file. You may have to add some sorting steps, though.
|||

The technique that I prefer for this situation is to have a separate package (or a separate data flow within the same package) that loads the lookup data from the source CSV into a lookup table in SQL Server (or whatever is your RDBMS of choice) and then perform the lookup in the "main" package against the database table version of the lookup data. If you ensure that the CSV to Table package is always executed first, this should solve the problem neatly.

Although this is not quite as simple as the ideal situation where the Lookup transform supported non-OLE DB data sources, it is much simpler (and much better performing) than any other alternative that I can think of.

Lookup task based on a variable

This doesn't seem possible but I'll ask anyway...

Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?

No, there is no support for this.

The best you can do is write a query and then use values in your input data as parameters, to help filter the rows. Seems obvious, but often I have got stuck thinking I wanted a parameterised query, so that results are filtered before I do the lookup, which cannot be done. This is probably not as effcient, but the closest we have.

|||

That's what I figured...

On the bright side, I was able to use another new SQL Server 2005 feature...

SELECT RecId, CAST(COUNT(*) AS Char(1)) [PVL]
FROM VT
WHERE Elect IN (

SELECT Elect
FROM (
SELECT State,PVLE1,PVLE2,PVLE3,PVLE4
FROM StateOptions) opt
UNPIVOT
(Elect For State IN (PVL1, PVL2, PVL3, PVL4)) AS UPV

)

GROUP BY RecId

Of course this would have been much simpler if the options table had been built vertically instead of horizontally!

Lookup OR Join?

Does anyone have experience with comparing the performance in dealing with very large data sets to determine the presence of a record based on key fields in order to determine wheter to update or insert a record to a table? With another ETL tool, large lookups perform better with a join transformation component, rather than an actual lookup component, because of the large caching requirements.

In using a join, you simply base the condition on the result of the join, as you can with an SSIS lookup component 'erroring' out. Some tools also have an actual 'upsert' component that helps in achieving such a scenario.

Thoughts?Joe,
During the research for this article: http://www.sqlis.com/default.aspx?311 I was comparing the 2 methods. LOOKUP performed significantly better although it wasn't on a big dataset.

Also I have a case study coming out on my blog imminently (it has to go through manager approval first) in which the 2 methods are compared on a very very large dataset (millions of rows). The performance increase when using LOOKUPs was breathtaking - I can't give details as I'm under unofficial NDA at the moment...but look out for it in the next few days on http://blogs.conchango.com/jamiethomson.

-Jamie|||I have been using a SQL join and creating a new column with either a 'U' for update or 'I' for insert. I am comparing a small number of rows against a large dataset. I am interested in the results of your case study, Jamie - I will watch for it.
-Evan Black

Looking for existing tool/product to integrate in MS CMS/SQL based website

Hi all!
I am the IT manager for the Flemish Red Cross and I am looking for an
existing tool/product that we can use to implement a full text-search
(including webpages, Word documents and PDF's) on our new website, thta
we're building on MS CMS and SQLServer.
Any suggestions? We don't really have the time to develop something
ourselves, and I am convinced that there are existing tools or products that
can do the job.
We're willing to spend some money on it, but not in the area of 10000
USD/EUR per processor...
Any help would be greatly appreciated.
I can be e-mailed directly at werner.huysmans@.advalvas.be or
werner.huysmans@.rodekruis.be
Thx in advance!
Werner
SQL Full Text Search is optimal for this and it is free.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Werner huysmans" <werner.huysmans@.advalvas.be> wrote in message
news:pHrhf.61734$rA.54376@.blueberry.telenet-ops.be...
> Hi all!
> I am the IT manager for the Flemish Red Cross and I am looking for an
> existing tool/product that we can use to implement a full text-search
> (including webpages, Word documents and PDF's) on our new website, thta
> we're building on MS CMS and SQLServer.
> Any suggestions? We don't really have the time to develop something
> ourselves, and I am convinced that there are existing tools or products
> that
> can do the job.
> We're willing to spend some money on it, but not in the area of 10000
> USD/EUR per processor...
> Any help would be greatly appreciated.
> I can be e-mailed directly at werner.huysmans@.advalvas.be or
> werner.huysmans@.rodekruis.be
> Thx in advance!
> Werner
>