Intermittent data source problem - trying to access a deleted data source

Hello
I am using Reporting Services within Visual Studio .Net 2003, connecting to
a SQL Server 2000 database.
I am having an intermittent problem with a report misbehaving. The report
has 5 subreports, all of which work fine on their own (for the most part).
The problem is that, sometimes, when I run the main report I get the
following error:-
"An error occurred while executing the subreport ¡OppsHotProspects¢: An
error has occurred during report processing.
Cannot create a connection to data source 'GuessDB'."
This error sometimes only appears for one report, sometimes for all. The
issue is that the 'GuessDB' data source is no longer used; in its place the
'GuessDBLive' data source is now used. I have removed the 'GuessDB' data
source.
When I run each subreport by itself they run fine, and the data source used
in both the Data tab and the Preview tab is the correct one. One
subreport, however, intermittently misbehaves and gives the same error on
its own.
I am very confused as to why this is happening. Presumably something in
each subreport is referencing the deleted data source, but I can't figure
it out. The deleted one should not be getting used at all.
I'd appreciate any help!
Thanks
DeniseFor anyone else having the same problem, I think I've found the answer.
Out of sheer frustration, I examined the code on each report, and on one
there was a chunk of code which referenced the obsolete data source. I've
no idea why this code remained on one report and not the others, but it
did. I removed the offending code, and the problem (touch wood!) has been
resolved.
On Thu, 3 Nov 2005 12:03:38 +0000, Denise wrote:
> Hello
> I am using Reporting Services within Visual Studio .Net 2003, connecting to
> a SQL Server 2000 database.
> I am having an intermittent problem with a report misbehaving. The report
> has 5 subreports, all of which work fine on their own (for the most part).
> The problem is that, sometimes, when I run the main report I get the
> following error:-
> "An error occurred while executing the subreport ¡OppsHotProspects¢: An
> error has occurred during report processing.
> Cannot create a connection to data source 'GuessDB'."
> This error sometimes only appears for one report, sometimes for all. The
> issue is that the 'GuessDB' data source is no longer used; in its place the
> 'GuessDBLive' data source is now used. I have removed the 'GuessDB' data
> source.
> When I run each subreport by itself they run fine, and the data source used
> in both the Data tab and the Preview tab is the correct one. One
> subreport, however, intermittently misbehaves and gives the same error on
> its own.
> I am very confused as to why this is happening. Presumably something in
> each subreport is referencing the deleted data source, but I can't figure
> it out. The deleted one should not be getting used at all.
> I'd appreciate any help!
> Thanks
> Denise

Interesting scenario - Multiple files updating one table

Hi,

i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...

Here is my problem...

The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.

File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...

The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.

What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I

Regards
Mike

You have more than one way to accomplish that, I think.

You could add an extra column to the staging table that will act as a flag to indicate whether a row was properly updated by the 2 file or not; then further steps should filter the rows based on the value of that column.

Or...

Why you don't create 2 staging tables; one for each file. Then you can use SQL statements to join them, perform some data quality checks and decide which rows are going to be processed and which ones would be rejected.

|||You could also use a Merge Join transform with an inner join prior to loading the table - you would take the good record pipelines from the two flat file sources into the Merge Join and set up an inner join on the key from each file. The pipeline output from the Merge Join would then have only the ten records that had a key match and all 9 columns, which you can then load to the SQL table desitination.|||Thanks, I used the flag method and its doing fine.

Much Appreciated Rafael
Mike

Interesting scenario - Multiple files updating one table

Hi,

i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...

Here is my problem...

The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.

File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...

The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.

What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I

Regards
Mike

You have more than one way to accomplish that, I think.

You could add an extra column to the staging table that will act as a flag to indicate whether a row was properly updated by the 2 file or not; then further steps should filter the rows based on the value of that column.

Or...

Why you don't create 2 staging tables; one for each file. Then you can use SQL statements to join them, perform some data quality checks and decide which rows are going to be processed and which ones would be rejected.

|||You could also use a Merge Join transform with an inner join prior to loading the table - you would take the good record pipelines from the two flat file sources into the Merge Join and set up an inner join on the key from each file. The pipeline output from the Merge Join would then have only the ten records that had a key match and all 9 columns, which you can then load to the SQL table desitination.|||Thanks, I used the flag method and its doing fine.

Much Appreciated Rafael
Mike

Interactive Sort on date field

I have an RS 2005 June CTP report where the data source is an MDX query against AS 2005 June CTP. I'm using Interactive Sort on several columns and it's working fine for the numeric and string fields, but it's sorting the date columns alphabetically instead of chronologically. Is there a way I can specify that these fields are date fields and should be sorted chronologically?
Thanks,
Dirk

Most likely the date field is returned as string through the provider. Try to explicitly convert it with the CDate(...) function in the sort expression. E.g. =CDate(Fields!OrderDate.Value)
-- Robert

|||That did the trick, thanks for the help.|||

I'm also using Interactive Sort on several columns in MSRS 2005,
my problem is with sorting the currency columns (columns with $ sign) - it doesn't work, dallor ammounts are not sorted in accending/decending order. Is there a way I can specify that these fields be sorted as numbers?

Thanks!

|||

It sounds like you are sorting on strings (because the underlying dataset field contains a string rather than a numeric value).

You should sort on a numeric value of the dollar amount - e.g. by changing the dataset query to return the numeric value instead of the string. You can then still apply the currency formatting in the report by using the Format property on textboxes and set it to e.g. C0 (for currency formatting with zero decimals)

-- Robert

Interactive Sort on date field

I have an RS 2005 June CTP report where the data source is an MDX query against AS 2005 June CTP. I'm using Interactive Sort on several columns and it's working fine for the numeric and string fields, but it's sorting the date columns alphabetically instead of chronologically. Is there a way I can specify that these fields are date fields and should be sorted chronologically?
Thanks,
Dirk

Most likely the date field is returned as string through the provider. Try to explicitly convert it with the CDate(...) function in the sort expression. E.g. =CDate(Fields!OrderDate.Value)
-- Robert

|||That did the trick, thanks for the help.|||

I'm also using Interactive Sort on several columns in MSRS 2005,
my problem is with sorting the currency columns (columns with $ sign) - it doesn't work, dallor ammounts are not sorted in accending/decending order. Is there a way I can specify that these fields be sorted as numbers?

Thanks!

|||

It sounds like you are sorting on strings (because the underlying dataset field contains a string rather than a numeric value).

You should sort on a numeric value of the dollar amount - e.g. by changing the dataset query to return the numeric value instead of the string. You can then still apply the currency formatting in the report by using the Format property on textboxes and set it to e.g. C0 (for currency formatting with zero decimals)

-- Robert

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration Services and Teradata as source

Hi All,

Please let me know whether we can use Teradata as Source in SSIS (Target is SQL Server 2005). ie. Do we have oledb driver for NCR Teradata to connect to it. Our SSIS will be hosted in 64 bit SQL, but for development we use 32 bit.

Any inputs is really appreciated.

Regards, kart

NCR provide an OLE-DB provider, so I would guess that would be your first option. Saying that I do not know if it supported within SSIS, not all providers are, you need a minimum set of features to be implemented in the provider, and implemented correctly according to the specification. Give it a try.

ETI also produce a SSIS connector for Teradata which may be worth a look-

ETI - High Performance Data Integration
(http://www.eti.com/microsoft/)

|||

There are various ways to conneto to Teradata. Their OleDB provider supports both 32 and 64 bit. They also have a new .NET provider, though I'm not sure how good it works with SSIS.

iWay has also Teradata providers (OleDB, ODBC) which could be used.

One thing to note here is that Teradata providers do not support Commands, though they support tables and views. As a workaround, some of our users use linked server in SQL 2005 and got the Commands working that way.

You can also check our young but improving connectivity portal for Teradata and other data sources : http://ssis.wiki.com/Data_Sources

|||

HI Deniz and Daren,

Thanks for the info.

It would be helpful to me if you could provide some inputs on Cost Factor w.r.t ETI and iWay and any other implementation details. I assume, using ETI/iWay connectors is a better approach than using OLEDB Driver provided by NCR - as these improve the access performance.

Regards

Kart

|||

Kart, I think it would be better if you did the research. I don’t know your business requirements, technical constraints, your budget, or quantities you would require, all of which may effect the cost and eventual product selection process. Talk to the suppliers and find out what they can do for you.

|||

We am trying to use the OLEDB provider from Teradata for SSIS and running into the following issues

We are able to connect to the Teradata database using the drivers. When we use the import wizard, the authentication screen into Teradata takes about 5 minutes to return. The screen that brings back the list of tables in Teradata to select from takes about half an hour to come back. The screen after that for mappings also takes a very long time to come back.

So, as you can see, every action on the wizard is taking way too long to come back and we are unable to complete the most basic package creation. We tried to manually create the package as well without using the wizard. Even that takes a long time to come back with the list of tables to select from.

Are there any such known issues with using the Teradata drivers with SSIS. This is making development impossible with Teradata OLEDB Drivers.