I am trying to import an excel spreadsheet into a Sql Server table. I am having 2 problems: 1. I imported a sql server table intoan excel spreadsheet. I have a sql column defined as Numeric(5,4). I can successfully import that into an excel columnwith no data conversion, but when I want to import that excel column back into a sql table defined the same way, Iget all zeroes after the decimal. and 2. I have a date in a sql server table defined as datetime. I'm improting that into an excel column defined as DT_Filetime. Some of these dates may be null. No problem getting them into the excel spreadsheet, but I can't get the null values to import back into the sql server date field, and I can't figure outhow to check in a conditional split to reroute these values elsewhere. Any ideas what I might be doing wrong?

I hate to reply to my own question, but I figured out what was wrong with #1. The excel column was mis-defined in the connection manager as having a scale of 0. That obviously takes precedence, and thus the 0's after the decimal point. Now if only I could figure out #2.......

What do you mean by "can't get the null values to import back" into SQL? How are they not "getting back"?

Thanks for your response. I'm getting the following error: [Afrs_SOFValues [1248]] Error: There was an error with input column "Copy of SOFValuesArchivedDate" (2799) on input "OLE DB Destination Input" (1261). The column status returned was: "Conversion failed because the data value overflowed the specified type.". I know that it is happening on the null date field because if all of the rows have dates, it runs just fine.

Your OLE DB Destination goes to a SQL Server database table? Hmm. That error does not sound like an error on the SQL end. What type is "Copy of SOFValuesArchivedDate"?

It is a database timestamp[DT_DBTIMESTAMP]

Which you've converted from DT_FILETIME? What does the data "look like" just before it reaches the OLE DB Destination? (In a Data Viewer) Do the dates look "normal"? Are you sure that the column it's mapped to in the database is a DATETIME type? Is it a SMALLDATETME type? Does it allow NULLS?

Yes, I've converted it from a DT_FILETIME. The column in the databse is a datetime, not a smalldatetime. It does allow nulls. But your suggestion to use a data viewer was a good one, but I still don't know how to fix it.... The null date going into the ole db control is 1/1/1601 12:00:00 AM. So, I put in a conditional split to try to reroute the null values. My split is YEAR(SOFValuesArchivedDate) == 1601, which "turns black" (is acceptable code), but then causes the following error: [Conditional Split [3208]] Error: An error occurred while evaluating the function. [Conditional Split [3208]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Conditional Split" (3208)" failed because error code 0xC0049067 occurred, and the error row disposition on "output "No Archived Date" (3241)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Conditional Split" (3208) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. There is no more information before these lines.

OK - what we do know: Your "null date" isn't a NULL... Somewhere in the reading of that column from Excel, or in your transformations, that value that used to be a NULL in the database has transformed itself to "1/1/1601" as it's traveled from the DB to Excel and almost back again. But wow... the error is weird. Well, the only thing I can think of at this time is that you have some "fake NULLs" and some "real" ones sprinkled in the data. (Your "fake NULLs" are those 1/1/1601 dates.) Because if the syntax checker of the Conditional Split likes your expression (and it does) then that means you DO have a date type, and YEAR should work. The only reason I can think that YEAR would not work is if the value was a true NULL. So... Can you try this in your Conditional Split expression? Keep your current "YEAR" split expression, but add another condition ahead of that one: Code SnippetISNULL(SOFValuesArchivedDate) So, to reiterate, what I want is three (3) outputs from your Conditional Split. The first one is the ISNULL detector, the second is the YEAR = 1601 detector, then the "everything else" default output. If that doesn't work, then I'll have to ask you to start down the path of checking your Service Pack level, blah, blah, blah, and/or send me a copy of that Excel spreadsheet...

WHEW! I got it! Thanks for the suggestion to add the data viewer. Don't know why I didn't think of that. Anyway, this is what I did. I added the conditional split and this is what I split on: [Copy of SOFValuesArchivedDate] == (DT_DBTIMESTAMP)"1/1/1601 12:00:00 AM" This actually routed the null archive dates to a different OLE DB control. So now I have 2 controls to the same sql table. The original one maps the archive dates and the second one doesn't. SO I don't even try to map the null dates. Not very elegant, I know, but it works! If any one has a better solution, I'd love to hear it.

I'm wondering if your Conditional Split was set up incorrectly - I should have mentioned that earlier. The expression you listed referred to "SOFValuesArchivedDate", not "Copy of SOFValuesArchivedDate"... could that be a typo? There's really no reason why YEAR() would crash when given a datetime type...

That's a good point, so I tried it again, this time with the conditional split as follows: YEAR([Copy of SOFValuesArchivedDate]) == 1601 But I get the following error messages: [Conditional Split [3208]] Error: An error occurred while evaluating the function. [Conditional Split [3208]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Conditional Split" (3208)" failed because error code 0xC0049067 occurred, and the error row disposition on "output "No Archived Date" (3241)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Conditional Split" (3208) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Hi, you can do this with Visual Basic application that would be used for importing Excel to database. While developing Visual Basic application you would use Visual Studio designer to automatically (with drag & drop) generate typed DataSet and DataAdapters from your database. Then you could use this Excel C# / VB.NET library to import Excel to DataSet. Here is a sample Excel VB.NET code: Dim ef = New ExcelFile() ef.LoadXls("DataSet.xls") For Each ws As ExcelWorksheet In ef.Worksheets Dim dataTable = dataSet.Tables(ws.Name) ' If excel cell value needs conversion, do it here. ws.ExtractDataEvent += Function(sender, e) If e.ErrorID = ExtractDataError.WrongType Then ' e.DataTableValue = parse e.ExcelValue e.Action = ExtractDataEventAction.Continue End If End Function ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows(0), ws.Columns(0)) Next After that you only need to update your database from DataSet using DataAdapters.