While trying to migrate my old SQL Server Integration Services (SSIS) packages from the legacy Package Deployment Model project to a Project Deployment Model one, I ran into the following error on some (not all) SSIS package after selecting it for import:
Object name ‘[object-name]‘ is not valid. Name cannot end with a whitespace.
This is a typical frustrating SSIS error message, uninformative and goes nowhere. Hopefully the tip here should help you get around the error in this particular situation.
With SQL Server 2012, you can take advantage of all the features offered by the new Project Deployment Model by migrating legacy SSIS packages onto the new model, this can be easily done through creating an SSIS project in Visual Studio (SQL Server Data Tools), and importing existing packages through:
- Right Click on the SSIS Packages folder in the Solution Explorer menu.
- Select Add Existing Packages from the options (as shown in the image below).
Then you get the option of either selecting a package from the “File System”, “SQL Server” or “SSIS Package Store”, In my case I had the SSIS package on my file system, and so that was the option I opted in for.
Some SSIS packages converted fine, I still received a very scary error dialog (shown below), which reference some missing DLLs and validation failures, but nothing too serious, and the SSIS package loads fine. These errors can be corrected after the SSIS package has been converted and is loaded onto Visual Studio
But then 2 of my SSIS packages resulted in the error message shown in the image above. simple, yet completely useless:
The error message awesomely reads: Object name ‘[object-name]‘ is not valid. Name cannot end with a whitespace.
Resolve SSIS Object Name is not Valid Error
To resolve the error above, and like I mentioned this only works if you have the EXACT same situation above, then I tried to import the SSIS package through another source, either through SQL Server or SSIS Package Store. Am not entirely sure why, but SSIS must does some validation/trimming on package before deployment onto these storage mediums, and for some reason this seems to not break the new SSIS conversion process introduced with the Project Deployment model.
Frustrating, but got there in the end!