question

Bab avatar image
Bab asked

The package path referenced an object that cannot be found

Please help. I have a SSIS package that excutes fine when executing from the SSIS but when i run from the store procedures that call the package i am getting this error: "Description: The package path referenced an object that cannot be found: "\Package.Variables[VPID].Value". This occurs when an attempt is made to resolve a package path to an object that cannot be found." VPID is where i am passing a Variable. I am runing this package from SQL server 2005 64 bit SP4. I have the same package runing on 32 bit without any problem. I can run from SSIS manually or from store procedure. Is this the issue with 64 bit. I try to install 32 bit SQL server 2005 but it didnt allow me since the existing verion is the latest one. Any help great appreciate!!
sql-server-2005
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
AS @TRAD mentioned, if you provide details how you are executing the package in the stored proc, it will be easier to help you.
0 Likes 0 ·
Bab avatar image Bab commented ·
This is the sp i am running to call the SSIS package. The package is generting the text file. It is running fine when i run manually from SSIS but there is a problem when i run from SP. Create PROCEDURE [dbo].[sp_ExportTextFile] @PPID Varchar(50) AS DECLARE @ReturnCode int DECLARE @Error int Declare @SSISPath as varchar(200) DECLARE @ExPath As Varchar(80) Declare @cmd as varchar(300) set @SSISPath = 'TEST' --name of the SSIS package in SQL SET @ExPath = 'C:\Export\Invoice.CSV' select @cmd = 'dtexec /SQL "' + @SSISPath + '"' select @cmd = @cmd + ' /SET \Package.Variables[VPPID].Value;"' + @PPID + '"' select @cmd = @cmd + ' /SET \Package.Variables[VEXPath].Value;"' + @ExPath + '"' Print @cmd Execute @ReturnCode = master..xp_cmdshell @cmd
0 Likes 0 ·
Tim avatar image
Tim answered
There are issues with Excel with x64 as there isn't an 64 bit jet driver for Excel. Are you using an Excel destination in your package? Also how exactly are you calling the package within the stored procedure?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bab avatar image Bab commented ·
I am generating the text file from the SSIS and using dtexec command from the store procedure to call the SSIS package.
0 Likes 0 ·
Tim avatar image Tim commented ·
I can't tell from your sproc where you are calling dtexec from. It appears as if you are expecting that the path is in your environment variables. Can you try to use the full path? C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Try construct the /SET parameter so it look in final like this: /SET "\Package.Variables[User::VPPID].Properties[Value]";"your value"; so the two lines should be: select @cmd = 'pathTo dtexec\dtexec.exe /SQL "' + @SSISPath + '"' select @cmd = @cmd + ' /SET "\Package.Variables[User::VPPID].Properties[Value]";"' + @PPID + '"' select @cmd = @cmd + ' /SET "\Package.Variables[User::VEXPath].Properties[Value]";"' + @ExPath + '"' print @cmd This works for me well on x64 system. Of course you could put a complete path to the appropriate dtexec utility (x86/x64)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Aah... You have to create the variables on package level. It's a common mistake and it was source of a lot of problems in many questions on Asks related to SSIS. What is worse, that it is easy to do such mistake but sometimes hard to track that source of problems as normally everything looks correctly. :-)
1 Like 1 ·
Bab avatar image Bab commented ·
Thanks Pavel, Is it possible my /SQL switch of dtexec command got corrupt as its not functioning properly?? Just to find the real issue, I created a simple SSIS package with out any variables and ran it using /SQL switch, it gave me this error: **Could not load package "package1" because of error 0xC001000A. Description: The specified package could not be loaded from the SQL Server database.** I have imported the Package1 in Integration services under **File System** and **MDSB** folder. But I can ran the package using /f and /dts switch. I just want to know the problem with the /SQL switch in my SQL server 2005 64 bit? since i have use this so many times before with out any issues....Weird!!
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Soo.. the **/SQL** switch specifies that the package should be loaded from SQL Server MSDB database. Then you have to provide the path to the package including any folders and subfolders in the msdb SSIS store, but the msdb itself should be omitted. If you conenect to the Integration Services and see the package eg udner the `MSDB\MyFolder\MyPackage` then the parameter should be in following form: /SQL "\MyFolder\MyPackage" Also when using teh **/SQL** switch, you should provide a server from which the package should be obtained. so you should also add **/SERVER** switch to the dtexec. so you will have: /SQL "\MyFolder\MyPackage" /SERVER "." Where "." represents a local machine. If the package is executed on other machine than the SQL Server instance is running, then you have to put there the server name(IP) instead of dot.
0 Likes 0 ·
Bab avatar image Bab commented ·
Pavel I was doing everything as you suggested but still had a problem until I found that the variables need to be created at the Package level/Scope. I had it at Task Level initially and I wasn't able to change the scope to the Package Scope ( Not so Good). I had to delete all the variables and recreate them under package level and it worked. Thanks!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.