What is the correct way of importing data from Excel 2007 file to SQL Server database 2008? The data from excel file should be transferred successfully even if the data in Excel file is changed / replaced with different data except the column name at first row.
I have done the same process Tim, to a SQL Server DB. It works successfully. But once I change the data from the excel file, I get an error like"[Audit [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Risk" (100)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Risk" (100)" 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."
Sreejesh Kumar
2010-06-02 14:06:22
A:
Excel is very finicky about how you remove rows from it. Your best bet is to select all rows below the column headers and right click and delete. If you simply clear contents and then don't paste in as many rows, it looks to SSIS like you have rows with nulls in them which sometimes causes the types of errors you are seeing.
William Todd Salzman
2010-06-02 18:04:08
But if other users/clients remove the rows without deleting, the error occurs. Once the package is deployed to another server, if the same problem occurs then there is no solution over there. But inside the BIDS, we can solve by refreshing the meta data.Is this product problem, where microsoft is still working on it ??
Sreejesh Kumar
2010-06-03 06:33:32
I did the same what you suggested. The same error occurs. Have you overcome with this ? Got any solution ?
Sreejesh Kumar
2010-06-03 06:44:17
If the users/clients are changing the schema, then automating this process using SSIS is not going to work. I am not sure MS will ever implement the ability for dynamic schemas. See this article: http://www.eggheadcafe.com/software/aspnet/35029554/dynamic-refresh-of-schema.aspx
rfonn
2010-06-03 12:30:33
Then I think we better go for Script Task. I think this will work using Script Task.
Sreejesh Kumar
2010-06-04 04:26:50