February 27, 2007 at 9:58 am
I have an SSIS package that is pulling data from multiple sheets (19) in the same Excel file, using a UNION ALL to connect the data, and importing it into a SQL Server table newly created for the data. There are 3 columns, the first two integers/numeric. The third column I've created as a varchar(25) in the table and am attempting to pull in as a string from the spreadsheet.
The problem I'm having is Col3 can be NULL, a number, or a string that starts with "Group X". In some sheets, it's only a number or NULL. In other sheets, it's "Group X" or all three. When I debug the package, everything loads successfully to the SQL table, but anything with "Group X" in it pulls into the table as a NULL value. I've checked the Metadata on the paths from all the Excel sources to the UNION ALL and the sheets appropriately say DT_WSTR where there are the "Group X" values in Col3. But all the sheets that don't have "Group X" all say DT_R8.
I can't seem to change the paths with the DT_R8 to DT_WSTR for Col3. How do I do this? I also can't find a task appropriate to an Excel Source to parse the data in Col3 and pull out the "Group X" stuff to a separate (new) column. Everything I've tried complains because the Excel source isn't an OLE DB source.
Any help anyone can give me on figuring this one out would be greatly appreciated. Thanks!
February 27, 2007 at 12:50 pm
Okay, I found what I wanted finally in the Advanced Editor for the Excel Source objects (Advanced Editor, Input & Output Properties -> Excel Source Output -> External Columns). But I'm still having problems with my data nulling out.
Unfortunately, I can't get the Input Columns (the columns from the spreadsheet itself) changed. The Excel Source is reading Col3 as a Double-precision Float. I tried reformating the column in the Excel sheet from General to Text, saving the sheet. Closing the SSIS package and re-opening it so it would revalidate the sources. That didn't work. Every time I try and change the External Column source to Unicode String, I get that red circle/white X error that says "The component is not in a valid state. The validation errors are: Error at DataFlowTask [Mytask]: The output column "Col3" on the error output has properties that do not match the properties of its corresponding data source column". So, I tried to change the Error Output column's datatype and get "Property not valid".
When my column has a value like "Group X/12345" or "12345" or " ", it reads into the SQL table just fine with " " translating to NULL like it should. When it has just "Group X" with no numbers, it reads into the SQL Table as a NULL. This is driving me nuts. Any advice would be greatly appreciated.
Thanks,
March 1, 2007 at 12:55 am
Hi Brandie,
I assume that you are using an Excel Connection Manager. In the properties window of the 'Excel Connection Manager'. Find the ConnectionString property and edit by adding 'IMEX=1' to the end of 'Extended Properties' like in the following line:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";
Here's an explanation.
Hope this helps
Gogula
March 1, 2007 at 4:55 am
Unfortunately, it doesn't help. I am using SSIS in SQL 2005, not DTS in SQL 2000. And the only other explanation I found that's similar to this is talking about using an ADO Recordset, not an Excel Source or a Excel Connection object.
The other suggestion I found was to go into the spreadsheet, change the columns to text, and then manually re-enter all the values in the spreadsheet. There are, unfortunately too many values (thousands) in the spreadsheet for me to do such a thing and there's no way I'm going to be able to convince the end user to do it for me.
If anyone else has any other suggestions, I'd be glad to hear them.
March 15, 2007 at 12:32 pm
Perhaps the problem is not Excel but UNION. Can you re-arrange the SELECT to pull from a worksheet that has text in col3 first. This will define the destination col 3 datatype as such, and subsequent worksheets will be forced to that size and datatype. If not, try forcing in a blank row first
SELECT 0,0,'some long text'
UNION ALL SELECT
UNION ALL SELECT
etc.
March 15, 2007 at 12:40 pm
Bob,
Actually, I finally figured out what the problem is. It isn't SSIS (per say), but the Excel connection to SSIS. Even if I pull a sheet with data first, each connection in SSIS acts independantly of the others. I had sheets with data that were pulling fine and other sheets that wouldn't. It was all dependant on what was in the first 8 rows of each sheet and what the majority of that data was.
Apparently if you have 2 text values and 6 number values, the transformation will NULL out the text values and force the connection to be number / int / float, etc.
My work around was to pull all the records with text values in that column into a separate sheet of their own, create a connection to the UNION task and then everything worked like a charm. But I only had 20 sheets. I shudder to think what people will have to go through if they have more than that.
March 15, 2007 at 12:41 pm
BTW, thank you both for your replies. I do appreciate them.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply