Executing SSIS Package in TSQL

See more:

SSIS

T-SQL

dtsearch

Hi experts,

I have dts package that generate report to flat that accepts input parameter. The problem is although it execute and successfully create the report it does not generate my desired output. Wherein it supposed to extract 180 but just generate 87.

Below is my TSQL query

use pos
declare @tempTpaID varchar(50)
--Cursor to scan each tpaid
declare scanTpaID cursor read_only
for	
	select distinct tpaid from transaction_header
	where tpaid='1517'
	--select tpaid,bccode from transaction_header
	
--open the cursor
open scanTpaID

--set the tempTPAID
fetch next from scanTpaID
	into @tempTpaID

while @@fetch_status=0
begin
	
	--print @tempTpaID
	
	declare @cmd nvarchar(200)


	set @cmd = 'dtexec.exe /F "C:\Test\UploadFile.dtsx" /de "atms"  /Set \Package.Variables[User::tpaVal].Value\;'
	declare @cmd2 nvarchar(200)= @cmd + '"' +@tempTpaID + '"'
	--print @cmd2
	EXEC xp_cmdshell @cmd2;

	--fetch value again to set another tpa
	fetch next from scanTpaID
	into @tempTpaID
	 
end

close scanTpaID

deallocate scanTpaID

Below as well is the logs of the query

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started:  10:20:22 AM
Progress: 2012-09-18 10:20:23.14
   Source: Generate Detail Upload File
   Validating: 0% complete
End Progress
Progress: 2012-09-18 10:20:23.27
   Source: Generate Detail Upload File
   Validating: 50% complete
End Progress
Progress: 2012-09-18 10:20:23.28
   Source: Generate Detail Upload File
   Validating: 100% complete
End Progress
Progress: 2012-09-18 10:20:23.28
   Source: Generate Detail Upload File
   Validating: 0% complete
End Progress
Progress: 2012-09-18 10:20:23.33
   Source: Generate Detail Upload File
   Validating: 50% complete
End Progress
Progress: 2012-09-18 10:20:23.33
   Source: Generate Detail Upload File
   Validating: 100% complete
End Progress
Warning: 2012-09-18 10:20:23.33
   Code: 0x80049304
   Source: Generate Detail Upload File SSIS.Pipeline
   Description: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
End Warning
Progress: 2012-09-18 10:20:23.33
   Source: Generate Detail Upload File
   Prepare for Execute: 0% complete
End Progress
Progress: 2012-09-18 10:20:23.33
   Source: Generate Detail Upload File
   Prepare for Execute: 50% complete
End Progress
Progress: 2012-09-18 10:20:23.33
   Source: Generate Detail Upload File
   Prepare for Execute: 100% complete
End Progress
Progress: 2012-09-18 10:20:23.34
   Source: Generate Detail Upload File
   Pre-Execute: 0% complete
End Progress
Progress: 2012-09-18 10:20:23.41
   Source: Generate Detail Upload File
   Pre-Execute: 50% complete
End Progress
Progress: 2012-09-18 10:20:23.41
   Source: Generate Detail Upload File
   Pre-Execute: 100% complete
End Progress
Progress: 2012-09-18 10:20:23.43
   Source: Generate Detail Upload File
   Post Execute: 0% complete
End Progress
Progress: 2012-09-18 10:20:23.43
   Source: Generate Detail Upload File
   Post Execute: 50% complete
End Progress
Progress: 2012-09-18 10:20:23.46
   Source: Generate Detail Upload File
   Post Execute: 100% complete
End Progress
Progress: 2012-09-18 10:20:23.46
   Source: Generate Detail Upload File
   Cleanup: 0% complete
End Progress
Progress: 2012-09-18 10:20:23.46
   Source: Generate Detail Upload File
   Cleanup: 50% complete
End Progress
Progress: 2012-09-18 10:20:23.46
   Source: Generate Detail Upload File
   Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  10:20:22 AM
Finished: 10:20:23 AM
Elapsed:  0.468 seconds
NULL

Thanks,
Dan