March 25, 2020 at 8:23 pm
Hello,
I have created an SSIS package which calls a SQL PROC and exports the data to an Excel file. We use an Excel template within the ConnectionString name, then within the ExcelFilePath we change the name of the file and add a date. However, we are getting the error message at the bottom of this message and cannot seem to resolve.
If we rebuild the Data Flow package or remap the Excel Destination step, it seems to work on the first try. However, if I delete the renamed file and attempt to run again, I get the following message. Can someone help me understand what I am doing wrong?
The Excel Destination ConnectionString expression looks like this to get the template Excel file:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\servername\Template_DO_NOT_MOVE\Temp.XLS;Extended Properties="EXCEL 8.0;HDR=YES";
The Excel Destination ExcelFilePath, it looks like this:
@[User::FilePath] + (DT_STR, 10, 1252) @[User::Branch] + "_List_"+
(DT_WSTR,4)YEAR(GETDATE()) +
RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + ".XLS"
This is the error message received:
IS package "List.dtsx" starting.
Information: 0x4004300A at Potential Issues, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Potential Issues, PotentialIssues [214]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error: 0xC02020E8 at Tab111, Tab111 [214]: Opening a rowset for "Tab111" failed. Check that the object exists in the database.
Error: 0xC004706B at Potential Issues, SSIS.Pipeline: "component "PotentialIssues" (214)" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Potential Issues, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Potential Issues: There were errors during task validation.
Warning: 0x80019002 at List: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyList.dtsx" finished: Failure.
March 26, 2020 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 27, 2020 at 2:22 pm
This is a guess based on exporting to Excel from other systems. SSIS may be maintaining a file lock on the template file so when you try and run the package again it can't access it.
I've not exported to Excel from SSIS so there might be another solution but can you copy the template to the new filename and then open the new file?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply