Click here to Skip to main content
15,071,581 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
CSS
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
Posted

1 solution

I think the problem is Cusror does not wait for each package execution to completed. I would suggest a you do all this in a package using For Each container: For example:
Using the Foreach ADO Enumerator in SSIS[^]
   
Comments
akosidandan 18-Sep-12 3:07am
   
Thanks man
Kuthuparakkal 18-Sep-12 3:08am
   
you're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900