datatype map - Native ODBC driver

Hi,

Our software uses native ODBC driver and Visual C++ 2003 to communicate with SQL Server 2005.

SELECT xml_schema_namespace(SCHEMA_NAME(schema_id) ,name) FROM sys.xml_schema_collections.

SQLDescribeColW returns -152 as the datatype for the column in the above select statement. However, I’m unable to find a preprocessor definition for -152. The datatype looks like an nvarchar. Is it an nvarchar? Where can I find the definition for this datatype?

I have discovered similar problems while bringing back SQL_VARIANT types. The returned subtype of a SQLVariant comes up with -16 and -25. I cannot find definitions for these sub types either.

Any help is appreciated.

Regards,

Venkat

-152 is the XML data type and is defined in SQLNCLI.H as SQL_SS_XML. SQLNCLI.H is the header file for the SQL Native Client ODBC driver and OLEDB provider for SQL Server 2005.

I would advice you use the SQL Native Client ODBC driver for the best performance and functionality with SQL Server 2005. You can download the redistributable from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Regards,

Uwa.

|||

Thank you very much!

Do you know where i can find information about SQL variant sub types -16 & -25? Subtype -16 looks like an integer (4bytes) while subtype -25 looks like a bigint (8Bytes)

Regards,

|||

-16 = "SQL_INTEGER + SQL_SIGNED_OFFSET" ( = 4 + (-20) )

-25 = "SQL_BIGINT + SQL_SIGNED_OFFSET" ( = -5 + (-20) )

So, basically they're SIGNED LONG and SIGNED BIGINT respectively, which explains their sizes of 4bytes and 8bytes.

You can find these defined in the header file SQLEXT.H:

#define SQL_BIGINT (-5)
...

#define SQL_SIGNED_OFFSET (-20)
#define SQL_UNSIGNED_OFFSET (-22)

....

#define SQL_C_SBIGINT (SQL_BIGINT+SQL_SIGNED_OFFSET) /* SIGNED BIGINT */
...

#define SQL_C_SLONG (SQL_C_LONG+SQL_SIGNED_OFFSET) /* SIGNED INTEGER */

As Uwa mentioned above, you'll need to search SQLNCLI.H for the SQL Server 2005 types. Here you can find (-152) defined:

#define SQL_SS_VARIANT (-150)
#define SQL_SS_UDT (-151)
#define SQL_SS_XML (-152)

|||Thanks a lot!sql

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

DataSet works... DataTable doesnt... (ODBC)

I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example:

'oConn is defined elsewhere...
Dim sErr as String = ""
Dim dt As New DataTable
If Not oConn Is Nothing Then
Try
Dim sSQL as String = "select 1"
Dim oCommand As New OdbcDataAdapter(sSQL, oConn)
oCommand.Fill(dt)
Catch ex As Exception
sErr = "Database Error: " & ex.Message
Finally
sqlCloseConnection(oConn)
End Try
End If

this works fine and my dt DataTable object gets one row. However using this as the SQL:

Dim sSQL as String = "declare @.foo table(mycol integer);insert @.foo select 1;select mycol from @.foo;"

does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with:

Dim ds as DataSet

I can then get the data in ds.Tables(0) no problem.

So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?

When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic.

Any ideas?

Hello my friend,

I tried your code in my application and it worked for me, but I am using the SqlDataAdapter. Why are you using Odbc? Try using the Connection, Command, DataAdapter, etc classes from the System.Data.SqlClient namespace and I think it will work.

Kind regards

Scotty

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 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 ODBC Query Timeout

Hello !

I get in my SSIS Package a Query Timeout in the Datareader!

I Use the ADO.Net OBC Connection with the Connection String:

Dsn=xxx;uid=xxx;connection timeout=0;command timeout=0;query timeout=0

Is there any Option to set the Query Timeout ?

Thanks !

Pseudo

Set your time out values to something other than 0, like 999.

Some ODBC drivers interpet 0 as "use default". Since you didn't say what ODBC connection you are using, I cannot tell if that is your problem or not.|||

I have had this problem with queries on very large datasets coming from an AS 400 where indexes weren't present (that I could find out about anyway) when I was using multiple where clause conditions that resulted in an extremely long running query. What I found was that using a simpler query that initially imported way more data than I actually needed to import was more efficient (from the simplistic viewpoint of how long my processing step took to execute) than did changing the timeout to a value large enough to accomodate the query.

Changing the timeout is a good idea but realize that with data sources that you have little to no control over or lack adequate access to info about, it can be more efficient to simply import a lot of data into a holding table in SQL Server that you subsequently prune in an Execute SQL task before you convert it, etc. At lleast once you get it into SQL Server, you can control it however necessary.

Hope this helps.

|||

Thanks for the answer !!

I have not seen that the Datareader in the SSIS Designer have a Command Timeout property ?
I set this property to 0 and now it works...

But exist a difference between the Datareader Command Timeout property and the ODBC Connection Command Timeout property ?

DataReader and bad dates

Hello,

I am using a DataReader to read an ODBC Data Source.

Some of my dates are not valid. I would like to have the DataReader just treat the bad dates as Null. However I cannot seem to find a setting that will get me past the DataReader.

In SQL 2000 DTS I was able to use a script to skip the bad dates.

Please help because I don't want to have to run SQL 2000 DTS on SQL 2005.

Thanks,

Michael

You can send all rows that fail down the error output by setting ErrorRowDisposition=RD_RedirectRow for the output column in question. or, you can set ErrorRowDisposition=RD_IgnoreFailure and it will ignore those errors.

-Jamie

|||

Hi Michael,

If you right click the datareader src and choose Show Advanced Editor, then go to the Input and Output Properties tab, and expand the Output Columns collection, you can set the behaviour you want for error handling on a per-column basis. Click the column you want to ignore errors on, and change the "ErrorRowDisposition" property to "RD_IgnoreFailure". I think this will give you the result you are looking for -- NULL for columns that were invalid.

Let me know if this does not solve your issue.

Thanks
Mark

Dataflow Tab:There is no ODBC Source option in the Toolbox

I need to extract data from tables in a database that I can only access via ODBC.

I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.

However I’m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.

This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process

How do I extract data from tables in a database via ODBC?

Thanks in advance

Dave

There is too, though it's not marked as such. Use the Data Reader Source.|||

You can use the script component as source.

|||

Thanks for that the data reader souce can actually use a ODBC source. I have it working fine.

I do think there is a peformance overhead and its slower than the ODBC connector in DTS.

|||

well that is interesting How can it connect to a foxpro file .dbf and matching .fpt file the .fpt files are used for memo text fieldsthe foxprpro ODBC driver does this for you behind the scenes

|||

Vijay Thirugnanam wrote:

You can use the script component as source.

True, though it shouldn't be faster than using the prepackaged source connectors.