asked on
SSIS - Import Excel Task Running Fine in Visual Studio, Failing as a SQL Job
Morning All,
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.
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.
Microsoft SQL ServerSSRSMicrosoft SQL Server 2008SSIS
Last Comment
ADT58
You might check the allowable errors in your package, and consider running the sql agent job with a proxy with credentials that has permission to the directory your modifying.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Hi,
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?
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
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.