February 17, 2012 at 6:40 pm
I am using SSIS 2008 and having a problem with pulling character data values from an Informix source. The etl runs fine, it pulls records from the source database and poulates them in the target, but all the fields that are char/varchar data type comes out blank in the target (no prob loading numerical values). I have tried loading data in a SQL Server table and also in a text file, the result is the same.
Here is info on diff software ver etc.:
The etl is running on a Windows Server 2008 R2 standard (64 bit OS). SSIS ver is 2008, 64 bit run time for the project is disabled (etl is running in 32 bit mode). Informix ODBC is ver 3.70.TC3 it's a 32 bit driver, and DSN has been setup under 32 bit ODBC (C:\Windows\SysWOW64\odbcad32.exe).
I am using ADO NET component to connect to the ODBC dsn. After writing my query in the ADO Net, when clicked on the "preview" button I can see all the correct data. Also, I am able to run a query against this ODBC dsn using the "Server Explorer" tool in BIDS -- query result shows all the correct values, I can copy the result and paste it in a notepad. But when the etl is run, values for all the fields that are char/varchar comes out blank in the target.
Please let me know if any additional info is required.
Any help/insight into it will be greatly appreciated.
Thanks,
Manish
February 20, 2012 at 12:21 am
Add a dataviewer in your dataflow. (right click on an error, choose Dataviewers).
If you run the dataflow, do you see the correct data in the dataviewer?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2012 at 7:31 am
I have already tried a data viewer on the link between ADO NET and the next transformation, all the char/varchar columns were blank in the data viewer.
February 23, 2012 at 10:00 am
I am having same problem. Any help will be greatly appreciated.
March 5, 2012 at 6:13 am
Hi All,
I have the same problem.
I'm not sure if this is not related with the latest Windows Server/SQL Server updates. I remember that it worked a month ago.
Some one know, how to handle this issue?
Thanks in advance.
BR,
Lukasz Chalecki
March 6, 2012 at 5:10 am
Just throwing out some suggestions:
1. In the target are the values NULL or do they contain spaces?
2. What if you ETL into a text file, not a database table - any data?
3. Have you tried using nvarchar as the target? What is the name of the datatype in the source and the destination according to SSIS?
March 6, 2012 at 5:17 am
In the target it shows as empty string.
I have tried to output it to nvarchar data type and text files without any success. It shows data inserted with no error however there are empty string for char data type but float data type is ok.
March 6, 2012 at 5:22 am
So you have tried transferring directly from Informix to a text file vai SSIS, and, for example if you've exported to CSV, you open the text file and just find a bunch of commas?
What happens if you use the query tool in Excel to import the data into Excel?
What about importing into MS Access?
...just trying lots of options to try and isolate one that works, then you can start from there.
I guess the question is: what does the SSIS previewer do that everything else doesn't?
Sometime the only solution is to use a workaround. Otherwise you can waste a month trying to sort something out when you could implement a sub-optimal but reliable workaround in a day.
March 6, 2012 at 5:30 am
I can get the data in crystal report without any problem. In SSIS preview it shows data correctly.
The workaround I am using now is exporting data into text file using qlikview and then using SSIS to populate database. It is a long way around at least it works.
March 6, 2012 at 1:28 pm
I can also confirm that it works in Access tool. I can also preview the data.
Problem is with extracting those data from ADO.NET Source.
This is very strange issue, because I can extract numeric data, but all text values will be blank(empty).
March 6, 2012 at 8:03 pm
As you can guess I do not have a simple solution for you but I can suggest tests that might isolate the problem. If I'm wasting your time let me know!
Next idea:
1. Use a query to extract the data in SSIS. Add a where clause filtering blank strings. Do you still get data? This will rule out any chance of the data truly being blank on the Informix side.
2. Use cast function in the Informix SQL to cast it to something like CHAR(5) if that exists. What do you get?
March 6, 2012 at 8:15 pm
Also what name is given to the source and destination data types in Informix and SQL Server in SSIS?
For example: WSTR, STR... something like that?
Do you know for sure whether the source and destination columns are unicode or not? i.e. in SQL Server are they VARCCHAR or NVARCHAR?
What is the name of the data type in Informix? i.e. VARCHAR, NVARCHAR?
March 8, 2012 at 6:21 am
Hi Nick,
I have tried your two options and unfotunately extracts are still blank in those two cases.
In the Source and Destination it is WSTR. It should be correct because text datatype is VARCHAR.
In my case, I'm trying to extract data from Firebird db using ODBC drivers from FireBird on Windows Server 2008 R2 64 SP1.
March 13, 2012 at 3:18 pm
This is what fixed the problem for me:
On the ADO.NET source editor, change "error" property of string columns to "Redirect row". Hit Ok to close the editor, save the package and run it. You should see all the columns correctly populated in your target table.
~Cheers
Manish
March 14, 2012 at 4:06 am
This has fixed the problem. Thanks.
It shows me warning
[ADO NET Source [1]] Warning: Rows sent to the error output(s) will be lost. Add new data flow transformations or destinations to receive error rows, or reconfigure the component to stop redirecting rows to the error output(s).
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply