This might be tied in with question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_22811697.html. I'm not sure, as I am only about a week into using DTS and still learning some of it. I have a DTS package that ultimately is copying data from SQL Server to Excel.
I have two Execute SQL Tasks in the package. The first one drops the table, the second one creates the table. I am getting erratic, but almost consistent results in that the task that creates the table returns the following message.
Multi-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
The Transform Data Task also occassionally generates this same error.
In order for the DROP TABLE task to work, I have to put a $ after the table (worksheet) name (the result of which I generated the above linked question), although there isn't one in the actual workbook. If I take it out, DTS tells me table `Rejects` doesn't exist (it does, I can see it). I've tried putting a $ after the table name in the CREATE TABLE task, but I still get the above error.
I am having a hell of a time getting actual transformation task properly designed.
I go into the Destination tab, I have the table name (Rejects), all columns are defined as VARCHAR, I run the transformation, and it creates a new worksheet named Rejects1 that gets populated, and the Rejects worksheet is empty. I am juggling back and forth, and cannot get this process to run homogeneously.
Can someone provide me some assistance, please!
I'd like to eventually delete the DROP TABLE and CREATE TABLE tasks, because part of the package copies a template spreadsheet to a new name. The template sheet already has column names, and the proper formatting, so it is just a matter of moving the data from SQL Server into this spreadsheet and getting the proper column mappings.
Start Free Trial
