question

I have created a package in BIDS. The package does the following: 1. Several data flow and SQL tasks to load text files into SQL tables. There is a connection manager for the SQL database (OLE DB Source object to connect to a SQL Server instance running on the local server). 2. A series of data flow tasks which run a SQL Query against one or more of the SQL tables loaded in step 1 use a Data Conversion object to convert the DT_STR types to DT_WSTR, in order to prevent the errors regarding converting Unicode to non-Unicode formats. Uses an Excel Destination object, linked to an Excel Connection Manager to map the columns provided by the Data conversion object to the correct columns in an Excel file and output the data to this Excel file. The Excel file name is set using an expression for Connection Manager property, and is a .xls (Excel 97 - 2003) file. The Excel file is empty, apart from the column headings 2. The package runs successfully when executed in Visual Studio. 3. Add a 'wrapper' which uses configuration files to set the variable values and execute the package. The package is then stored in the MSDB. 4. There is a job which will run the .bat file and execute the package from the MSDB and send an email on success or failure. This package and wrapper were working fine. I then: 1. Updated the package with logic for new tables and additional sheets in the Excel workbook (i.e. Report_2_b sheet is now Report_2_b_1, Report_2_b_2 etc. The package still works successfully in Visual Studio but fails when Running the step to update the Report_2_b_1 sheet in the Excel workbook. The two tables listed in the error message both exist prior to this task being run. The task has Delay Validation set. the full text is posted below. Step ID 1 Server ACMREPOSVR Job Name NEQCA_TMP DSTHS ETL Step Name 0 - Execute_ALL Duration 00:08:58 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:26:52 PM Error: 2014-10-09 14:27:24.99 Code: 0xC0202009 Source: Report 2b1 MED NEQCA_TMP_MED_NEW [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Invalid object name 'dbo.NEQCA_TMP_MED_SVC_CAT_NEW'.". End Error Error: 2014-10-09 14:27:24.99 Code: 0xC004706B Source: Report 2b1 MED SSIS.Pipeline Description: "component "NEQCA_TMP_MED_NEW" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2014-10-09 14:27:24.99 Code: 0xC004700C Source: Report 2b1 MED SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-10-09 14:27:24.99 Code: 0xC0024107 Source: Report 2b1 MED Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:26:52 PM Finished: 2:35:49 PM Elapsed: 537.547 seconds -----Original Message----- From: Database Reporting [mailto: Database@dovetailhealth.com] Sent: Thursday, October 09, 2014 2:36 PM To: DL Data Analytics Subject: SQL Server Job System: 'NEQCA_TMP DSTHS ETL' completed on \\ACMREPOSVR JOB RUN: 'NEQCA_TMP DSTHS ETL' was run on 10/9/2014 at 2:26:51 PM DURATION: 0 hours, 8 minutes, 58 seconds STATUS: Failed MESSAGES: The job failed. The Job was invoked by User ACM\lpepi. The last step to run was step 1 (0 - Execute_ALL). The job was requested to start at step 1 (0 - Execute_ALL).