April 10, 2008 at 7:38 am
I have a task that uses an Excel connection manager to read in an Excel file. In the file there are 6 date columns. The date columns can have either a date or the letters NA. If I look at the External Column properties from the advanced tab, 4 of the date columns have a datatype of DT_DATE. The other 2 date columns have a datatype of DT_WSTR. The 4 columns with DT_DATE, my data looks okay as when it is read by the connection manager. The issue is that the 2 columns that are DT_WSTR, all dates are turned into NULLS, while the NA's are just fine. I have tried to change the datatypes in the advanced tab to DT_DATE, but it won't work.
Changing the data type on the advanced tab gives me the little Red X telling me there is a mismatch and asking do I want to fix it. I found that the original problem because the task populated the destination table with NULLS instead of the dates I expected to see.
I made changes to the connection managerbased on the following post in a different thread:
"Excel (more specifically, the JET driver) guesses at the data type of the columns you are retrieving. It does this by sampling a number of rows (8 by default) and picking a data type that matches the majority of the sampled rows. Unfortunately, it will also NULL any values that don't match the data type. There are some workarounds, such as increasing the number of sampled rows, or adding IMEX=1 to the extended properties of the connection string. You'll want to read this topic in Books Online, http://technet.microsoft.com/en-us/library/ms141683.aspx as it has more information on this topic and the workarounds."
I added the IMEX=1as indicated above, which presented a new problem. While the data in the fields is now there when I go to preview, at run time I get the following error:
[IMS Excel File [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "IMS" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I didn't see anything that would indicate the connection could not be made, since I was able to PREVIEW the file. It fails at run time.
The connection string I am using is:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\MoorestownDataDrillData\ahe prod_ims_200803.xls;Extended Properties="EXCEL 8.0;HDR=YES; IMEX=1;";
I don't want to resort to changing the input file to a CSV before loading. Can someone PLEASE help?
April 10, 2008 at 10:03 am
I've heard what you said about not to use CSV format, but I still want to argue, that probably is your best way to resolve this for good reasons.
Stripping data down to CSV plain text saves ETL programmer a lot of headaches. Just get the data in first. Data type, data format, size of the field etc. will be defined based on DW needs, and be taken care of later.
Down the road, the source excel file may be created from a different version of excel than the driver your SQl server has. Maintaining the SSIS is a lot harder.
April 11, 2008 at 6:05 am
I hear what your saying, however as a company we are trying to minimize user interaction with the files. I would prefer to do that as a last step if nothing else works.
April 11, 2008 at 6:42 am
Have your programmer write a little utility that will save the excel as csv.
June 12, 2008 at 1:13 am
You need to set registry value on [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text] Set ImportMixedTypes as Text.
I have implemented it in my website www.relationshiplist.biz and it worked for me.
--------------------------------------------------------------------------------
Mayank Parmar
Software Engineer
Clarion Technologies
SEI CMMI Level 3 Company
8th Floor, 803, GNFC info Tower,
SG Highway, Ahmedabad - 380 054,
Gujarat, India.
www.clariontechnologies.co.in
Email: mayank.parmar@clariontechnologies.co.in
MSN : mayank.parmar@clariontechnologies.co.in
Mobile: +91 9727748789
--------------------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply