Encountered a weird one today and I blog in the hope that some whizz out there will know the reason why.
I have a fairly simple SSIS package that takes a flat file and imports to a database table. The control flow looks like this:
1. Find all files (pipe delimited) that need to be processed. Files are located on a network folder.
2. Calls a SP that creates the staging table or, if already exists, removes all rows.
3. Call DFT that imports file into staging table (see image below).
4. Calls a SP that performs and UPSERT from the staged table to the main table.
4. File system task that creates an archive folder.
5. Places processed file into archive folder.
For testing purposes I have readied a single flat file with one row of data.
Now the package executes fine on my local machine within BIDS and also via Integration Services up on my SQL server – all steps execute, data placed in the database and the file archived.
The weirdness occurs when I call the package as a step within a SQL agent job. The job runs successfully (SP called and the file is archived) but no data is placed into the staging table.
Strange! Must be a permission thing…
The user assigned to the SQL Agent service (which the job runs under) has all relevant permissions proved by the fact that the database is called and the file is moved. It just seems to bypass the retrieving data from the file!
The package logs and I can see the following when the package is run interactively
component OLE DB Destination" (285)" wrote 1 rows.
and this when run under the agent
component OLE DB Destination" (285)" wrote 0 rows.
This tells me that the DFT is being executed all the way to the “OLE DB Destination” task.
I have logged into the server as the user assigned to the SQL Agent service and ran the package again from IS and the agent job but the outcome was still the same!!!
The fact that running the package interactively works when using this profile suggests that the account is ok but there is a subtle issue with this user under SQL Agent.
Any ideas out there?
Finally managed to resolve this. (Much thanks to bluedog67 for some handy diagnosis tips).
Turns out that the issue was with the protection level of the package and the use of XML package configuration.
Early in my investigation into the problem I looked at the ProtectionLevel and it was set to “DontBeSensitive”. I also set the property to this when I imported the package up to the server (just to be sure!).
What I didn’t realise was that when I created the XML package configuration (to store a conn string and folder location) the ProtectionLevel must have still been set to the default “EncryptSensitiveWithUserKey” as there were specifics denoting this when I looked at the XML (Author, Key etc).
When I removed the package configuration and deployed the package again it all worked!!!
A strange issue but one to look out for in the future.
This article makes pointers to the issue.