SSIS - Import Excel Task Running Fine in Visual Studio, Failing as a SQL Job
I'm having difficulty with a package I have created to import an Excel Spreadsheet into a SQL Table.
Basically we have an aged Call System that doesn't have an ODBC setup for exporting Call Stats information, however it is able to export the information into a CSV format and email out.
I have created a VBA Process in Outlook that saves said file to a folder upon it being emailed out. I have then created an SSIS Package that takes whatever file is in the directory and imports it into SQL.
This works without issue in Visual Studio and then Executing the package using the debugger, however when the job actually runs in SQL Server Agent the job states it completes successfully however the file remains and doesn't import (the file is supposed to be deleted at the end of the SSIS Package.)
Anybody able to shed some light on this for me.
Cheers.
After checking the file path you decide file is going to load or not. If not then create dynamic filepath variable.
To track these enable the SQLLogging in SSIS package and check sysssislog table on msdb database.
Thank You Very Much Both.
Rainer that was spot on enabling the Step Output History and getting a detailed log pointed me in the direction of a variable that was being populated with a datetime and it was a 'string' type.
I amended this and run ran and this ran perfectly, surely with an error being outputted though the package should fail and not succeed?
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.