June 22, 2009 at 3:04 pm
I have a package that works perfectly. The beef of the package takes several tables and imports them into excel. It was created by a developer using the wizard.
If I take a copy of the package and generate a new ID I can make changes and it still runs fine.
The minute that I enable package configurations so that I can make changes from the Config table without opening the package I get errors. This happens independent of the agent. One thing that makes me wonder is the password field on the Excel connection. I've tried leaving it off as well but had no luck.
Error: 2009-06-22 15:40:02.59
Code: 0xC0202009
Source: PackageName Connection manager "DestinationConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
End Error
Error: 2009-06-22 15:40:02.61
Code: 0xC00291EC
Source: Preparation SQL Task Execute SQL Task
Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 7:53 am
Jason, are you in a 32 bit or 64 bit environment? There are known issues with the 64 bit Jet driver. I doubt if that's the only issue since it seems to work with package configurations disabled, but it's worth a look.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
June 23, 2009 at 7:53 am
I've done battle with similar stuff. I can't put my finger on a link right now, but the way I'd approach this is to use an OLE DB Destination instead of an Excel Destination. OLE DB is less "touchy" and it'll run in 64-bit.
Apologies for the brevity...
Hope this helps,
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 23, 2009 at 7:55 am
It's on x64 but I'm calling it with DTExec to get around that:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\SSISShare\package.dtsx" /CHECKPOINTING OFF /REPORTING V
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 8:02 am
Andy Leonard (6/23/2009)
I've done battle with similar stuff. I can't put my finger on a link right now, but the way I'd approach this is to use an OLE DB Destination instead of an Excel Destination. OLE DB is less "touchy" and it'll run in 64-bit.
Ok, I'm looking into that.
Don't have that provider option and I'm looking to see if this download is the right one.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 8:41 am
Hi Jason,
There's a way to configure the connection string property of an OLE DB Connection Manager that will allow it to connect to Excel. You can then write to the spreadsheet via an OLE DB Destination adapter.
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 23, 2009 at 8:42 am
i think that the import/export wizard did a poor job of creating the package.
the data flow task is the recommended task for the insertion of data into excel, not the execute sql task. usually, an ole db source component is used to extract the data and the excel destination is used to load the data.
hth
June 23, 2009 at 9:03 am
The execute SQL Task that I'm having trouble with is before actually putting data in.
I've created the connection fine, but when I try to execute SQL against it like the following small snippet:
CREATE TABLE `GroupREG` (
`KeyID` Long,
`CostGroupCode` LongText,
`CostPoolDesc` LongText,
`ActivityCode` LongText,
`SalaryPerActivity` Currency,
`SalaryPercent` Double,
`Amount` Currency
)
GO
CREATE TABLE `GroupSEC` (
`KeyID` Long,
`CostGroupCode` LongText,
`CostPoolDesc` LongText,
`ActivityCode` LongText,
`SalaryPerActivity` Currency,
`SalaryPercent` Double,
`Amount` Currency
)
GO
I get the following:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,servername,username,Package1,{B1DAFB1E-E84B-464E-9633-E6C250348268},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,Beginning of package execution.
Diagnostic,servername,username,Package1,{B1DAFB1E-E84B-464E-9633-E6C250348268},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 10.
Diagnostic,servername,username,D:\SSISShare\Allocation.xlsx,{2279A28E-2A9D-462B-BDDF-5B8B541DDADA},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Diagnostic,servername,username,D:\SSISShare\Allocation.xlsx,{2279A28E-2A9D-462B-BDDF-5B8B541DDADA},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The external request has completed.
PackageEnd,servername,username,Package1,{B1DAFB1E-E84B-464E-9633-E6C250348268},{B281BB49-9369-4E37-96C1-51EB136B3AC9},6/23/2009 9:36:36 AM,6/23/2009 9:36:36 AM,1,0x,End of package execution.
Throughout all the settings, I can't see where the issue is. There's no username or password on the file. The file is local. I tried blank password with no luck either.
Googling now.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 9:52 am
Andy Leonard (6/23/2009)
Hi Jason,There's a way to configure the connection string property of an OLE DB Connection Manager that will allow it to connect to Excel. You can then write to the spreadsheet via an OLE DB Destination adapter.
Andy
I've been able to do this using that download that I found. There's still the whole 64bit problem when using that provider. When I ran it in 32 bit mode it ran successfully the first time but the file wouldn't open because it said it was corrupted. Subsequent runs fail.
Still digging. Please pass on any links if you find them.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 10:05 am
Have you tried using separate execute SQL tasks for each Create statement? I don't know if the Excel provider allows batches...
Also, you say that this fails only when you enable package configurations. Have you confirmed that the package is being configured how you think it is? I know this seems obvious, but it might be worth setting up a msgbox to display the value of your configured items just to make sure the package is getting the expected values. The msgbox won't work with SQL Agent, but should do if you execute using DTExec on your dev machine.
If I remember correctly, the create table statement will create a range in excel. If the range is already defined, then this might causing an error too.
Kindest Regards,
Frank Bazan
June 23, 2009 at 10:16 am
Frank Bazan (6/23/2009)
Have you tried using separate execute SQL tasks for each Create statement? I don't know if the Excel provider allows batches...
It won't even do it with just one.
Frank Bazan (6/23/2009)
Also, you say that this fails only when you enable package configurations. Have you confirmed that the package is being configured how you think it is? I know this seems obvious, but it might be worth setting up a msgbox to display the value of your configured items just to make sure the package is getting the expected values. The msgbox won't work with SQL Agent, but should do if you execute using DTExec on your dev machine.
yes, I've made changes and then looked at the configuration table. The changes show up there.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 10:53 am
More ideas for you...
If you are trying to write to Excel and you have IMEX set to 1 in your connection string, your excel file will be in read-only mode.
You said it was independent of SQL Agent, so I assume you've checked your privileges to the file... but may be the original developer set the encryption level in a way that is causing your login problems when you run the package?
Have you checked the logic that creates the excel file? How is the file name derived? If you are copying from a template, does the copy process occur correctly? Does the package get as far as creating the new file? If so, when you open the file in excel, is the named range you are trying to create already there? If it is you won't be able to create it again without deleting the existing range beforehand.
If you shut down the package and reload with package configuration set to true, you should find all the configured values in your design environment. You can check these all evaluate correctly by running the package again with breakpoints set up and locals window displayed.
I've run out of ideas now you'll be pleased to hear 😉
Cheers
Frank
Kindest Regards,
Frank Bazan
June 23, 2009 at 11:17 am
All great ideas, but don't seem to fit with this one.
The configuration failure issues are moot at this point as I'm dealing just getting it to work with using an ODBC connection instead of ExcelDestination.
I finally have it putting the data into the sheet, but it doesn't format like it used to. I have to actually create the xlsx file first, and then be sure to use Excel 12.0 XML instead of just Excel 12.0 or it won't work.
I'm about to put it aside for a few minutes as it's driving me crazy at this point.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 23, 2009 at 12:08 pm
the excel source and destination components weren't designed to work with excel 2007 (yet another quirky "gotcha" in ssis). the work-around is use the ole db source and destination components with the Jet ACE 12 OLE DB Provider. more info is provided at the following link: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e9cca2de-0ee6-4468-8302-f817e0746eb9/[/url] make sure that "Excel 12.0" is included in the extended properties of the connection string.
hth
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply