Datasource Reader - Name for output column is blank.

Hi,

I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-

select max(id), sum(amount) from my_table

OR

select a, b, c, recid(my_table) from my_table

which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'

I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.

Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(

Hi,

I don't know about 'Progress'. But just in a curiosity I am asking this. If you can't have a column alias how will you create a view using the statements that you have mentioned above. If there is any workaround there, u better try the same thing.

|||

At a stretch, it should be possible to create a script source which would execute this query and assign the results to columns in the data flow.

another possibility may be to use an Execute SQL task - if you're not returning too many rows.

However, are you sure column aliases are not supported? The Progress online documentation appears to suggest that it may be. there may be a way in progress to make this happen.

Donald

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 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 Output skipping first line

I am using the following code to query a DB and output an Excel Spreadsheet. For some reason, the first line is always missing.

Any help would be greatly appreciated.

============================================

1reader = cmd.ExecuteReader()2If Not reader.Read()Then3 Return False4Else5 For i = 0To reader.FieldCount - 16 strLine += reader.GetName(i).ToString & Chr(9)7Next8 objStreamWriter.WriteLine(strLine)9 strLine =""10With reader11While .Read12For x = 0To .FieldCount - 113 strLine = strLine & reader.GetValue(x) & Chr(9)14Next15 objStreamWriter.WriteLine(strLine)16 strLine =""17End While18 End With19End If

line 2 of you code is wasting the first row of data by performing a reader.Read that you ignore.

change line 2 to:

If Not reader.HasRows()Then

|||

Perfect!

Thank you!!!!

DataReader output column length

Hello,

I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.
This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :

Datatype : Unicode string [DT_WSTR]
Length : 10

Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?

tanks in advance ...

What are you talking about? How can you have a column declared with a width of 10 and have data that exceeds that length? You might want to double check your source metadata.|||

renyx wrote:

In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.

That is a physical impossibility.

Can you expand more on what you mean?

-Jamie

|||

In Progress a column can be declared as Char(10) while creating the table, but while inserting in the table, a value larger than 10 can be inserted without causing an error.

So I am looking for a way to make the DataReader output column 15 in length.

|||

renyx wrote:

In Progress a column can be declared as Char(10) while creating the table, but while inserting in the table, a value larger than 10 can be inserted without causing an error.

So I am looking for a way to make the DataReader output column 15 in length.

Really? OK, I take it back. Sorry. That's just....bizarre....for want of a better word.

I think you'll be able to go into the Advanced Editor of the Datareader Source and manually edit the column lengths.

|||

Jamie Thomson wrote:

Really? OK, I take it back. Sorry. That's just....bizarre....for want of a better word.

I think you'll be able to go into the Advanced Editor of the Datareader Source and manually edit the column lengths.

I just looked into this some... Progress 4GL does not use the width definition for the storage of data. Character data types in Progress 4GL can be up to 2,000 characters, I believe. The char(10) definition that the OP mentioned is for query results, I believe, and not for storage.

So, how to get around this? Well, do as Jamie suggested and edit the Datareader Source manually using the advanced editor.|||

Thanks for the suggestion, but that did not work.
I also tried a query on the column in management studio (linked server) and that also did not work.

OLE DB provider "MSDASQL" for linked server "LISA" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Column KM4-CODE in table PUB.ARTIKEL has value exceeding its max length or precision.".

Looks like I will have to convince the Progress people to change the datatype.

|||This is a well known Progress problem. Check out http://www.progresstalk.com/archive/index.php?t-76301.html|||By the way, I'm just going on record to say that the Progress leaders should be sent back to logic school for designing a product that enforces the data length on a one-way basis. "Um yeah, we let you store data greater than what's defined, but we won't let you select it back out."

That's the most ridiculous thing I have heard in a long time.|||Same issue happens to me. We import data from a Thoroughbred basic database. The field can be 12 chars long with 20 chars of data. In the Thoroughbred world, this is just an "integrity" problem....Their db still works somehow, but causes fits on our end.|||

If anyone out there wants a humorous outlook on this (and promises not to take offence to SQL zealots), go here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=263&messageid=340643

-Jamie

|||

Thoroughbred still lives? Aaargh! The only implementation of a computer language I ever saw that allowed indefinite GOTOs (I'm not kidding). I had not heard about this "integrity" issue with their DB, but am not surprised.

|||

renyx wrote:

Thanks for the suggestion, but that did not work.
I also tried a query on the column in management studio (linked server) and that also did not work.

OLE DB provider "MSDASQL" for linked server "LISA" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Column KM4-CODE in table PUB.ARTIKEL has value exceeding its max length or precision.".

Looks like I will have to convince the Progress people to change the datatype.

Renyx, did you try changing the SqlCommand select statement to cast the column to something longer?
As you noted, you cannot change the column length in the advanced editor, but doing so in the select statement might work for you.

DataReader Destination Adapter Error!!!

Hi, Buddies,

I have a very simple package in SSIS using a datareader destination as the output result. But when I ran the package, it always timeout and shows: "write 0 rows to the datareader". Why this happened, Can anyone help?

Thank you very much!

AlbertWhat happens if you increase the value of ReadTimeOut (it's a custom property of Data Reader Dest adapter)?

What does your package look like? Can you share it?|||How do you execute the package?

The goal of DataReader Destination is to let you use the result of executing data flow in other applications via DtsClient ADO.NET provider.

So the way you use it is by configuring this "other" application (e.g. Reporting Services or Analysis Services, or other app that supports ADO.NET data source) to use DtsClient ADO.NET provider, configure the provider to execute the particular package and get data from specific DataReader Destination, then run this application. When the application starts to read data from its data source, we instantiate the package and stread data from DataReader Destination to the client application.

If the package containing DataReader Destination is executed stand-alone, without the client reading from DataReader Destination, you get the message "component "DataReaderDest" (NN)" wrote 0 rows".

SQL Books Online contain more detailed infromation and instructions on setting this up.

DataReader

Hi all,

I read somewhere that we have to close the data reader explicitly before getting an output parameter from a stored procedure. I experimented, and this retrieves the right value:

sqlConnection1.Open();
SqlDataReader myReader = sc.ExecuteReader();
lblCatName.Text = "CatName: " + sc.Parameters["@.CatName"].Value;
lblReturn.Text = "Return: " + sc.Parameters["@.RETURN_VALUE"].Value;
myReader.Close();
sqlConnection1.Close();

I found the following in MSDN: " You must explicitly call the Close method when you are through using the SqlDataReader (...) The Close method fills in the values for output parameters (...)"

Am I missing something here? And if both ways work, what is better practice?

Thanks,you don't need a reader for output parameters.

simply sc.ExecuteNonReader();
lblCatName.Text = "CatName: " + sc.Parameters["@.CatName"].value;
...|||Hi,

Of course, you are correct!!! How did I miss that??!!
I tried returning some values from my stored procedure so that it would make sense to use the ExecuteReader, and it is true that the DataReader has to be closed before getting the output and return values!! (ExecuteNonQuery does not raise the problem I described because doesn't have to be closed)

Thanks for your help!!

Bea|||well here's the truth behind it on SQL Server side at least...

If you execute some command, text or a stored procedure, it sort of looks like this (depending on the provider)

DECLARE @.p1 DATATYPE
DECLARE @.p2 DATATYPE
SET @.p1 = <VALUE FOR PARAMETER p1>
SET @.p2 = <VALUE FOR PARAMETER p2>
-- do the command here i.e.
EXEC StoredProcedure @.p1, @.p2 OUTPUT
SELECT @.p1, @.p2 -- this is how it gets its output parameters

tricky eh... well this means that you can't get the output parameters until you close the data reader that is executing from the EXEC StoredProcedure @.p1, @.p2 OUTPUT ...

does this make sense? yes... this is because it does it in order of operation, and you may get zero or more datareaders from the StoredProcedure, while in other providers (Oracle, Odbc) the implementation may be different and thus it may be able to return the output parameters before it streams the readers ...

imagine:

CREATE PROCEDURE StoredProcedure (@.p1 INT, @.p2 INT OUTPUT) AS
BEGIN
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND @.p1 = 1
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N'IsView') = @.p1

SET @.p2 = 5
END

execute this in a stream reader or soemthing like it ... if you pause on the first datareader ... assuming it returns something ... it will not execute the rest of the procedure until you close the stream or ask for the next reader... then it will stream you the next data reader and then you ask for another or close ... once you close you go outside the procedure and it finishes executing ...

so essentially what it should/we would like it to do is

EXECUTE COMMAND:
- GET ALL READERS
- REMOVE THE LAST READER FROM THE LIST OF AVAILABLE READERS AND STORE THE OUTPUT PARAMETERS CHANGES
- ALLOW ACCESS TO THE REMAINDING AVAILABLE READERS

You can see proof of all this if you execute SQL Profiler and an application in debug mode... try stepping through the executing of the procedure and the retrieval of the data reader(s)... also add watches to each of the parameters... to see when their values change...|||Thank you so much for your very complete post! It was extremely helpful!!

Bea

DataDirection output Problems

I have the below code. When the SqlParameter is created the @.UserID is set as input instead of output. anyone have any ideas why this is happining and how I can get it to work. I can create the variable as a single parameter and set the direction to output and it works but when I try it in the code below it won't work. Any help will be appreciated

Endeavor

Dim parametersAs SqlParameter() = {New SqlParameter("@.strSex", Data.SqlDbType.Char, 50), _New SqlParameter("@.intRace", Data.SqlDbType.Int), _New SqlParameter("@.intAge", Data.SqlDbType.Int), _New SqlParameter("@.UserID", Data.SqlDbType.SmallInt, Data.ParameterDirection.Output)}The overload that takes 3 parameters does not have the direction as the 3rd parameter. If you want to do it that way, you'll need the full overload that takes the 10ish parameters.|||

Thanks for your help. OK I don't need all of the rest of the parameters. Is there any way to make the variable an output and still keep it in the array or add it to the array after creating the sqlparameter.

Endeavor

|||cmd.Parameter("@.Name").ParameterDirection=Output

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
You could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards