After migrating SQL DTS packages to SSIS, in which SQL very nicely created SSIS packages for me to work with, I found I was getting System.IO.FileNotFoundException errors once I imported into SQL. The package worked fine in BI. I looked into what SQL had done when it imported these packages and noticed that the storage location for the Packages was "Embedded in Task"
It had embedded the packages within the admin users Temp directory - a directory not accessible by the SQL user that ran the packages.
To resolve this issue, Edit the package, Click Save As to save as a Structured Storage file in a central location that the SQL agent user has access to.
Change the Storage Location to be Structured Storage File and use the File attribute to point to the centrally located file. Browse to set the package name and load the required package.
Rebuild & deploy the package and all is now good! :) Make sure that you select the checkbox to
"Rely on Server Storage for Encryption" so that the package password is stored.
Alternatively - create & load the package as a seperate package in SQL and load from SQL directly in the package properties form above.
No comments:
Post a Comment
Hi,
Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.