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.
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?
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
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.
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.
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?
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.
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.
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?!
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?
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?
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............
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.
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?
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?
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:
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.
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.
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.
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:
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
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
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:
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.
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.
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.
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.
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).
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:
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.
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?
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)
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.
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
I was having one package which uses a source query in OLEDB Source Control and fetches the record and a couple of lookups and then an oledb command to insert/update the records in the table using as SP. I changed the source query(Infact the package) and removed in lookup and a different SP was called similar to the old one. But my problem is the package which was before taking only minutes to update 50,000 records is now taking more than 2 hrs. The problem is the number of records it is fetching from the source each time is very less.. its fetching hardly 500 records a time compared to nearly 2500 records before. Where am i going wrong? Any suggestion greatly appreciated.
