DataReader With ODBC As Source Using A User Variable In The Sqlcommand
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 Complete Forum Thread with Replies
Related Forum Messages:
Using Variable In DataReader's SqlCommand Text
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 Replies !
Problem With Variable In DataReader SQLCommand
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 Replies !
Run Variable In DataReader Source?
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 Replies !
SSIS Datareader ODBC Source Error
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 Replies !
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 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 Replies !
Pass Variable Value To DataReader Source
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 Replies !
SPSS ODBC Strange Column Value Repeating With DataReader Source
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 Replies !
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).
View Replies !
Weird!! &&"&&<&&" Sign Doesn't Work Within DataReader Source Connecting To Mysql Using ADO.NET ODBC Option
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 Replies !
Passing Parameter From SQL Task To DataReader SQLCommand
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 Replies !
Setting Expressions In Datareader's SQLCommand Property Dynamically
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 Replies !
Datareader Destination As Source For Other Datareader Source ?
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 Replies !
Script Task User Defined Variable And Data Reader Source SQL Statement
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 Replies !
How To Use A Variable In A SQLCommand?
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 Replies !
Passing Variable To SqlCommand
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 Replies !
Can The Result From An EXEC(@sqlcommand) Be Returned To A Variable
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 Replies !
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?
View Replies !
Expressions And Datareader Source
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 Replies !
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?
View Replies !
Error On DataReader Source
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 Replies !
MDX OLEDB Source Vs Datareader
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 Replies !
Using Parameters With DataReader Source
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 Replies !
Datareader Source Error
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 Replies !
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
View Replies !
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?
View Replies !
SSIS Error On DataReader Source
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 Replies !
How To Configure DataReader Source For DataFlow
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 Replies !
Error When Changing The Length On DataReader Source
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 Replies !
Datatype Migration Problem Using DataReader Source
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 Replies !
Problem Determining If IDTSComponentMetaData90 Is A DataReader Source
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 Replies !
Advanced Editor For DataReader Source / SQLServer 2005
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 Replies !
Can't Assign Connection Manager Object To DataReader Source
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager. Problem: When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab: Name=IDbConnection Connection Manager=blank Description=Managed connection manager I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab: "Not all connection managers have been set. Set all connection managers." When I click refresh the warning message changes to: "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 removing the IDbConnection or assigning my Connection Manager object to the DataReader Source. Thanks, -- Mike
View Replies !
Error Assigning Connection Mgr To Datareader Source Component
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 Replies !
PrimeOutput() And NullReferenceException Problems With DataReader Source In SSIS
Hi I have an integration services script that was working ok until a recent database upgrade. When I run the script in gui debug mode it behaves as follows. The data flow reads in data from an oracle server table VALNREQ & then uses it to populate a sqlserver table (with a little manipulation of the fields in between). For some reason although it populates the destination table with the correct number of rows (& the data looks ok) it errors out with the messages listed below. The DataReader Source dataflow source box turns red & all the other boxes turn green including the destination one. I have run out of ideas ... any suggestions? Have I inadvertently changed a property without noticing? I can't see anything obvious & the input data itself looks clean enough. Any pointers in the right direction would be great, thanks. I've been thrown in at the deep end with this s/w so I imagine there are a number of large gaps in my knowledge so apologies if there is a simple solution to this. B [DataReader Source VALNREQ [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket) [DTS.Pipeline] Error: The PrimeOutput method on component "DataReader Source VALNREQ" (1) returned error code 0x80004003. 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.
View Replies !
The Best Overloaded Method Match For 'System.Data.SqlClient.SqlCommand.SqlCommand Error
Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: { Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString); Line 26: mySqlDataAdapter.Fill(myDataSet); Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System; using System.Data; using System.Data.SqlClient ; using System.Configuration; using System.Collections; using System.Text; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Xml; public partial class createGeoRSSFile : System.Web.UI.Page { protected void Page_Load(object sender, DataSet myDataSet, EventArgs e) { string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password"; string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes"; using (SqlConnection mySqlConnection = new SqlConnection(connString)) { SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString); mySqlDataAdapter.Fill(myDataSet); return myDataSet; } // Create a new XmlTextWriter instance XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode); // Start writing! writer.WriteStartDocument(); writer.WriteStartElement("item"); // Creating the <town> element writer.WriteStartElement("town"); writer.WriteElementString("PostCode",myDataSet .Tables[1].Columns("PostCode")); writer.WriteElementString("geo:lat",myDataSet.Tables[1].Columns("Latitude")); writer.WriteElementString("geo:lon", myDataSet.Tables[1].Columns("Longitude")); writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndDocument(); writer.Flush(); writer.Close(); } }What seems to be causing this error?Thanks.
View Replies !
Pass A Variable To A DataReader In A DataFlow Task
How can I pass a variable to a DataReader in a DataFlow task? My SqlCommand for the DataReader is: SELECT CustName, CustCode FROM Customers WHERE CustCode = '?' The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.
View Replies !
Passing A Table Programmatically To Datareader Source Component In Ssis
Hi There, I am loading/executing packages from c# and I need to populate a temp table from user input and pass this table as a variable to the datareader source components sql command. I am using expression to build this query, but I am getting design time error when I have this command.. "select id, (SysDate + 28) as ExpiresDate from Table1 where id in (Select Id from" +@[User::Table2]+")".. I have declared Table2 as a variable of type Object and I am creating Table2 in C# and I am assigning that Table to the user Table. But in the design mode, I am getting an error...expression cannot be evaluated. Can anybody please tell me when I cannot do this? Thanks,
View Replies !
DataReader Source Freezes In Validation Of MySQL Stored Procedure
I have a package that hangs in the designer after I change the sql statement in a DataReader Source from a 'select' to a 'call stored procedure'. The stored procedure takes 2 date parameters. I use an expression to build the 'call stored proc' statement and the 2 date strings. The data reader source uses an ADO.Net connection manager. The ADO.Net connection manager uses the provider for MySQL (Connector/.Net 5.1) which I installed from MySQL.com (http://dev.mysql.com/downloads/connector/net/5.1.html). Before creating the stored procedure I had been using an expression to build a 'select' statement with two date variables as follows: select ... where ads.last_seen >= "" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "" and ads.first_seen <= "" + (DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] )+ "" group by sm.service_provider_id,lm.location_id,lm.web_sublocation_id;" The sql for the data reader source is set via the sql command property of the data flow component. After testing the sql, I created a stored proc from this sql and then changed the expression (using the sql command property of the the data flow component) to build the 'call stored proc' statement, like this. "call usp_SEL_Rollup ("" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "","" +(DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] ) +"");" then when I tried to switch to the data flow tab, the editor froze, with the status bar saying "validating datareader source". The data flow tab says "Loading...". I don't know how to troubleshoot this. Each time I have tried I have had to kill the application. Any ideas/suggestions? Thanks, Al
View Replies !
DataReader Source Error - Cannot Change The Datatype, Precision Or Scale In The Output Columns
I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors). I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message: Property Value is not valid. The details are: Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed. Error at DataReader Source: 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) Any help is greatly appreciated. Dave
View Replies !
MS OLE DB For ODBC With Oracle ODBC Source Headaches
Hi all,I am having trouble getting linked Oracle 9 server in MS SQL Server2005 Express to work properly. My machine is running Windows XP.The Microsoft and Oracle OLE DB Providers have problems dealing withOracle's Numeric Data Type, so I decided to use Microsoft's OLE DB forODBC Provider and an Oracle ODBC source. When using the Microsoft ODBCfor Oracle Driver in my ODBC source I have inconsistent behavior.Sometimes my queries are processed properly, then other times I get thefollowing errorOLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC driver for Oracle][Oracle]".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL"for linked server "ODBCBEAST".I have no idea why sometimes I can connect to the linked server with noproblems andwhy other times it performs like this. I'm not changing anything aboutthe system I can think of. When I use an Oracle client (PL/SQL) I haveabsolutely no problems connecting. TNSPING returns that the connectionis good.This is unacceptable so I decided to try my luck with the Oracle 10gODBC driver. However when I use this and perform an openquery selectagainst the linked server I get back only 11 rows, when I know that thedatabase has over 100 rows (in fact when using the Microsoft ODBCdriver and it works that's what I get). I figured maybe the buffersetting needed to be raised in the ODBC configuration so I took it from64000 to 600000 (a magnitude of 10) but I still get back only 11 rows.I'm at my wit's end.Any suggestions on resolving one or the other problem would be muchappreciated.Thanks much
View Replies !
SSIS Error Reading XML Loaded Into Variable With XML Source Using XML File From Variable.
Hi I am getting the following error when trying to extract data using XML source " Error: 0xC02090D0 at Data Flow Task - Load XML data to database, XML Source - Load XML data from variable [15893]: The component "XML Source - Load XML data from variable" (15893) was unable to read the XML data." What I have done is read XML data from file and stripped out DTD contents using XSLT transformation in an XML task. The XML file is loaded into a string variable called XMLProduct. Next I have a XML task to validate the variable contents against the XSD, which works. Then I am trying to load the data using XML Source within a Data Flow task, which is when the error occurs. If before I enter the Data Flow and use a script task to read the content of XMLProduct variable and save that to a file then point XML Source task to the file it works ok. I need to iterate through a whole bunch of XML files so reading it into a variable would be the preferred option if it worked of course. Any help would be hugely appreciated. Edit --- I have saved the output from the XSLT transformation XML Task to file and not a variable then read that in using the XML source task and it worked. It would be nice to get the variable method working though as it would save having to create another file. It is a work around at the moment though.
View Replies !
How To Solve :Error: 0xC0047062 At CTPKPF, DataReader Source [1]: System.NullReferenceException: Object Reference Not Set To An
hi i need help to solve following error in ssis package when i aun :: Error: 0xC0047062 at CTPKPF, DataReader Source [1]: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket) Error: 0xC0047038 at CTPKPF, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0x80004003. 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. There may be error messages posted before this with more information about the failure. Error: 0xC0047021 at CTPKPF, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. Information: 0x40043008 at CTPKPF, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at CTPKPF, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at CTPKPF, DTS.Pipeline: "component "OLE DB Destination" (1993)" wrote 0 rows. Task failed: CTPKPF
View Replies !
The Product Level Is Insufficient For Component &&"datareader Source&&"
I have created an SSIS On my PC, That extracts data out of Lotusnotes. When I run it manaully from Visual studion-execute package, it works just fine. However, when I go tO Management studio, chose the servername, then go to Stored Package-MSDB- And chose Packagename, and hit Run PKG from there, it gives me the error below. Server has 2005 AND IS WINDOWS 2003 OS. The product level is insufficient for component "datareader source" Can you tell me why the same pkg will work fine when executed from Visual studio-But when I go the run it from Storedpackages-MSDB-it fails.
View Replies !
DataReader Source - ERROR [42000] XML Parse Error At 162:1338: Not Well-formed (invalid Token)
Hello, I get the following error when I run my package interactively. From the logs written out by the driver, it appears that all is working well as far as connecting to the data source and pulling data. It seems as if this error occurs when the DataReader source tries to process the received data. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.Data.Odbc.OdbcException: ERROR [42000] XML parse error at 162:1338: not well-formed (invalid token) at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper) Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed the pre-execute phase and returned error code 0x80131937. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (691)" wrote 0 rows. Task failed: Data Flow Task SSIS package "MyPackage.dtsx" finished: Success. I am not sure where to look next. Any help is much appreciated. Dave
View Replies !
XML TASK XSL Variable: Exception 'Property &&"New Source&&" Has No Source Xml Text'
Hi, I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task). After that, in an xml task, I set the input properties like that: Operation type: xslt SourceType: File connection Source: ras.xml All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that: Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90 Me.VariableDispenser.LockOneForWrite("RAS", v) v("RAS").Value = doc.OuterXml v.Unlock() and in the xml task, I set configuration like that: Operation type: xslt SourceType: Variable Source: user::RAS But when I execute, I got this following exception: Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.". Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string. Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed. <?xml version="1.0" encoding="UTF-8"?> <root> €¦ </root> (When I store it in a file, I can see it in IE) Have you got an idea ?
View Replies !
ODBC Trusted_Connection Call To SQLGetInfo Returns DBO As User Name, Not NT User
Hello, all: I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo. So: Login Type / user id /returned value from SQLGetInfo Windows Authentication / Windows login / dbo SQL Authentication / Windows login (UID) / UID The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server.... My questions: Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?
View Replies !
How Do I Add An ODBC Connection Data Source As A Data Flow Source
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds. Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster? Thanks in advance ADG
View Replies !
|
TRACKER
