| Author |
Topic |
|
rockstar283
Yak Posting Veteran
96 Posts |
 Posted - 2012-02-08 : 13:53:27
|
| I am trying to create a dynamic excel destination using the answer provided here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/bda433aa-c8f8-47c9-9e56-efd20b8354ac/ I am following the same exact steps.. my SQL task is creating a file and a sheet/table inside the file properly, but when creating the Excel destination..when I click on the NEW table/View button..I am gettting 'Unspecified Error' 2-3 times..if I click retry, nothing happens..If I click Cancel 2-3 times, then it goes through, asks for the new table..here after giving the new table script, again I get 'Unspecified Error' 2-3 times. Again same, if I click retry, nothing happens..If I click Cancel 2-3 times, then it goes through and tells me that "The table name could not be retrieved from the script provided. Select a table or view from the drop-down list box". I am using the following script to create the table CREATE TABLE `Master_Table_Ascender_DATA`( `PATIENT_ID` INT, `VALUE` INT, `LAST_NAME` NVARCHAR(50), `FIRST_NAME` NVARCHAR(50), `DOB` DATETIME, `MRN` INT, `GROUP_NAME` NVARCHAR(50) ) When I click on the drop down box and it gives me a message 'Please select the new Excel sheet from the Dropdown list box.Any white space in the excel sheet name may have been replaced by "_" '. When I click OK and hit the drop down box, again says 'Unspecified Error'. Again same, if I click retry, nothing happens..If I click Cancel 2-3 times, then it goes through and gives me a message 'No table or views could be loaded' There is absolutely no other error message I am getting, nothing descriptive about it. I am dumbfounded here..please help me guys :'( Update.. Instead of New table/View, if I use SQL Command in the Excel destination then it is giving me an error: SSI Error code DTS_E_OLEDBERROR. An OLEDB error has occured. Error code 0x80004005. An OLEDB record is available. Source: "Microsoft JET database engine". Hresult 0x80004005. Description : 'Unspecified Error' :(Here is a link to the image of the error:http://imageupload.org/getlinks.php?s=c76f167ae3c602d41bc77a5a51ccc594 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-02-08 : 14:15:04
|
| you always need a dummy excel file in destination folder just to bypass those errorsIf you don't have the passion to help people, you have no passion |
 |
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 14:16:55
|
| I am assuming the first line is for me and the second line is your signature.. ;)anyways I have the file present there as a result of executing the SQL Task..still the problem persists |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-08 : 14:19:12
|
| are you using correct connection manager? also is there excel template with column names to set initial mapping?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 14:25:12
|
| Yes..the SQL task given in the procedure is working and has created a file with proper columns inside it.. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-02-08 : 14:26:41
|
signature fixed :)quote:
Originally posted by rockstar283
I am assuming the first line is for me and the second line is your signature.. ;)anyways I have the file present there as a result of executing the SQL Task..still the problem persists
<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-02-08 : 14:34:42
|
| is your destination static or dyamic<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 14:37:11
|
| The destination is deleted using a file system task..then a SQL task creates the excel file and table/sheet inside it..and then data flow task is used to dump data in it..first two actions are working but having problem with the last one |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-08 : 14:45:01
|
quote:
Originally posted by rockstar283
The destination is deleted using a file system task..then a SQL task creates the excel file and table/sheet inside it..and then data flow task is used to dump data in it..first two actions are working but having problem with the last one
whats the error data flow task throws?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 14:47:54
|
| http://imageupload.org/getlinks.php?s=c76f167ae3c602d41bc77a5a51ccc594I am not able to create new table/view there.. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-02-08 : 14:49:43
|
| you definitely need a template / dummy excel file. 1. Always write to the dummy file2. Save your dummy file as your dynamic destination file3. Delete your dynamic destination file<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 14:53:46
|
| Did that..but still why the heck Unspecified Error :'( |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 14:58:37
|
| One more update..If I remove the dynamic nature and just keep the file static..still I am getting unspecified error problem..what i did is that I took SQL task and created a table there using excel connnection manager to whom I have given the existing file path..this task is succeeding..now in the DFT, if i am using excel destination with the same excel connection manager with the same static file..now when I click on table/view dropdown box, it still gives me an uNSPECIFIED error..So the problem is present for static files as well.. :( |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 15:04:11
|
| If instead of New table/view..If i use SQL command and use the create table script..I am getting the following error:http://imageupload.org/getlinks.php?s=bf989f3f8a3c99bfc9852400ac9330ad |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 15:06:52
|
| I am inclining towards a driver issue now :-O |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-08 : 15:27:48
|
| i think it might be even that deleting the file is removing reference from the connection manager and hence its erroring next time you try to access the file------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2012-02-08 : 15:37:25
|
| iT JUST WORKED..Create a package from scratch on another machine..and copied it on my machine..now its working..but still dont understand the Unspecified error.. :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-08 : 16:29:15
|
| i think connection manager might have lost reference of file during deletion process------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Problem with Dynamic Excel Destination