April 10, 2025 at 7:44 pm
Good Day SQL Guru's,
I have a problem I am hoping someone can give me some guidance on as It has been very challenging SQL 2019 environment. We have an Excel file that needs loaded into a database. These Excel source files can come from more than one end user and the idea is to just have an SSIS running on a schedule that will pick up the files if they are present in the source folder. The first step of the SSIS is in fact a script task that just sets a BOOL to true if the file exists and the connector to the next step is an expression (this will be important in the explanation of the problem later). I have tried just using Excel as a source but of course the sampling means that the wrong data types keep getting selected. I messed with both the registry key to increase sampling as well as the advanced editor but still having problems so decided to switch gears. This seemed like a good idea for 2 reasons since the data also need to be cleaned up a bit since users have entered "-" or "n\a" instead of a null cell. I cant rely on the end user to convert to CSV so I (Read as Google Gemini) created a VB script that can open the excel files do some global replacement and save each worksheet to a CSV. This all works great in Visual Studios as well as running the package directly from SSISDB. However I need this job to run on a schedule so a SQL agent job was defined. The SQL agent job runs and based on the SSIS reports it gets to the "parse Excel to CSV" step however it seem to stop there. After some research a suggestion was to convert the VBScript to a script task in the SSIS as it was likely a permission issue with running the VBScript. So I (also Read Google Gemini) converted the process to a script task in the SSIS package via C#. Again the package runs successful in Visual Studios and in SSISDB however in the SQL Agent the SSIS fails with an error that Excel cannot locate the source file. As I stated earlier the first step of the process is to check if the file exists so the Sql agent service account has access to see the file but that permission is not extended to the Excel object in the script task. I have been looking for a solution for 3 days and have not been able to determine a fix or even alternate method that would work. Any assistance or advice would be greatly appreciated.
P.S. I apologize for the post across topics. I realized I posted in the wrong location likely under data warehouse but when I tried to edit and change the forum location I received a message that is was SPAM and being removed.
April 10, 2025 at 7:58 pm
Check SSIS Package Configuration: If you're using SSIS, ensure the package is configured to run under a domain account with access to both the file and the Excel object.
April 11, 2025 at 2:14 am
I tested that was the case quite a bit. I temporarily changed the SQL agent and integration service account to my own admin account. I then logged into the server with my credentials. I ran the job from SSISDB and from SQL agent but had the same results. My only assumption is that because SQL agent is a more confined running environment it is not using credentials that would be inherited through normal login. So I manually added permission to the COMs thinking that might solve the issue but had the same results.
April 11, 2025 at 2:15 am
are there additional permissions I might need to add that I am not thinking of? I did add non inherited direct rights to the folder and the source.
April 11, 2025 at 8:27 am
If you check the All Executions report, does that give you any additional warnings or errors?
Also, if your Excel files always have column headings, importing the data with IMEX=1 and no header (so the headings are treated as data) usually does the trick with those pesky datatype issues.
April 11, 2025 at 9:06 am
We began switching to c# scripts in SSIS to parse the excel and output the required columns
April 11, 2025 at 11:16 am
April 11, 2025 at 2:09 pm
TAV29 you are a saint. The link was exactly what I needed and the process is now working. Thank you so much.
April 11, 2025 at 5:08 pm
Your welcome.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy