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
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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)