PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
11 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SSIS - Import Excel Task Running Fine in Visual Studio, Failing as a SQL Job
Posted on 2013-01-04
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4 Comments
Active 6 days ago
Please check the SQL Agent get file path properly. If there is error to access the file then It will throw error related to that. -> To resolve you need to give that folder permission to SQL Agent user for Read/Write whatever need as per logic.
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.
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.
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.
Active today
Hi,
which SQL Server version are you using?
Which CPU architecture?
When running a package in VS / BIDS it will be executed as 32bit, depending on your installation the SQL agent job might run with 64bit where you will not have the Excel ODBC connection drivers.
To log a little bit more details, just configure the execute package step in your SQL agent job by
- adjusting the commend line to include "/REPORTING EWCDI
- in the "Advanced" properties to "Include step output history" and write to a dedicated log file
HTH
Rainer
which SQL Server version are you using?
Which CPU architecture?
When running a package in VS / BIDS it will be executed as 32bit, depending on your installation the SQL agent job might run with 64bit where you will not have the Excel ODBC connection drivers.
To log a little bit more details, just configure the execute package step in your SQL agent job by
- adjusting the commend line to include "/REPORTING EWCDI
- in the "Advanced" properties to "Include step output history" and write to a dedicated log file
HTH
Rainer
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?
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 3 hours left to enroll
628 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.