Export dtx package but doesnt work on SQL Agent
-
Hello,
So I am trying to get this simple SSIS package to work on SSISDB, it has 1 variable called SheetName, a foreach loop, and within the foreach loop has a data flow task and the foreach loop is using ado.net schema rowset Enumerator. The purpose of this is to get an excel file from a unc path, then loop through all the tabs and import them into a single table, all tabs are the same dataset structure.
It does work on Visual studio 2017 running it, it works. when I deploy it to SQL server 2017 developer edition, and try and run it on the SQL agent, it gives the following error message:
Foreach loop container: error: the Getenumerator method of the foreach enumerator has failed with error 0x80131509 "(null)". this occurs when the foreach enumerator cannot enumerate.
I tried setting the excel file or connection manager to use a variable instead of just having it on connection manager... nope that didn't work, again still same error.
I looked online, i did try set the project to "Run64bitRuntime" to false, still same error message.
I tried to set it from SQL version 2017 to 2014, 2012 and even 2016.... still didn't work. please anyone else encounter this and fixed it besides the 2 solutions I listed? I am all out of ideas....
-
It may the the Access Database Engine version. When I worked with SSIS/Excel, Visual Studio needed the 32 bit version, which was included with my 32 bit Office 2016 so I didn't have to install anything. I think SQL Server requires the 64 bit version, when I got it last, it was here:
https://www.microsoft.com/en-us/download/details.aspx?id=54920
-
And if it's not that, it will probably be a permissions issue. Does the SQL Agent user have the necessary access to the file system?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hey Phil, good thought, I was just thinking that and checked, plus added my account that will run the SQL agent service and sql job/ssis package... unfortunately, still the same error message 🙁 but thank you, any other ideas is welcomed... as I don't have any other ideas left....
-
OK, interesting.
How are you populating your enumerator? Can you share some details?
You could consider adding some logging (Dts.Events.FireInformation, maybe) to check the contents of the enumerator before entering the Foreach loop.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hey Phil, thanks for the info, ok so on the properites of the foreach loop, when i go to collection, on the foreach loop editor, where it says Enumerator, i have it as "Foreach ADO.Net schema Rowset Enumerator", below that, the connection is the microsoft office 12.0 Access database Engine OLEDB provider which points to an excel file. then the Schema is Tables, no restrictions are set. the variable Mapping is using 1 variable which is the SheetName veriable index 2.
I used this example below, in the link and followed most of it to create what i have... hopefully this may help shed some light on what i am doing wrong... thank you again for taking the time to help:
https://excelkingdom.blogspot.com/2017/10/ssis-foreach-loop-to-load-data-from.html
-
Hello,
so an update, not sure if this is any better. but what I decided to do, is go through SSMS, create a import process, so it will create the excel on the dataflow on the ssis package, then open the ssis package, add the dataflow to a foreach loop... make the same configuration of the foreach loop... however now I am getting the same error message now on the ssis package as I did on the SQL agent.... not sure if I am moving backgrounds or forwards... but thinking if I can possibly get this to work on SSIS, then maybe I can get it to work on SQL agent... but attached is a screen shot.
also couple more bits of into... I am using package parameters thinking this may have fixed it... NOPE.... also on the excel file connection manager, if you click the tab ALL, on the extended properties I did put "Excel 12.0", not sure if this info would help... but thought I just add that in that maybe I input something wrong?
-
Sorry I thought I replicated the issue on SSIS visual studio, but after I closed it and re-opened it... not sure why but works fine.... tried restarting the SQL engine and agent services thinking it might fix it... no, the only different now is... it runs successfully but when I look at the history, it still shows that same error message, which i attached... any other info or ideas is welcomed.
-
If you expand the Server Objects/Linked Server/Providers node of your SQL instance, what versions of the ACE driver do you see?
Actually, it's not 100% clear from your post whether you are deploying to your local instance, or to an instance on a server somewhere, please clarify that.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hey Phil, thank you again for the questions, sorry, so I am deploying to my local instance on my laptop, which does have admin rights for the user Steven (which is an AD account), but also I did a screen shot of the question you asked as well, let me know if there is any additional info you may need to maybe track down the issue? I think mentioned before, but I will again just in case, I did uninstall the office access 2016 and installed the office access 2010 32 bit version, which many mentioned on some forums that fixed some of the issues... but unfortunately didn't fix mine, as well as change the run64bit to false as well... but still didn't fix it for me 🙁
-
It looks like you have this covered already, but your SSIS development experience will be a happier one if you have both 32- and 64-bit versions of the ACE driver installed.
The way to do this is to first install the 2016 64-bit driver, then to install the 2010 32-bit driver. (If you install the 2010 version first, the 2016 version will not install as well.)
The reason for needing both is that Devenv.exe (the SSDT executable) is 32-bit and DTExec.exe (the executable which runs SSIS packages) is 64-bit (by default, on a 64-bit o/s).
Once you have both installed, there is never (in my experience) any need to run in 32-bit mode.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
gotya thanks for the info, i will go ahead and do those steps now. see if that fixes the problem.
-
k, i just installed first Access office 2016 64 bit driver, then the office access 2010 32 bit. just need your thoughts and wondering. since i installed the drivers, should i recreate the ssis package so it will work with the drivers? or will it work without needing to recreate the entire ssis package?
-
No need to recreate anything. Once the drivers are there, they will (or should!) get picked up when you next run.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
gotya, sadly still didn't work, still same error message, attached the screen shot 🙁
I rebooted my laptop to make sure the excel file that is on my C drive, and folder directories (c:\Test\TestFile\TestImprt.xlsx) is not being held by a process... but still same error.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply