January 31, 2023 at 3:18 pm
Hello,
I'm pretty new to SSIS, but I have an Excel source that I need to bring into an SQL Server table daily. There is a column I need to preserve which is an ID field with both numeric and alphanumeric characters. If I bring them in as-is, the alphanumeric values turn to NULL and the record, for my purposes is essentially lost. (I have no control over the Excel file by the way) I did set up an SSIS package that includes a sort, but while it sorts the numeric records properly, it leaves the alphanumeric records at the bottom and when it comes over into my table, they're NULL again. Anyone face this before and find a solution?
thank you!
January 31, 2023 at 3:54 pm
Yeah, Excel files can be a lot of fun/pain, because an Excel sheet is essentially a bunch of Variant type cells. That makes mapping them into "normal" sql data types difficult and error-prone.
Avoid Excel as input source whenever you can.
January 31, 2023 at 5:10 pm
The standard trick is to import the header row as if it were data and then discard it in the data flow (use a Conditional Split to do this).
Also add IMEX =1 to your Excel connection string (treat mixed datatypes as text).
But the much better solution (as already mentioned) is to import from a text file.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 1, 2023 at 5:30 pm
thanks a ton guys!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply