Showing posts with label control. Show all posts
Showing posts with label control. Show all posts

Friday, March 30, 2012

Problems with the Web ReportViewer Control

Problems with the Web ReportViewer Control

I'm using SQL Server 2005 Reporting Services Sept CTP and Visual Studio 2005 RC to develop an ASP.NET application. On one of my web forms I have placed the web ReportViewer control so that I can display reports from Reporting Services.

The .aspx code is:

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="ReportViewer.aspx.cs" Inherits="ReportViewer" %>

<%@. Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>ReportViewer</title>

</head>

<body>

<form id="formReportViewer" runat="server">

<div>

<asp:Panel ID="PanelDisplayReport" runat="server" Height="100%" Width="100%">

<rsweb:reportviewer id="ReportViewerDisplayReport" runat="server" bordercolor="Navy" borderstyle="Solid" borderwidth="1px" height="100%" tooltip="Display Report" width="100%" Font-Names="Verdana" Font-Size="8pt" ProcessingMode="Remote">

</rsweb:reportviewer>

</asp:Panel>

</div>

</form>

</body>

</html>

The .aspx code-behind (in C#) is:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class ReportViewer : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

this.ReportViewerDisplayReport.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

this.ReportViewerDisplayReport.ToolTip = "/AdventureWorks Sample Reports/Company Sales";

this.ReportViewerDisplayReport.ZoomPercent = 90;

this.ReportViewerDisplayReport.ServerReport.DisplayName = "/AdventureWorks Sample Reports/Company Sales";

this.ReportViewerDisplayReport.ServerReport.ReportPath = @."/AdventureWorks Sample Reports/Company Sales";

this.ReportViewerDisplayReport.ServerReport.ReportServerUrl = new Uri(@."http://localhost/ReportServer");

}

}

As you can see, this is a very simply web form that is simply trying to display one of the reports from the AdventureWorks sample database and reports.

The problem is that the report will not display in the ReportViewer control. When I request the page, I see the ReportViewer's toolbar but not the actual report. I initially see the "Report is being generated" message in the control, where the report would normally be displayed. But, after the report is generated, no report, just the toolbar.

Anyway, does any one have any suggestions as to what's wrong?

Is there a bug in the ReportViewer control (for web forms) in Visual Studio 2005 RC?

Anything else???

Thanks in advance.


The problem is that height is set to 100% and doctype is set to XHTML. Either set the height to a fixed size (not a percent) or remove the line that sets doctype to XHTML.|||Thanks for this. I spent a couple of hours on this, I could have spent many more.|||You can also fix this problem by rendering the report synchronously. The control displays the report differently based on the method of rendering and calculates the actual height on the fly. When rendered synchronously, the calculated height is 0px, so nothing shows.

Problems with the ReportViewer Control

Theclient side reportviewer control is used with a typed dataset in vs 2005.

But I have noticed something that is really interesting,

theturkish characters in the dataset are changed in the runtime enviroment when used with the reportviever control.

such as.

exec sp_executesql N'SELECT CASES._ID, CASES._OWNERID, CASES._LASTMODIFYDATE, CASES._DELETIONSTATUS, CASES._SECURITYCODE,
CASES._NAME, CASES._STATE,
CASES._DESCRIPTION, CASES._CATEGORY, CASES._TYPE, CASES.PRIORITY, CASES.REASON, CASES.ORIGIN, CASES.FROMDATE,
CASES.TODATE,
CASES.COMMENT, CASES.WHATID, CASES.WHATTYPE, VIEW_WHONAME.WHONAME, ORGANIZATION._NAME AS ORGANIZATIONNAME,
PERSON.HOMETEL, PERSON.EMAIL
FROM CASES INNER JOIN
VIEW_WHONAME ON CASES.WHOID = VIEW_WHONAME.WHOID INNER JOIN
PERSON ON VIEW_WHONAME.WHOID = PERSON._ID INNER JOIN
ORGANIZATION ON PERSON.WORKINGORGANIZATIONID = ORGANIZATION._ID
WHERE (CASES._OWNERID = @.ownerID) AND (CASES._ID = @.entityID) AND (VIEW_WHONAME.WHOTYPE =''KİÅ?İ'')',N'@.ownerID
smallint,@.entityID int',@.ownerID=1,@.entityID=30

-----------

>>> the bold area should be 'K???' which is a turkish string...

What sholud I do? Do I need to configure the sql server or do I need to configure asp.net runtime?

Is the data type of VIEW_WHONAME.WHOTYPE NVARCHAR? If yes, that means you've stored the unicode characters in SQL, so just make sure you're comparing it to unicode chars at runtime. You can mark a string as UNICODE in SQL by adding a N prefix in front of the string:

VIEW_WHONAME.WHOTYPE =N''KİÅ?İ''

|||

I did tried that but still the dataset in the app_code changes the string at runtime.When I test the dataset designer at design time, everything is just fine.

if there any configuration I have to make to stop asp.net runtime changing my strings?

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

exec sp_executesql N'SELECT CASES._ID, CASES._OWNERID, CASES._LASTMODIFYDATE, CASES._DELETIONSTATUS, CASES._SECURITYCODE,
CASES._NAME, CASES._STATE,
CASES._DESCRIPTION, CASES._CATEGORY, CASES._TYPE, CASES.PRIORITY, CASES.REASON, CASES.ORIGIN,
CASES.FROMDATE, CASES.TODATE,
CASES.COMMENT, CASES.WHATID, CASES.WHATTYPE, VIEW_WHONAME.WHONAME, ORGANIZATION._NAME AS ORGANIZATIONNAME,
PERSON.HOMETEL, PERSON.EMAIL
FROM CASES INNER JOIN
VIEW_WHONAME ON CASES.WHOID = VIEW_WHONAME.WHOID INNER JOIN
PERSON ON VIEW_WHONAME.WHOID = PERSON._ID INNER JOIN
ORGANIZATION ON PERSON.WORKINGORGANIZATIONID = ORGANIZATION._ID
WHERE (CASES._OWNERID = @.ownerID) AND (CASES._ID = @.entityID) AND (VIEW_WHONAME.WHOTYPE =N''KİÅ?İ'')',N'@.ownerID
smallint,@.entityID int',@.ownerID=1,@.entityID=30

|||finally I have sp?ved the problem by jsut saving the dataset using advanced save options and I gave the apporopiate encoding to it.|||finally I have solved the problem by just saving the dataset using advanced save options and I gave the apporopiate encoding to it.

Monday, March 26, 2012

Problems with RS Unattended Execution Account

HI all
I have RS2000 Report Server with a report that contains an image control.
The image control value is a UNC path to an image (ie
\\<server>\images\image.jpg. The <server> is a different server to the
Report Server. I have setup the unattended execution account to be the
domain administrator (for testing purposes). WHen running the report on my
machine in Visual Studio, I can see the images, however when running it off
the server the images do not appear. I setup object access auditing on the
server containing the images and it seems like the report server is not even
attemting to access the images.
Anyone know what I may be missing here.
Thanks in advance
GeorgeOn Aug 29, 2:15 am, "_george" <none@.nojne@.none> wrote:
> HI all
> I have RS2000 Report Server with a report that contains an image control.
> The image control value is a UNC path to an image (ie
> \\<server>\images\image.jpg. The <server> is a different server to the
> Report Server. I have setup the unattended execution account to be the
> domain administrator (for testing purposes). WHen running the report on my
> machine in Visual Studio, I can see the images, however when running it off
> the server the images do not appear. I setup object access auditing on the
> server containing the images and it seems like the report server is not even
> attemting to access the images.
> Anyone know what I may be missing here.
> Thanks in advance
> George
It might be that since you are accessing the image through the Report
Server, which is basically a website, you may be using the default
ASPNET account that has minimal permissions in the system. On
occasion, explicit settings are overridden in ASP.NET and IIS.
Depending on your circumstances, you could try embedding the image in
the report. Also, you might want to check the settings in one of
the .config files in the path: C:\Program Files\Microsoft SQL Server
\MSSQL.x\Reporting Services\ReportManager it may be overriding your
domain admin account. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique
I had swapped the default ASP.Net account used in the Out of process
components for IIS to a Domain Administrator account but still no go. As for
the config files, what exactly should I be looking for' I was under the
impression that all usernames and pwds in the .config files are encrypted.
Thanks again
George
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1188435094.798326.100950@.22g2000hsm.googlegroups.com...
> On Aug 29, 2:15 am, "_george" <none@.nojne@.none> wrote:
>> HI all
>> I have RS2000 Report Server with a report that contains an image control.
>> The image control value is a UNC path to an image (ie
>> \\<server>\images\image.jpg. The <server> is a different server to the
>> Report Server. I have setup the unattended execution account to be the
>> domain administrator (for testing purposes). WHen running the report on
>> my
>> machine in Visual Studio, I can see the images, however when running it
>> off
>> the server the images do not appear. I setup object access auditing on
>> the
>> server containing the images and it seems like the report server is not
>> even
>> attemting to access the images.
>> Anyone know what I may be missing here.
>> Thanks in advance
>> George
>
> It might be that since you are accessing the image through the Report
> Server, which is basically a website, you may be using the default
> ASPNET account that has minimal permissions in the system. On
> occasion, explicit settings are overridden in ASP.NET and IIS.
> Depending on your circumstances, you could try embedding the image in
> the report. Also, you might want to check the settings in one of
> the .config files in the path: C:\Program Files\Microsoft SQL Server
> \MSSQL.x\Reporting Services\ReportManager it may be overriding your
> domain admin account. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Friday, March 23, 2012

problems with ReportViewer Control: Not accepting parameters

Hello all,
I am trying to use the reportviewer control to publish reports in
another website to provide customised interface.
I am using the example at
http://www.odetocode.com/Articles/128.aspx to try to make it work.
It works fine normal. I get a frame of params and after Submitting i
get the report in another frame.
When I try to hide the paramters frame and send parameters through the
Reportpath property I get the following errors.
When I set a param a value this is the error I get:
An attempt was made to set a report parameter 'param_IdServer' that is
not defined in this report. (rsUnknownReportParameter) Get Online Help
When I dont pass any params but still have the command
rc:Parameters=false& in the URL i get the follow error.
The path of the item '/projectname/reportname,' is not valid. The full
path must be less than 260 characters long, must start with slash;
other restrictions apply. Check the documentation for complete set of
restrictions. (rsInvalidItemPath) Get Online Help
I would like to know where is the problem.
Thanks
RaviI'm also trying to use the ReportViewer control, to some success.
Assuming you are passing parameters through code, you can use the
SetQueryParameter(), which is the same as the Ode To Code example.
Remember, if it is not a report parameter, always include the prefix (i.e.
rc, rs). Without these prefixes, the parameters are treated as report
parameters and you get the rsUnknownReportParameter error.
SetParameter("rc:Parameters","false");
SetParameter("rc:DocMap","false");
SetParameter("rc:LinkTarget","_top");
The rsInvalidItemPath error is usually due to some invalid characters on the
URL string. Try removing the "amp;" on your url.
Hope this helps.
Marlon
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1108516396.906611.247880@.f14g2000cwb.googlegroups.com...
> Hello all,
> I am trying to use the reportviewer control to publish reports in
> another website to provide customised interface.
> I am using the example at
> http://www.odetocode.com/Articles/128.aspx to try to make it work.
> It works fine normal. I get a frame of params and after Submitting i
> get the report in another frame.
> When I try to hide the paramters frame and send parameters through the
> Reportpath property I get the following errors.
> When I set a param a value this is the error I get:
> An attempt was made to set a report parameter 'param_IdServer' that is
> not defined in this report. (rsUnknownReportParameter) Get Online Help
> When I dont pass any params but still have the command
> rc:Parameters=false& in the URL i get the follow error.
> The path of the item '/projectname/reportname,' is not valid. The full
> path must be less than 260 characters long, must start with slash;
> other restrictions apply. Check the documentation for complete set of
> restrictions. (rsInvalidItemPath) Get Online Help
> I would like to know where is the problem.
> Thanks
> Ravi
>|||I am back to square one. The customer doesnt want the web service
interface as well. After comparing the pros and cons he wants the
Reportviewer Control.
Here is how its supposed to look.
A frame based page.
Left Frame --> a treeview or a menu like listing of all the reports
Right Frame --> when the user clicks a report I should render the
Reportviewer object for that report.
Now I have some questions of the Reportviewer as well.
1. Can I seperate the params part and the report part into 2 diff
frames ?
Thx
Ravi|||You might find these examples helpful.
http://msdn.microsoft.com/msdnmag/issues/04/08/SQLServerReportingServices/default.aspx
http://odetocode.com/Articles/95.aspx
Marlon
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1108750314.788207.212850@.g14g2000cwa.googlegroups.com...
>I am back to square one. The customer doesnt want the web service
> interface as well. After comparing the pros and cons he wants the
> Reportviewer Control.
> Here is how its supposed to look.
> A frame based page.
> Left Frame --> a treeview or a menu like listing of all the reports
> Right Frame --> when the user clicks a report I should render the
> Reportviewer object for that report.
> Now I have some questions of the Reportviewer as well.
> 1. Can I seperate the params part and the report part into 2 diff
> frames ?
> Thx
> Ravi
>|||hey TechnoSpyke,
thanks for the links. They helped me a lot.
Ravi

Problems with replicaion to JET 4.0 subscribers

Hi,
I have a couple of queries I wonder if anyone can help me with.
Merge replcation to Access with Merge ActiveX control.
Using MSDE subscriber, takes about 30 secs to download schema and
data.Using Access, takes about 5 minutes, and the file size is about 5
times what I would expect. Data is correct, though. Is this just
excessive freespace being allocated?
JET 4.0 Transactional replication subscriber.
I have set up a linked server to act as a subscriber. I can access the
subscriber database via the linked server, and apparently add it as a
subscriber via 'Configure Publishers / Subscribers' - but when trying
to set up a push subscription, it does not show as an enabled
subscriber.
I set up the linked server via sp_addlinkedserver, set rpc on in both
directions and collation compatible with sp_serveroption, and set the
login to the linked server to 'Admin'
I've obviously missed a trick, but I cant see what it is...
Many thanks for your help
Duncan Tilley
Is this publication enabled for jet subscribers? I suspect it is not.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Duncan Tilley" <me@.privacy.net> wrote in message
news:0kde129mhmhb6af21jgcnn2q94fioguvl2@.4ax.com...
> Hi,
> I have a couple of queries I wonder if anyone can help me with.
>
> Merge replcation to Access with Merge ActiveX control.
> Using MSDE subscriber, takes about 30 secs to download schema and
> data.Using Access, takes about 5 minutes, and the file size is about 5
> times what I would expect. Data is correct, though. Is this just
> excessive freespace being allocated?
> JET 4.0 Transactional replication subscriber.
> I have set up a linked server to act as a subscriber. I can access the
> subscriber database via the linked server, and apparently add it as a
> subscriber via 'Configure Publishers / Subscribers' - but when trying
> to set up a push subscription, it does not show as an enabled
> subscriber.
> I set up the linked server via sp_addlinkedserver, set rpc on in both
> directions and collation compatible with sp_serveroption, and set the
> login to the linked server to 'Admin'
> I've obviously missed a trick, but I cant see what it is...
>
> Many thanks for your help
> Duncan Tilley
>
|||On Thu, 16 Mar 2006 07:21:59 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>Is this publication enabled for jet subscribers? I suspect it is not.
Hi Hilary
Thanks for the reply.
Err, yes, you're right... Now I feel stupid...
Many Thanks
Duncan
sql

Wednesday, March 7, 2012

Problems with DateTime

Hello all,
I have a problem when I try to insert with a SQL Command a value obtained from a Calendar Control in a SQL Server table. I get the date from the calendar and I put it in a TextBox, but when I try to insert this value in a DateTime defined field I have the following error:
The string or binary data would be truncated. The instruction was finished.

Could you help me?

Thanks.You need to convert the text into a c#/vb date type first. You're prob' hitting a localization issue with the date format.|||I'm certainly no SQL server pro but from my limited experience, SQL server is persnickety about datetime values. I avoid the problem altogether by passing the date in your SQL statement as a string (wrapping it w/in single quotes) or, if you are using sprocs, converting it to datetime w/in the sproc in SQL Server.

good luck.|||No don't wrap it as a string that's exactly where you start getting the problems. If you wrap it as a string you need to either know the database format (and be able to convert to it) or you have to convert to a universal format. Much better to use a param.

Monday, February 20, 2012

Problems Using Temporary Tables

I am declaring a temp table in control flow via Execute SQL task. I then want to use that table as a source in a data task, keep it alive going back to control flow and use it as a destination in another data task following. I am having trouble just getting the first data flow to access that temp table.

In my Execute SQL task, I'm creating a simple temp table. After executing that task, I can head to the data task and change my OLE DB Source to point to that temp table, but I recieve an error about an invalid table name when I attempt to map columns or hit "OK."

Is there something else I need to do to use that temp table as a source or can I only reference it through SQL queries?

Thank you.

I have found them to be unreliable and problematic to use in SSIS. The meta-data issues during dev. for example are a real pain, and the difficulty of controlling the lifetime of the temp tables is another.

I am now using table variables where I previously used temp tables. Table variables are typically faster for small row counts but slower with greater row counts. Unfortunately, I am using them for greater row counts and my sql statement went from 5 1/2 minutes to 8 1/2 minutes for about 250,000 rows.

|||How would I go about doing that though? I would like to try it out even though I'm aware of the limited success.
|||

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

|||

Although you can use temporary tables in tempdb (#MyTempTable) by setting RetainSameConnection, you might prefer creating a temporary table in an ordinary database (MyTempTable), without setting RetainSameConnection:

Create the table ahead of time. Truncate it before using it on each invocation.

Cheers/Kristian

|||

kenambrose wrote:

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

Right, I've seen this answer before, and actually did this. It allowed me to see the temp table after executing the task (as #TempTable_0283817, or something similar) but when I select it, I recieve an error.

I will reply later with the specific error in a few hours when I can get to the SQL box.
|||Here is the error message that pops up when I try to map columns to the temp table I chose from the drop down.

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Destination [340]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [OLE DB Destination [340]]: Opening a rowset for "[dbo].[#TestTable_000000002219]" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

|||

Yes that works of course but then the SSIS user context needs "create object" and "write" permissions on the source data server (unless you are willing to suffer the performance disaster of heterogenerous joins). Bad and/or impossible for most extraction scenarios.....

Ken

|||That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian|||

Kristian Wedberg wrote:

That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian

When you say "staging database" are you referring to an object in the SSIS package or is that just a general term?

Basically, what I want to do is run through the entire input file, validate it, and record any errors I find before inserting any DB records.
|||

In my use of temp tables and the OLE DB Source or Destination I have resorted to using the Advanced Editor where you can create the columns and mappings "by hand". An easy way to see how to set them up is to create a table in your dev db that looks like your temp table. Set up the source or destination using that table and map your columns. Then open up the Advanced Editor.

One thing I found with temp tables. Of course you know about the RetainSameConnection setting. I was using the same connection to create the temp table, fill it, then open it so that I could write to another table on the same connection. Where I write to another table on the same connection I had to create a new separate connection and use it to write to the other table. Otherwise it seemed the viability of the temp table was uncertain when I run a second parallel data flow task where that temp table would be used as well.

Put another way, I use one connection to create the temp table, fill it and read from it. I do not use that same connection to write or read from any other tables. This may not be effecient but it was the only way I could get things to work in parallel.

Hope this helps,
David Martin

|||We are loading data from several source systems simultaneously. Each system generates a set of identical files using the Raw Data File destination. All extracts share common load procedures that pick up the generated Raw Data files through an expression variable. It's a pretty slick way around temp tables and seems to work pretty fast. Would be nicer if there was a Recordset source and we didn't have to create the file on the server but for now there isn't one. You may want to look at that approach as a way around temporary tables.|||

Martin is absolutely right. Raw files are akin to temp tables.

He is correct that there is no recordset source (and with good reason) but you can build one yourself as explined here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

You can't append to a recordset destination unfortunately. If you think that would be nice functionality to have then vote for it here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=6831c0b9-ef74-4132-a137-b073a950d3fd

-Jamie

|||I meant "staging database" as a general term, i.e. it's often useful for the SSIS part to have access to a database where it can write and read arbitrary tables, even if that database is different from both your source database and your target database.|||

<< we didn't have to create the file on the server >>

That's fine if:

a: Your package runs local to your source data (very rare I expect)

or

b: You do not need to reference temp data structures in the queries that populate the raw data files.

But when you need the functionality of temporary data structures when working with queries that reference remote source data, it's either heterogenerous joins (death to performance) or table variables.

Ken

Problems Using Temporary Tables

I am declaring a temp table in control flow via Execute SQL task. I then want to use that table as a source in a data task, keep it alive going back to control flow and use it as a destination in another data task following. I am having trouble just getting the first data flow to access that temp table.

In my Execute SQL task, I'm creating a simple temp table. After executing that task, I can head to the data task and change my OLE DB Source to point to that temp table, but I recieve an error about an invalid table name when I attempt to map columns or hit "OK."

Is there something else I need to do to use that temp table as a source or can I only reference it through SQL queries?

Thank you.

I have found them to be unreliable and problematic to use in SSIS. The meta-data issues during dev. for example are a real pain, and the difficulty of controlling the lifetime of the temp tables is another.

I am now using table variables where I previously used temp tables. Table variables are typically faster for small row counts but slower with greater row counts. Unfortunately, I am using them for greater row counts and my sql statement went from 5 1/2 minutes to 8 1/2 minutes for about 250,000 rows.

|||How would I go about doing that though? I would like to try it out even though I'm aware of the limited success.|||

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

|||

Although you can use temporary tables in tempdb (#MyTempTable) by setting RetainSameConnection, you might prefer creating a temporary table in an ordinary database (MyTempTable), without setting RetainSameConnection:

Create the table ahead of time. Truncate it before using it on each invocation.

Cheers/Kristian

|||

kenambrose wrote:

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

Right, I've seen this answer before, and actually did this. It allowed me to see the temp table after executing the task (as #TempTable_0283817, or something similar) but when I select it, I recieve an error.

I will reply later with the specific error in a few hours when I can get to the SQL box.|||Here is the error message that pops up when I try to map columns to the temp table I chose from the drop down.

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Destination [340]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [OLE DB Destination [340]]: Opening a rowset for "[dbo].[#TestTable_000000002219]" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK|||

Yes that works of course but then the SSIS user context needs "create object" and "write" permissions on the source data server (unless you are willing to suffer the performance disaster of heterogenerous joins). Bad and/or impossible for most extraction scenarios.....

Ken

|||That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian|||

Kristian Wedberg wrote:

That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian

When you say "staging database" are you referring to an object in the SSIS package or is that just a general term?

Basically, what I want to do is run through the entire input file, validate it, and record any errors I find before inserting any DB records.|||

In my use of temp tables and the OLE DB Source or Destination I have resorted to using the Advanced Editor where you can create the columns and mappings "by hand". An easy way to see how to set them up is to create a table in your dev db that looks like your temp table. Set up the source or destination using that table and map your columns. Then open up the Advanced Editor.

One thing I found with temp tables. Of course you know about the RetainSameConnection setting. I was using the same connection to create the temp table, fill it, then open it so that I could write to another table on the same connection. Where I write to another table on the same connection I had to create a new separate connection and use it to write to the other table. Otherwise it seemed the viability of the temp table was uncertain when I run a second parallel data flow task where that temp table would be used as well.

Put another way, I use one connection to create the temp table, fill it and read from it. I do not use that same connection to write or read from any other tables. This may not be effecient but it was the only way I could get things to work in parallel.

Hope this helps,
David Martin

|||We are loading data from several source systems simultaneously. Each system generates a set of identical files using the Raw Data File destination. All extracts share common load procedures that pick up the generated Raw Data files through an expression variable. It's a pretty slick way around temp tables and seems to work pretty fast. Would be nicer if there was a Recordset source and we didn't have to create the file on the server but for now there isn't one. You may want to look at that approach as a way around temporary tables.|||

Martin is absolutely right. Raw files are akin to temp tables.

He is correct that there is no recordset source (and with good reason) but you can build one yourself as explined here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

You can't append to a recordset destination unfortunately. If you think that would be nice functionality to have then vote for it here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=6831c0b9-ef74-4132-a137-b073a950d3fd

-Jamie

|||I meant "staging database" as a general term, i.e. it's often useful for the SSIS part to have access to a database where it can write and read arbitrary tables, even if that database is different from both your source database and your target database.|||

<< we didn't have to create the file on the server >>

That's fine if:

a: Your package runs local to your source data (very rare I expect)

or

b: You do not need to reference temp data structures in the queries that populate the raw data files.

But when you need the functionality of temporary data structures when working with queries that reference remote source data, it's either heterogenerous joins (death to performance) or table variables.

Ken

Problems Using Temporary Tables

I am declaring a temp table in control flow via Execute SQL task. I then want to use that table as a source in a data task, keep it alive going back to control flow and use it as a destination in another data task following. I am having trouble just getting the first data flow to access that temp table.

In my Execute SQL task, I'm creating a simple temp table. After executing that task, I can head to the data task and change my OLE DB Source to point to that temp table, but I recieve an error about an invalid table name when I attempt to map columns or hit "OK."

Is there something else I need to do to use that temp table as a source or can I only reference it through SQL queries?

Thank you.

I have found them to be unreliable and problematic to use in SSIS. The meta-data issues during dev. for example are a real pain, and the difficulty of controlling the lifetime of the temp tables is another.

I am now using table variables where I previously used temp tables. Table variables are typically faster for small row counts but slower with greater row counts. Unfortunately, I am using them for greater row counts and my sql statement went from 5 1/2 minutes to 8 1/2 minutes for about 250,000 rows.

|||How would I go about doing that though? I would like to try it out even though I'm aware of the limited success.|||

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

|||

Although you can use temporary tables in tempdb (#MyTempTable) by setting RetainSameConnection, you might prefer creating a temporary table in an ordinary database (MyTempTable), without setting RetainSameConnection:

Create the table ahead of time. Truncate it before using it on each invocation.

Cheers/Kristian

|||

kenambrose wrote:

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

Right, I've seen this answer before, and actually did this. It allowed me to see the temp table after executing the task (as #TempTable_0283817, or something similar) but when I select it, I recieve an error.

I will reply later with the specific error in a few hours when I can get to the SQL box.|||Here is the error message that pops up when I try to map columns to the temp table I chose from the drop down.

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Destination [340]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [OLE DB Destination [340]]: Opening a rowset for "[dbo].[#TestTable_000000002219]" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK|||

Yes that works of course but then the SSIS user context needs "create object" and "write" permissions on the source data server (unless you are willing to suffer the performance disaster of heterogenerous joins). Bad and/or impossible for most extraction scenarios.....

Ken

|||That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian|||

Kristian Wedberg wrote:

That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian

When you say "staging database" are you referring to an object in the SSIS package or is that just a general term?

Basically, what I want to do is run through the entire input file, validate it, and record any errors I find before inserting any DB records.|||

In my use of temp tables and the OLE DB Source or Destination I have resorted to using the Advanced Editor where you can create the columns and mappings "by hand". An easy way to see how to set them up is to create a table in your dev db that looks like your temp table. Set up the source or destination using that table and map your columns. Then open up the Advanced Editor.

One thing I found with temp tables. Of course you know about the RetainSameConnection setting. I was using the same connection to create the temp table, fill it, then open it so that I could write to another table on the same connection. Where I write to another table on the same connection I had to create a new separate connection and use it to write to the other table. Otherwise it seemed the viability of the temp table was uncertain when I run a second parallel data flow task where that temp table would be used as well.

Put another way, I use one connection to create the temp table, fill it and read from it. I do not use that same connection to write or read from any other tables. This may not be effecient but it was the only way I could get things to work in parallel.

Hope this helps,
David Martin

|||We are loading data from several source systems simultaneously. Each system generates a set of identical files using the Raw Data File destination. All extracts share common load procedures that pick up the generated Raw Data files through an expression variable. It's a pretty slick way around temp tables and seems to work pretty fast. Would be nicer if there was a Recordset source and we didn't have to create the file on the server but for now there isn't one. You may want to look at that approach as a way around temporary tables.|||

Martin is absolutely right. Raw files are akin to temp tables.

He is correct that there is no recordset source (and with good reason) but you can build one yourself as explined here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

You can't append to a recordset destination unfortunately. If you think that would be nice functionality to have then vote for it here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=6831c0b9-ef74-4132-a137-b073a950d3fd

-Jamie

|||I meant "staging database" as a general term, i.e. it's often useful for the SSIS part to have access to a database where it can write and read arbitrary tables, even if that database is different from both your source database and your target database.|||

<< we didn't have to create the file on the server >>

That's fine if:

a: Your package runs local to your source data (very rare I expect)

or

b: You do not need to reference temp data structures in the queries that populate the raw data files.

But when you need the functionality of temporary data structures when working with queries that reference remote source data, it's either heterogenerous joins (death to performance) or table variables.

Ken