DataSource Renaming

How do you check to find a data source....We were testing a cube, decided that we wanted to change the names of the dimensions, datasources, etc.

I thought I had gone though each data source, dimension, data source view, that had a reference to datasource view "A" and changed it to reference "B". I did this by going into the view code and finding each reference to "A" and replacing it with "B". However, now when I try to process a dimension, I get the following error message:

The 'DataSourceView' with 'ID' = 'A' doesn't exist in the collection.

Well, I didn't want it to exist because I thought I had changed it to reference "B". Every time I try to go in to the view code section to see if I missed a reference to "A", it says that it can't find a "A".

I was just wondering if there is another way that I could go about finding if I have truly changed all references from "A" to "B"

Every object in SSAS has a name and ID propety. You can change the name freely, but the ID is not meant to be changed as the ID can be used all over the place internally.The Id's are unique within their object type, but not within the database, so doing a straight search and replace in the script can create problems.

If you haven't already tried this, Try connecting to the server, right clicking on the database and getting it to script the whole database as an alter script and then search through for datasource "A".

Datasource error after deployment

reports run fine in studio. When I deploy the reports that reference a 2k
box, I get an error saying I'm unablt to connect to the data source unless we
promt for name and password and pass them as windows credentials. How do I
use the user's windows credentials?Once I granted access to nt authority\anonymous logon, my problems
disappeared. Is this an IIS setting?
"Jeff Ericson" wrote:
> reports run fine in studio. When I deploy the reports that reference a 2k
> box, I get an error saying I'm unablt to connect to the data source unless we
> promt for name and password and pass them as windows credentials. How do I
> use the user's windows credentials?

Datasource and Catalog

Hi all,

I accidentally deleted the datasource including the root data source and catalog from reporting services running in SQL server 2005. As a result, my reports didn't work. What should I do? Thanks!

lk_spec

Create a new datasource and link the report to the new datasources. What do you mean by catalog ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for you reply!

The data source was created dynamiclly through web services and SOAP. Before I deleted the data source, my reports worked fine. After that, however, it didn't work. I wonder if it's related to the root data source or something. Any help is appreciated!

|||The datasource is saved in the RDL as:

<rd:DataSourceID>e80dd27e-83d3-475c-b4c7-81261ee98f08</rd:DataSourceID>

with an ID, I don′t know if you can recover that. I you have the chance to reploy everything, I would go this way.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jen,

Where did I find the datasource id. I checked the rdl file, but there wasn't such information. I'm really new at this stuff.

|||Did you have a view on the RDL in VS or the RDL in the Report Manager, you can download the RDL in Report Manager by Opening the report > Properties > Edit Report, you will get the RDL file downloaded which contained (in my case) this GUID.

HTH, Jens Suessmeyer.

http.//www.sqlserver2005.de|||

Jen,

Thanks for your reply!

I was able to create a new data source. The problem now is, however, an error message saying that the item "/" cannot be found. The application failed when it tried to FindCatalogItems:

rsws.FindItems(folderName, BooleanOperatorEnum.Or, conditions)

Any help would be appreciated!

lk_spec

|||

If you take regular backup of database than restore the database.

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated Security,
Initial Catalog is the database where the stored procedure is. The stored
procedure selects data from a different database on the server. This server
is running SQL Server 200 sp2 (so pre-Cross-Database-Ownership Chaining
option). The users can execute the stored procedure fine so it does not seem
to be a permissions issue to the data. The Report runs fine FROM the Report
Server. But, if I try to run the report from a different machine, I get:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSourceNameIsHere'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
The report also runs fine if we don't use Windows Integrated Security. The
report runs fine if the datasource (both databases) are on SQL2000 sp3. Is
there anything that we can do to get this working on SQL2000 sp2? I'm not
sure that all systems on this server are supported on sp3 (3a or 4). It hosts
several databases from purchased products.I found this in the Reporting Services Log:
ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'DataSourceNameIsHere'., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'DataSourceNameIsHere'. -->
System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
Michelle
"Michelle" wrote:
> We have a report in RS 2005, data source uses Windows Integrated Security,
> Initial Catalog is the database where the stored procedure is. The stored
> procedure selects data from a different database on the server. This server
> is running SQL Server 200 sp2 (so pre-Cross-Database-Ownership Chaining
> option). The users can execute the stored procedure fine so it does not seem
> to be a permissions issue to the data. The Report runs fine FROM the Report
> Server. But, if I try to run the report from a different machine, I get:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'DataSourceNameIsHere'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
> The report also runs fine if we don't use Windows Integrated Security. The
> report runs fine if the datasource (both databases) are on SQL2000 sp3. Is
> there anything that we can do to get this working on SQL2000 sp2? I'm not
> sure that all systems on this server are supported on sp3 (3a or 4). It hosts
> several databases from purchased products.sql

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated

Security, Initial Catalog is the database where the stored procedure

is. The stored procedure selects data from a different database on the

server. This server is running SQL Server 200 sp2 (so

pre-Cross-Database-Ownership Chaining option). The users can execute

the stored procedure fine so it does not seem to be a permissions issue

to the data. The Report runs fine FROM the Report Server. But, if I try

to run the report from a different machine, I get:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSourceNameIsHere'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

The report also runs fine if we don't use Windows Integrated Security.

The report runs fine if the datasource (both databases) are on SQL2000

sp3. Is there anything that we can do to get this working on SQL2000

sp2? I'm not sure that all systems on this server are supported on sp3

(3a or 4). It hosts several databases from purchased products.I found this in the Reporting Services Log:

ERROR: Throwing

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source 'DataSourceNameIsHere'., ;

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSourceNameIsHere'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Michelle

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated

Security, Initial Catalog is the database where the stored procedure

is. The stored procedure selects data from a different database on the

server. This server is running SQL Server 200 sp2 (so

pre-Cross-Database-Ownership Chaining option). The users can execute

the stored procedure fine so it does not seem to be a permissions issue

to the data. The Report runs fine FROM the Report Server. But, if I try

to run the report from a different machine, I get:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSourceNameIsHere'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

The report also runs fine if we don't use Windows Integrated Security.

The report runs fine if the datasource (both databases) are on SQL2000

sp3. Is there anything that we can do to get this working on SQL2000

sp2? I'm not sure that all systems on this server are supported on sp3

(3a or 4). It hosts several databases from purchased products.I found this in the Reporting Services Log:

ERROR: Throwing

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source 'DataSourceNameIsHere'., ;

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSourceNameIsHere'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Michelle

DataSet table as DataFlow Destination Component

Is it possible to use a datatable as destination component in dataflow ?

Sample code Fr MS:
Create the source component.
IDTSComponentMetaData90 source = dataFlow.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();

// Create the destination component.
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();

destination.ComponentClassID = WHAT ? for dataset Table ?

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

thanks in advance
PatrickI saw that the datareader can be used as Destination.
So, my "ask for" is closing.
Thanks
Patricksql

DataSet Problems

Hi,
We have noticed a problem with following:
1. creating a new report
2. adding a DataSet which pulls from a shared data source and displays
information correctly
3. choosing 'Save' and not pulling the fields into the layout form
When we open the report again the dataset is there but all references to the
table we selected are gone.
Is this supposed to happen? We have tried this on 2 separate installations,
one with SP2 and one without.
Any suggestions?Sometimes RS does not recognize that anything has changed in the data tab
and does not save. Just go to the layout tab and click on save all and you
will be good to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
> Hi,
> We have noticed a problem with following:
> 1. creating a new report
> 2. adding a DataSet which pulls from a shared data source and displays
> information correctly
> 3. choosing 'Save' and not pulling the fields into the layout form
> When we open the report again the dataset is there but all references to
> the
> table we selected are gone.
> Is this supposed to happen? We have tried this on 2 separate
> installations,
> one with SP2 and one without.
> Any suggestions?|||Bruce - Thank you, that worked! Would this be fixed in a future release?
Although we were working on a small dataset I would hate to have lost
development time on a larger query?!
"Bruce L-C [MVP]" wrote:
> Sometimes RS does not recognize that anything has changed in the data tab
> and does not save. Just go to the layout tab and click on save all and you
> will be good to go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
> news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
> > Hi,
> > We have noticed a problem with following:
> > 1. creating a new report
> > 2. adding a DataSet which pulls from a shared data source and displays
> > information correctly
> > 3. choosing 'Save' and not pulling the fields into the layout form
> > When we open the report again the dataset is there but all references to
> > the
> > table we selected are gone.
> > Is this supposed to happen? We have tried this on 2 separate
> > installations,
> > one with SP2 and one without.
> >
> > Any suggestions?
>
>|||I have no idea. I have lost work so I do this compulsively now. I'll
probably keep doing it when the bug no longer exists.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:DC256683-1D12-4C55-B578-8A4440F1B41E@.microsoft.com...
> Bruce - Thank you, that worked! Would this be fixed in a future release?
> Although we were working on a small dataset I would hate to have lost
> development time on a larger query?!
> "Bruce L-C [MVP]" wrote:
>> Sometimes RS does not recognize that anything has changed in the data tab
>> and does not save. Just go to the layout tab and click on save all and
>> you
>> will be good to go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
>> news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
>> > Hi,
>> > We have noticed a problem with following:
>> > 1. creating a new report
>> > 2. adding a DataSet which pulls from a shared data source and displays
>> > information correctly
>> > 3. choosing 'Save' and not pulling the fields into the layout form
>> > When we open the report again the dataset is there but all references
>> > to
>> > the
>> > table we selected are gone.
>> > Is this supposed to happen? We have tried this on 2 separate
>> > installations,
>> > one with SP2 and one without.
>> >
>> > Any suggestions?
>>

Dataset as source

Can we have an ADO.NET dataset as a datasource to a MS SQL Reporting service
report ? If so, how do we configure the report to do so ?
Regards,
Chak.here you are!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp
"Chakra" <r_chakravarthy@.hotmail.com> schrieb im Newsbeitrag
news:u5I51OloFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Can we have an ADO.NET dataset as a datasource to a MS SQL Reporting
> service
> report ? If so, how do we configure the report to do so ?
> Regards,
> Chak.
>

Dataset - Command type question

Hi to all
I need to use a set of stored procedures as source of my report datasets, my
problem is that I have several reports already created some of them using
StoredProcedure as command type, and others using Text and using "EXEC
MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
the same way, change one of the sets of reports can cost some resources of
my team.
My question is if no exist differences or I can have problems with this
second type of datasets?
ThanksFor "SQL Server" connections they are for all practical purposes identical.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:#XmzhXedEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> I need to use a set of stored procedures as source of my report datasets, my
> problem is that I have several reports already created some of them using
> StoredProcedure as command type, and others using Text and using "EXEC
> MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
> the same way, change one of the sets of reports can cost some resources of
> my team.
> My question is if no exist differences or I can have problems with this
> second type of datasets?
> Thanks
>sql

Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian

DataReader source output help

I have configured my DataReader to use an ADO.net (ODBC) connectivity (entered Select * from AMPFM) in Sqlcommand and can see my database columns listed in the Advanced Editor / Column mappings window. My process needs to perform a straight column to column population from AMPFM table into my dbo.visitfinancials table. How do I point the output to the above table?

Add an OLE DB destination. Select the DataReader source, connect its green arrow to the OLE DB destination. Double click on the OLE DB destination and configure it for the appropriate connection manager (will have to click "New..." to create a new one) and table name. Be sure to use "Table or view - fast load" in the Data access mode drop down list.

Then select mappings on the left. Map your columns by dragging one to the other. When done, click OK. Run it. If your data types match, this is as easy as it gets.|||

That worked great, thanks Phil. Now of course, you knew my data types would not all match. I am getting the error "Column "ID" cannot convert between unicode and non-unicode string data types. I believe I came across one of your posts that stated to use a derived column to cast the field. My field (ID) is varchar (254) not null. Where would I enter this derived casting procecure?

|||In a derived column, add a new column.

This expression should do the trick.

(DT_STR,254,1252)[your_input_field]|||

Looks like I have ran into a snag. I navigated to DataReader Source / show advanced editor / Input and Output properties / Datareader Output / Output columns / clicked on the add column radio button and received the following error message:

Error at Data Flow task[DataReader Source[1]]: The component does not allow adding columns to this input or output. Additional information: Pipeline component has returned HRESULT error code 0xC0208019 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

Any ideas?

|||You need to use Derived Column transformation for that...it it an item in the toolbox in the data flow.|||

OK, I have added a derived column object from the toolbox, connected it to the datareader and then tried to connect it to my OLEDB data source but get an error saying there are no available inputs. I deleted the mapping for "ID" field between DataReader source and destination OLEDB and then tried to connect "ID" from derived column to the OLE DB data source adn get the same message. I feel like I'm getting close but not quite there yet. I need additiona assistance please.

|||You have the wrong OLE DB component. You have a source on there and you need the OLE DB DESTINATION component instead. It's toward the bottom of the list in the toolbox.|||

I believe I used the wrong terminology in my previous posts and confused the issue. I do have a DataReader source, Derived column control and OLEDB destination. Thanks.

|||The data reader source should be connected to the Derived Column, which should be connected to the OLEDB destination. Three boxes, two lines.

Datareader Source connects to no longer existing ODBC-link

I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).

Hi Johndahl,

A quick question -- when you refer to "changed mappings", are you referring to the connection string used to connect to your Access database, or to column metadata (column names, data types, etc.)? If the latter, you'll want to fix (or even delete and recreate) your source adapters in your data flow components, as that is where column and table metadata are stored.

Also, slightly unrelated to this question, but I wonder if you've considered using the OLE DB Connection Manager/Source to connect to your Access database instead of ADO.Net through the ODBC bridge. The OLE DB provider for Jet should give you better performance and datatype fidelity overall.

-David

|||

Hi David

Thanks for the reply.

The changed mapping refers to the use of another Access-database and a changed network drive mapping. When I setup the connection and test it, the connection tests ok. However when retrieving data (whether in Server Explorer or DataReader Source) it returns an error message: refering to the database-connection no longer in use.

Furthermore, as you suggested, I tried the use of OLEDB but here also I get an error message when trying to retrieve data: "Object reference not set to an instance of an object". Again the connection tests ok.

John

|||

It would help if you could describe step-by-step how you setup your connections and source adapters.

Thanks,

Bob

|||

First the ODBC was setup on the server (System DSN). Then in SSIS a connection was established using the Connection Manager. Provider is the ADO.NET for ODBC. The DSN which was setup in the ODBC Administration is chosen en tested if it can connect. Tests are always succesfull. Also setup is a connection in the Server Explorer.

For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.

However then the network drive mapping and the name of the Access-database changed (the old network drive mapping was deleted !!!). This change was executed in de ODBC Administration using the same DSN. Expected was that this change was enough for SSIS to connect to the correct, new database. However then the troubles began...

Testing the connections resulted in ok results. However upon collecting data through the datareader source we received HY024 errors stating that a connection using the OLD network drive mapping and database name could no longer be established. The question here is of course how it still knows what this old network drive mapping and database name is because the only reference to it (ODBC) is no longer available.

Afterwards we setup a new DSN in ODBC, we worked in a new package and even started a new project. In all cases the problem endured. It looks like SSIS/Visual Studio has somehow, somewhere saved the old network drive mapping and database name and is using it as default ODBC connection not using the definition of the DSN setup in ODBC Administration.

Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?

|||

Could you check the connection string assigned to your ADO.NET connection manager?

I guess the server explorer does some caching. Here is something that might help clean it out:

when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.

HTH,

Bob

|||

I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:

- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

Both the dbq as the systemdb point to the correct paths and databases. Testing the connection results in an OK-status. However when accessing the tables in Server Explorer an error message is generated:

"SQL Execution Error

Executed SQL Statement: SELECT * from table

Error Source: odbcjt32.dll

Error Message: ERROR [HY024][Microsoft][ODBC Microsoft Access Driver] 'H:\path\previousdb.mdb' is not a valid path. Make sure that the correct pathname is spelled correctly and that you are connected to the server on which the file resides"

In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?

|||

Probably cached in the server explorer. Try deleting them and creating new ones.

When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.

HTH,

Bob

Datareader Source connects to no longer existing ODBC-link

I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).

Hi Johndahl,

A quick question -- when you refer to "changed mappings", are you referring to the connection string used to connect to your Access database, or to column metadata (column names, data types, etc.)? If the latter, you'll want to fix (or even delete and recreate) your source adapters in your data flow components, as that is where column and table metadata are stored.

Also, slightly unrelated to this question, but I wonder if you've considered using the OLE DB Connection Manager/Source to connect to your Access database instead of ADO.Net through the ODBC bridge. The OLE DB provider for Jet should give you better performance and datatype fidelity overall.

-David

|||

Hi David

Thanks for the reply.

The changed mapping refers to the use of another Access-database and a changed network drive mapping. When I setup the connection and test it, the connection tests ok. However when retrieving data (whether in Server Explorer or DataReader Source) it returns an error message: refering to the database-connection no longer in use.

Furthermore, as you suggested, I tried the use of OLEDB but here also I get an error message when trying to retrieve data: "Object reference not set to an instance of an object". Again the connection tests ok.

John

|||

It would help if you could describe step-by-step how you setup your connections and source adapters.

Thanks,

Bob

|||

First the ODBC was setup on the server (System DSN). Then in SSIS a connection was established using the Connection Manager. Provider is the ADO.NET for ODBC. The DSN which was setup in the ODBC Administration is chosen en tested if it can connect. Tests are always succesfull. Also setup is a connection in the Server Explorer.

For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.

However then the network drive mapping and the name of the Access-database changed (the old network drive mapping was deleted !!!). This change was executed in de ODBC Administration using the same DSN. Expected was that this change was enough for SSIS to connect to the correct, new database. However then the troubles began...

Testing the connections resulted in ok results. However upon collecting data through the datareader source we received HY024 errors stating that a connection using the OLD network drive mapping and database name could no longer be established. The question here is of course how it still knows what this old network drive mapping and database name is because the only reference to it (ODBC) is no longer available.

Afterwards we setup a new DSN in ODBC, we worked in a new package and even started a new project. In all cases the problem endured. It looks like SSIS/Visual Studio has somehow, somewhere saved the old network drive mapping and database name and is using it as default ODBC connection not using the definition of the DSN setup in ODBC Administration.

Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?

|||

Could you check the connection string assigned to your ADO.NET connection manager?

I guess the server explorer does some caching. Here is something that might help clean it out:

when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.

HTH,

Bob

|||

I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:

- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

Both the dbq as the systemdb point to the correct paths and databases. Testing the connection results in an OK-status. However when accessing the tables in Server Explorer an error message is generated:

"SQL Execution Error

Executed SQL Statement: SELECT * from table

Error Source: odbcjt32.dll

Error Message: ERROR [HY024][Microsoft][ODBC Microsoft Access Driver] 'H:\path\previousdb.mdb' is not a valid path. Make sure that the correct pathname is spelled correctly and that you are connected to the server on which the file resides"

In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?

|||

Probably cached in the server explorer. Try deleting them and creating new ones.

When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.

HTH,

Bob

DataReader Source can not configurate

when I configurate the datareader source using the ODBC connection manager. it show the follow error message:
"Error at Data Flow Task[DataReader Source [562]]: Cannot acquire a managed connection from the run-time connection manager"
this ODBC is connect to IBM DB2.

Can anyone help on this?

Frank,

see this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1

Thanks.

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and ODBC connection to PostgresSQL

Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.

Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -

Have you made any progress?

-robert

|||

After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.

Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.

Best,

Michael Sh

|||

My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.

Do you have file write permissions where this package is running?

What is the enivironment TEMP or TMP variable value set to?

Thanks

Mark

|||

You can tell that the temp directory is located at:

C:\Documents and Settings\<username>\Local Settings\Temp

Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"

It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.

I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -

I did a test and I created a similar package in DTS - it ran quickly and effortlessly.

|||

Thanks for the extra information.

Could you share exactly how many files are in the temp directory at the point of failure?

Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?

Thanks

Mark

|||

I re-viewed the data files- and I found that they are formatted as such:

DTS####.tmp - where # is a hexadecimal character -

I noticed the files were as such:

DTSAAA0.TMP

DTSAAA1.TMP

DTSAAA2.TMP

DTSAAA3.TMP

.....

DTSAAAA.TMP

DTSAAAB.TMP

...

DTSAAAF.TMP

DTSAAB0.TMP

...

and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.

In my dataset, all I have are about 8 columns, only one of which is a TEXT field.

I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)

-rob

|||

Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.

At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.

Thanks
Mark

|||

A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.

Mark

|||

Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...

But next time if it ever happens to me again, I will give it a try!

|||

Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error

Package Package

DataReader Source and Column Types

Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?

Darren,

I am afraid there is no way to influence this mapping. The Data Reader Source adapter uses only the CLR type (the DataType column from the table's schema) to determine which DT_... type to choose. The ProviderType field could not be used as it has different meaning for different providers.

The mapping files would definitely help here, but that infrastructure is not used by this component.

I do not have any good advice, but explicit data conversion to the DT_STR type or building your custom ADO .NET adapter are options I see available at this moment.

Thanks.

|||

Bob,

Thanks for confirming what I already suspected, but I had to ask. I'm thinking a MSDN feedback request for the ability to supply mapping files would be coming your way.

Conversion works, but I'm concerned about the impact of doubling the buffer size each time I do this. Most columns I am working with are DT_WTR, but need to be DT_STR, it is 2 x buffer every time. Is this really twice the size or is there some fancy pointer type work going on? I will probably test when I have time as I had some other ideas about custom components for such conversion, but it depends on what the impact really is.

A custom provider had been considered but currently rejected due to time. It took me long enough to get the managed provider written :)

Thanks

|||

Hi Darren,

I believe you are right about the buffer size. It might impact your package performance, but it is not sure how significant that could be. It may depend on many factors. If you get a chance to measure the impact in your configuration, please share results with us.

If the "power" stays with us, we should be able to provide much better story with managed providers in the next version.

Thanks.

|||

I have done some playing around with this.

For information my theory goes like this. If I use a Data Conversion transform I am increasing the number of columns in my buffer so I get less rows per buffer. This seems inefficient. On the other side we know that copying data between buffers has a cost. So lets test which is more efficient, the larger row size versus the cost of moving between buffers, and keeping a small row size.

I wrote a simple asynchronous component that allowed you to select columns from the input buffer which are then copied directly to the output buffer. The one feature is that any DT_WSTR column is reproduced as DT_STR. So the buffer sizes/structure are the same for input and output except for the change in type, and any associated overheads of each type. One would think that unicode types require twice the space of non-unicode, so this should make the asynchronous component test even faster as this allows even more rows to fit into the output buffer of my component.

For a baseline I used a Script Component -> Union All. The script component generated a variable number of rows, as determined by a package variable. The columns produced are 1 integer column (row count), and 9 x 50 character DT_WSTR columns fully populated.

For testing I used the same script component and two methods of converting the columns -

Script Component -> Data Conversion -> Union All

Script Component -> DeUnicodeAsynchTestComponent -> Union All

Tests showed that the data conversion was 1.5-2.5 times slower than the baseline. The asynchronous component was then 2-2.5 times slower than the data conversion. Times were averaged across 6 executions. The range in times are for different row counts, 100,000 to 1,000,000.

N.B. These ratios are for my local machine, and I would fully expect results to vary on different hardware and with different resource constraints. These are for illustration only. If you want to know how this equates to your environment, test it for yourself, and use real hardware, not a test system.

So, whilst it may not look pretty leaving the buffer alone is the way to go. Trying to remove columns or change columns is a non-starter as this means creating a new buffer, the cost of which far outweighs the benefit of the smaller row size in the buffer. When you do need to work on columns, use a synchronous component such as the Data Conversion or Derived Column transformations, and don’t worry if you end up with more columns that you will use at the end. (Obviously don’t create columns for the sake of it!)

Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.