June 3, 2009 at 8:54 pm
Hi all.
I've done this before with .csv files, so I figured querying data from my SQL Server to an excel file would be a piece of cake.
I need to output to a different file name based on the date, for an excel dump that runs daily, so I can't just do a one-off DTS export and be done with it. However, I started by doing this and proving that the query outputted to excel fine from the SQL Server Management Console.
I saved the resulting package and then opened it in Visual Studio 2005, so I could see the graphical components of the SSIS package and set a variable to represent my file/path which will allow me to change the name daily.
Before I made this change by adding a path/file variable, I simply tried executing it from the interface as it was created, with a hardcoded path/file destination. This should have worked with no problem.
Instead, I received the following message:
[Execute SQL Task] Error: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.
I would have thought I was executing the package with the same user rights in both interfaces, and I can create folders and files in my destination directory when I am logged into the server as this same user (who is administrator and has administrator rights), so there don't seem to be any bona-fide permissions issues, and I suspect this is yet another dodgey message, courtesy of Microsoft.
I should point out that our server is 64-bit, because I know there are some ISSUES with running SSIS packages from jobs, for example, using the 64-bit SSIS .exe. However in this case, I'm simply launching the package from the Visual Studio interface, and it is identical to the one I ran from the Management Console to create the package in the first place.
I have read there are limitations on the types of fields one can output to excel from SQL, and in this case, it had setup one conversion on a field that is NN-NNN (where the Ns are numbers) to be a text field. It is nvarchar(6) in our database, so I have no idea why it felt the need to set it up to convert to text in this case, but again, it worked properly in the management console, so this shouldn't be the issue... it's just strange.
The problem seems to be with the output destination in any case, and it almost looks like it is trying to match up the data with existing columns in an already existing spreadsheet... however, I have deleted the spreadsheet before trying to run this, and from both interfaces was creating a brand new .xls file that didn't exist yet.
Any suggstions or specific steps for setting this up as an SSIS package would be very much appreciated. If anyone has some quick instructions written down, I can work through those... likewise for tips on why I might be getting this failure... it's rather abstract!
Thanks in advance!
June 4, 2009 at 12:21 am
Hello,
According to the following thread, the Excel Connection Manager won’t work on a 64 bit machine:-
http://www.eggheadcafe.com/conversation.aspx?messageid=29627397&threadid=29627390
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 4, 2009 at 12:29 am
Thanks John.
I had a sinking feeling the answer was going to be something like that...
It's like Microsoft ran out of time and went, "Oh well, we just won't finish the SSIS 64-bit stuff. Nobody will probably notice anyway." ARGH.
If only there was a way to call the 32-bit exe for this and run it from that... maybe there is, I'll read the link you posted.
Much appreciated.
June 4, 2009 at 7:23 am
The MSDN article on this may also be useful, it helped me:
July 28, 2009 at 8:20 am
I copied the DTExec file that works for excel 32 bit which is in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe to C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ folder but named the 32 bit one to DTExec32.exe
then I was able to run my SSIS script through a stored proc:
set @params = '/set \package.variables[ImportFilename].Value;"\"' + @FileName + '\"" '
set @cmd = 'dtexec32 /SQ "' + @packagename + ' ' + @params + '"' --DECLARE @returncode int exec master..xp_cmdshell @cmd --exec @returncode = master..xp_cmdshell @cmd --select @returncode
otherwise if you have sql 2008:
Note:
The Run64BitRuntime project property applies only at design time.
Selecting 32-bit or 64-bit Package Execution in a SQL Server Agent Job
When you configure a SQL Server Agent job with a job step type of SQL Server Integration Services Package, the job invokes the dtexec utility. However, the version of the dtexec utility that the job invokes depends on what versions of SQL Server and SQL Server Agent have been installed and are running on the 64-bit computer:
•The 64-bit versions of SQL Server and SQL Server Agent have been installed and are running on the computer. Then, a job step type of SQL Server Integration Services Package invokes the 64-bit version of the dtexec utility, and the package runs in 64-bit mode.
Note:
To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box.
•The 32-bit versions of SQL Server and SQL Server Agent have been installed and are running on the computer. Then, a job step type of SQL Server Integration Services Package invokes the 32-bit version of the dtexec utility, and the package runs in 32-bit mode.
July 28, 2009 at 5:49 pm
Hi Gino.
I think a better thing to say than that the 32-bit property only applies at design time is to say that it obviously only applies in that interface.
If you run your package from the Business Intelligence Studio with the project property run64bitruntime = false, then it will indeed execute it as 32-bit from within that interface, and certainly for excel exports, they execute just fine.
In my case, my problem was that I'd forgotten I'd created a new solution which defaulted that property key to true. Soon as I set it back to not use 64-bit, everything was fine.
It is true however that if you are running the package from a job in 64-bit, then any and all packages will fail. Period. And will throw up some pretty weird errors in the process. When launching integration packages from a job, you need to run from a command line that calls the 32-bit dtexec.exe with your package as a parameter, rather than just choosing to execute an integration services package, which will automatically default to 64-bit and fail.
June 23, 2016 at 5:04 am
This functionality appears completely broken in SSIS 2014.
You can generate files in the IDE or by command line but cannot do so using an SQL Agent Job.
With 32 but Access Drivers Installed,
With Execute as 32 bit specified in the step or even using a command line step that explicitly calls the 32 bit dtexec. It simply returns the same error messages as a command line calling the 64 bit one.
An absolute total waste of my time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply