Click here to Skip to main content
12,950,090 members (64,815 online)
Rate this:
 
Please Sign up or sign in to vote.
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
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 17-Sep-12 18:23pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
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)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,749
CHill60 3,380
Maciej Los 2,953
Jochen Arndt 1,935
ppolymorphe 1,820


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 18 Sep 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100