DataReader With ODBC As Source Using A User Variable In The Sqlcommand
Nov 15, 2007
Does anyone know how I can use a user variable in a sqlcommand in a Datareader source with an ODBC connection as the source?
I am storing a date value in a user variable(Date) I fill with a SQL Task and then want to use the value in the sqlcommand I use in the Datareader Source. It won't let me use the @variablename in the sql command.
Can anyone help with some advice on how I can make this work?
Appreciate any help I can get.
Thank you
View 5 Replies
ADVERTISEMENT
Jan 17, 2007
I'm attempting to configure a DataReader's SqlCommand text to use a package-level variable and I'm having trouble. I've read about using the "sql command from variable" option but although I see that during the configuration of Ole DB source, I don't see the same option for DataReader source. Viewing the properties of the DataReader and selecting the SqlCommand property does have button to configure the value but only displays a string value editor window. (I was hoping for the expression editor.)
The configured connection to the DataReader uses the odbc Data Provider (under .Net providers). I'm connecting to mySql, if that matters.
What I'm looking to do is to use a variable in the where clause of the DataReader's SqlCommand. Any hints how to accomplish this or other ideas would be greatly appreciated.
Thanks
View 4 Replies
View Related
Nov 20, 2007
Hello,
I'm trying to set the SQLCommand of a DataReader with a string variable (at the DataFlow Task level), and I'm having some problems. The source is a DB2 table. The destination is an MS Excel file. Here is what I have:
1. A variable named SQLStatementDataFlow of type String. EvaluateAsExpression is set to True, and the SQL statement in the expression evaluates without a problem.
2. In the Expression property of the DataFlow Task, I have the [SqlCommand] property configured with the variable.
First, in the component properties of the DataReader, what kind of SQLCommand do I set up here if the DataFlow SQLCommand is configured to use a variable?
Thank you for your help!
cdun2
View 4 Replies
View Related
Feb 24, 2006
I am trying to import data from an ODBC source using the Datareader but have come accross a problem, can anyone help?
Whenever I try to do a 'SELECT * FROM sometable' I get the following error:
Error at Data Flow Task [DTS.Pipeline]: The output column "notes" (521) has a length that is not valid. The length must be betewwn 0 and 4000
Most of the tables we require have this field.
Is there any way to by pass this error and import the field as DT_WSTR type and convert to DT_NTEXT?
Our original DTS package (2000) worked well but we would like to move forward.
Thanks in advance.
John
View 1 Replies
View Related
Mar 16, 2006
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 SettingsmichaelshLocal SettingsTemp". 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
View 11 Replies
View Related
Apr 7, 2006
Hi all,
I am trying to have a DataReader Source that can run
a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand
that connects to an ODBC connection. If it is possible by any way,
please let me know. Thanks in advance.
Daren
View 14 Replies
View Related
Apr 25, 2008
Hello,
I am having an issue when attempting to retrieve data from SPSS via a ADO.NETDBC Connection
using the DataReader source. What seems to be occurring is that the DataReader is reading a column that
has a length of 255 and what it is doing is taking the first 200 characters and starts repeating the
characters starting at character 201, in this way erasing any data held in positions 201 to 255.
Another way of saying this:
This statement returns data in the results but I have noticed the data is incorrect. It seems to only
be selecting the initial 200 characters of the 255 in the field. Then it starts to repeat the
first 200 characters again to complete the full selection of the 255 characters
Here is an example:
Instead of:
€œXXXXX changed my life before it got worse. It was very informative. They gave me the information. They left it up to me to ponder over it and make the decision on what I wanted to do. It informed me on what drugs do to your body and mind. I was stress€?
I end up getting:
€œXXXXX changed my life before it got worse. It was very informative. They gave me the information. They left it up to me to ponder over it and make the decision on what I wanted to do. It informed XXXXX changed my life before it got worse. It was very€?
Source Column Datatype that SSIS can see is of Unicode string [DT_WSTR] type. Also it correctly identifies
the length is 255 in both the External Column, and Output Column section.
View 2 Replies
View Related
Aug 7, 2007
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).
View 7 Replies
View Related
Aug 17, 2007
Dear All,
I have created a DTS Package in Integration Services 2005.
Within the DTS Package declared a variable named xxx and passed a value 1234.
In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined
Property = [DataReader Source].[sqlCommand]
Expression = Variable name.
Now in the DataFlow Task Canvas dropped DataReaderSource.
How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.
regards
Sufian
View 4 Replies
View Related
Jul 25, 2007
my package contains
Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;
Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;
And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.
Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.
Anyone could help? Thanks!
View 3 Replies
View Related
Aug 27, 2007
Hello,
Newbee here
64 bit SQL 2005 running on Windows Server 2003 X64
I have an exececute SQL task (in the control flow obviously)
SELECT MAX(last_update) AS OrdersLastUpdateFROM orders
This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD.
The next step of the Control flow is a dataflow task
Details
DataFlow Source = DataReader Source (MySQL .NET connector)
DataFlow Dest = local SQL Server OLE DB.
In the DataFlow Source the DataReader SQLCommand property is
Select * from orders where last_update >= @User:tmOrdersLastUpdate
I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal.
How do I pass a user-defined global variable to the WHERE clause in a DataRader object?
Thanks
View 5 Replies
View Related
Sep 19, 2006
Hi all,
I have been playing with integration services for a few days and at the moment, its up there with my list of software that I find ......painful.
What I am trying to do is read different tables from my one SQL database, then populate my Access database with its data.
I have put a foreach loop which goes through a collection SQL statements that I have entered into it. It first assigns it to a string variable called tablenameVar which contain statements such as "Select * from Terminals". Then the datareader is supposed to execute it based on the connectstring which never changes, and the SQLCommand value which I passed using the variable @[User::tableName]. However when I try to run it, I'm getting this error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [DTS.Pipeline]: "component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Does anyone have an idea of what I should do? or maybe a better way to do it? I appreciate you guys for taking a time to look at this.
Thanks,
Joseph
View 1 Replies
View Related
Aug 30, 2006
HI!
as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?
how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?
Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?
THANKS, HANNES
View 7 Replies
View Related
Jan 12, 2007
Hello,
Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?
The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.
The SQL command in the custom properties doesn't cross reference back to the variable.
If anyone can help or needs further detail to help please post a resonse.
Thanks,
Marcus Simpson
View 4 Replies
View Related
May 10, 2007
Hi! I need to know what can I do to use a variable in the WHERE condition of the sqlcommand as I show you: current_user = User.Identity.Name
Dim cmd As New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = current_user", cn) Obviously it doesn't work and I need your help. Thanks.
View 7 Replies
View Related
May 18, 2007
Can't seem to pass a variable to the sql statement. I'd appreciate any help. I'm trying to pass pColName to CommandText = "ALTER TABLE tb_roomInfo ADD @rColName varchar(50);";Doesn't seem to work though. CODE: [WebMethod] public string addCol(string pColName) { SqlConnection cnn = new SqlConnection(connString); try { cnn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cnn; cmd.CommandText = "ALTER TABLE tb_roomInfo ADD @rColName varchar(50);"; SqlParameter rColName = new SqlParameter("@rColName", pColName); cmd.Parameters.Add(rColName); int i = cmd.ExecuteNonQuery(); cnn.Close(); return "Insert Successful"; } catch { return "Insert Unsuccessful"; } }
View 3 Replies
View Related
Oct 25, 2006
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
View 5 Replies
View Related
Apr 11, 1999
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
Any assistance would be greatly appreciated...
Regards,
Wayne
View 2 Replies
View Related
May 18, 2006
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?
View 1 Replies
View Related
Nov 15, 2007
Hello,
I have a DataReader souce configured to an ADO.NET connection manager that uses a .NET ODBC Data Provider. The DSN configured in the connection manager uses the IBM DB2 ODBC Driver.
I have a package variable, the value of which I want to use in the WHERE clause of the query contained in the Data Reader source. The query is as follows:
SELECT
T1."IRK_ACCT_CD",
T1."IRK_COMPANY_NBR",
T1."IRK_ACCT_NAME",
T1."IRK_ADDRESS_1",
T1."IRK_ADDRESS_2",
T1."IRK_ADDRESS_3",
T1."IRK_STATE_CD",
T1."IRK_POSTAL_CD",
T1."IRK_AR_TYPE",
T2."RK502_ITEM_RE_NO",
T2."RK502_TRANS_CD",
T2."RK502_TRANS_TYPE",
T2."RK502_ITEM_AMT",
T2."RK502_ITEM_DT",
T2."RK502_PURCH_ORD_NO",
T2."RK502_GL_EFF"
FROM "CBDBOW"."IRK_RECORD" T1, "CBDBOW"."RK502_OPEN_ITEMS" T2
WHERE
T1."IRK_COMPANY_NBR" = T2."RK502_COMP_NO"
AND
T1."IRK_ACCT_NUMBER" = T2."RK502_ACCT_NO"
AND
T2."RK502_ITEM_DT" < ?
I'm not very sure as to how to map the package variable this way. I think for ODBC parameters, I need to represent the parameter with a question mark.
What else do I need to do to set this up?
Thank you for your help!
cdun2
View 10 Replies
View Related
Mar 26, 2007
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?
View 11 Replies
View Related
May 21, 2008
I have a problem with DataReaderSource.
I'm trying to get data from Notes table. I created a Connection manager
and the connection was successful. The SQLCommand in "Component properties"
tab is a simple "select * from <table_name>". When I switch to the
"Column mappings" tab, only the first column from the table is displayed.
Pressing the "Reftesh" button resulst in the following error:
Error at Data Flow Task {DTS.Pipeline]: The "output column <column_name> has a length that is not valid.
The length must be between 0 and 4000.
When I go to the "Input and Output Properties" tab, the DataType for the output column is not populated
and the error message "Error in Data Flow Task [DTS.Pipeline]: The output column <column_name> had an
invalid datatype (0) set."
The DataType property is not populated at all. Changing the data type to DT_STR results in error:
"Property value is not valid". Details: Error at Data Flow Task [DataReader Source]:
The data type of output columns on the component "DataReader Source" cannot be changed".
I read on a previous post to explicitly convert field , and tried to explicitly covnert the dataype on the field
in my query (ex. select convert(varchar(50) from fieldname)
It then gives foll err:
ERROR [42000] [Lotus][ODBC Lotus Notes]Incorect syntax near ','
[Lotus][ODBC Lotus Notes]Name, constant or expression expected.
Any idea on how to resolve this?
View 3 Replies
View Related
Feb 12, 2008
Hi,
I am running an MDX query in SSIS but I don't know what is the best way of doing this, performance wise.
I know I can run the MDX query through an openquery in the OLEDB, and also run it through a Datareader, no openquery needed.
I know the datareader is slower in a normal basis due to .Net, but in this case the OLEDB is running an open query to a linked server which won't be fast like running a regular SQL.
If anyone knows which of this two run faster in this scenario I'll appretiate if you let me know.
View 1 Replies
View Related
Mar 12, 2007
Greetings my SSIS friends,
Apologies for asking a similar question again but I am still non the wiser with this problem!
Let me explain to you my situation and the method I've adopted to try and solve it.
I have some source data residing in a SQL Server 6.5 database. The source data consists of a single table. For this example I will assume that my table contains only 2 columns, an ID column called result_ID and a Result_Name.
The idea is to retrieve new data each time the package is run. We will know this because the result_IDs in the source table will be greater than the maximum result_ID in my destination table . The way the package should work is like this :
1) Retrieve maximum result_ID from destination table
2) retrieve data from source table where result_ID > maximum result_ID from destination table.
My package consists of a
1) SQL Query Task which retrieves the maximum result_ID and places it in a user variable (type Int32).
2) A Data flow task with a Datareader source adapter which uses an expression to retrieve the data. My expression looks like this : "select * from result where result_id > " + (dt_str, 10, 1252) @[User::max_result_id]
When I run my package the first time all the rows are retrieved (as my destination table is empty to begin with). BUT when I run it the second time the same thing happens again!! All rows are retrieved.
I placed a breakpoint at the point where the variable gets populated with the maximum result_ID and true enough, the variable gets populated with the correct result_ID BUT then that variable gets reset to 0 in my expression!
This problem is driving me crazy! Has anybody out there experienced this kind of problem before?! What are the ways to solve it?!
Thanks for your help in advance.
View 5 Replies
View Related
Oct 25, 2007
I get an error at the end of a 47 million row job when I use the datareader source. It goes through all the records and then the package fails. The error (DataReader Source [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. ) occurs at the datareader source. I suspect it's because my record set returns a null value at some point. Any ideas?
View 10 Replies
View Related
Mar 2, 2006
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?
View 6 Replies
View Related
Aug 25, 2007
Dear Friends,
I have to import dBASE files to SQL Server. For this I have created one ODBC connection manager for those dBASE files. This I have to set for DataReader Source. But I am unable to configure the DataReader Source.
So, Please tell me how to configure DataReader Source for ODBC connection Manager.
Eagerly waiting for your valuable reply............
Santosh
INDIA
View 2 Replies
View Related
Feb 29, 2008
I have a package that runs every hour. It runs fine most of the time but once in a while
I get this not very helpful error message "the component 'xyz' is unable to process data" on DataReader Source component.
when i try to run it again on the same data it works fine. So i dont think it has to do with data.
Is there a way i can get more information on this error?
View 9 Replies
View Related
Mar 21, 2008
I am trying to migrate data from MySQL 5.0 to SQL Server 2005. The MySQL database has a table which stores the profile description in different languages like (Arabic, Spanish etc). I use MySQL ODBC 5.1 driver for creating the ODBC connection and creating a ADO connection in SSIS using that ODBC. The datareader source connection is set to this ADO connection.
When I view the properties of columns in Datareader source it shows as Unicode, which is good. But when I migrtae to SQL Server 2005 I get junk data instead of the data in Arabic, Spanish etc.
Am I missing something or is there any other alternative to do the data transfer correctly?
View 10 Replies
View Related
Dec 4, 2007
I'm attempting to iterate package components of a package created in the SSIS designer, but have run into a problem identifying the DataReader Source managed component.
For instance, if iterating the dataflow task components like so:
Application app = new Application();
MainPipe pipe = taskHost.InnerObject as MainPipe;
foreach (IDTSComponentMetaData90 component in pipe.ComponentMetaDataCollection)
{
if (component.ComponentClassID == app.PiplineComponentInfos["DataReader Source"].ID)
{
// THIS IS NEVER REACHED
}
}
The ComponentClassID of the component is a GUID, but the PipelineComponentInfo.ID AND PipelineComponentInfo.CreationName are both the fully qualified assembly name of the data reader source adapter, which is:
"Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
From reading another post i attempted to get the ProgID of the component from the class ID (ole32 ProgIDfromCLSID) but that only yields a ProgID of DTS.ManagedComponentWrapper.1
How can I properly identify this type of dataflow component?
I am running SQL Server SP2 (Developer Edition) on XP Pro SP2.
View 6 Replies
View Related
Nov 3, 2006
Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.
Under the Component properties tab, the SQL Command looks something like this.
Select ID, ADDRESS, REVISED from ADDRESS
The data type for the source columns are Integer, Varchar(30) and DATE VMS.
Now when I look at the Input and Output properties window,
The External columns has the following data types.
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
The Output columns has the following data types
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
When I tried to change the length of the ADDRESS on the output column, I get the following error.
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.
Any thoughts.
Thanks,
SK
View 8 Replies
View Related
Jan 13, 2006
Synopsis:
Attempting to create a data flow task to copy data from AS/400 (DB2) to SQL2005, using an existing System DSN ODBC connection defined on the SQL2005 host.
Problem:
When adding the DataReader Source component to the package, I cannot assign the Connection Manager. Designer issues the error message:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
Editing the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
The datareadersrc component editor displays the warning message: "Not all connection managers have been set. Set all connection managers.". Clicking the Refresh button causes the error message to be displayed "The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from assigning my Connection Manager object the DataReaderSrc.
The package already contains one Connect Manager object:
Provider: .Net Providers/Odbc Data Provider
System DSN
Test Connection operation succeeds
Any help would be appreciated.
Fraser.
View 3 Replies
View Related
May 11, 2007
I want to import data from a remote MySql Database
So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:
Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)
the sql string is very simple, this should not be the problem:
SELECT Objektnr FROM m02a_tblObjektstamm
Thank you very much for your support
Martin
View 8 Replies
View Related
