help on packages for a newbie - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.
All Forums
SQL Server 2000 Forums
SQL Server Development (2000)
help on packages for a newbie
Author
Topic
ronny
Starting Member
1 Post
Posted - 2004-11-15 : 15:19:25
Hi

I am a newbie to sql server and I could really use your help.

At work I found myself responsible for an access application running on sql server tables (linked tables).

I need to improve a few features in the application (the customer is really unhappy with performance of the system).
one of them is entering of data from a file to a few tables.
I managed to create a package to do that but I want to make it more flexible.
1 - Is it possible to “feed” to the package the path for the text file dynamically - can it read the path from a table ?

2 – can the package rename the file or move it to a different dir on completion of the process ?

if I can do that I can remove this feature from the access and perform it purely on the sql server – removing this from the client side of the application

Thanks
Ronny
X002548
Not Just a Number
15586 Posts
Posted - 2004-11-15 : 15:33:25
I'd use a sproc



CREATE PROC Load_Ledger_Init_sp 

AS

Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int
Declare @Proof_Year Char(4), @Proof_Quarter Char(2), @Month1 Char(3), @Month2 Char(3), @Month3 Char(3)
Declare @Fn1 varchar(255), @Fn2 varchar(255), @Fn3 varchar(255), @MaxDate char(6), @Batch_Id Int, @ws_Date datetime
Declare @FilePath varchar(255), @command_string nvarchar(4000),  @FilePathAndName varchar(255), @Proof_Month char(2)
Declare @rc int, @Load_Ind Char(1), @System_State Char(2), @MoveDate varchar(50), @db_Name varchar(255)
Declare @File1_Count Int, @File2_Count Int, @File3_Count Int, @File0_Count Int, @Timer Int

DECLARE @cmd nvarchar(4000), @var nvarchar(4000), @Comp_Code int, @Comp_Msg varchar(256), @User_Id char(8), @Log_Id Int

Set NoCount On

BEGIN TRAN


Select @Log_Id = IsNull(Max(IsNull(Log_Id,0)),0) + 1 From Tax_Load_Log

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Starting Quarterly Load Process'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' > d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

	Select    @FilePath 	= 'd:\Data\Tax\SmartStreamExtracts'
		, @Comp_Code 	= 0
		, @Comp_Msg  	= 'Load Completed Successfully'
		, @Load_Ind 	= 'Y'
		, @MoveDate = replace(replace(Rtrim(convert(char(50),GetDate())),' ','_'),':','_')
		, @db_Name = db_Name()

	Select 	  @Proof_Year   = Proof_Year
		, @Proof_Quarter= Proof_Quarter
		, @Month1	= Case  When Proof_Quarter = '01' Then '01'
					When Proof_Quarter = '02' Then '04'
					When Proof_Quarter = '03' Then '07'
					When Proof_Quarter = '04' Then '10'
								  Else '99'
				  End
		, @Month2	= Case  When Proof_Quarter = '01' Then '02'
					When Proof_Quarter = '02' Then '05'
					When Proof_Quarter = '03' Then '08'
					When Proof_Quarter = '04' Then '11'
								  Else '99'
				  End
		, @Month3	= Case  When Proof_Quarter = '01' Then '03'
					When Proof_Quarter = '02' Then '06'
					When Proof_Quarter = '03' Then '09'
					When Proof_Quarter = '04' Then '12'
								  Else '99'
				  End
		, @User_Id	= Updated_By
		, @System_State	= System_State
	  from	  System_Var

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 1
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	If @Month1 = '99' Or @Month2 = '99' Or @Month3 = '99'
		BEGIN
			SELECT @Error_Loc = 2
			SELECT @Error_Message = 'Month Not set for File name.  Check System Variables.  Values are: '
						+ ' Proof_Year=~' + @Proof_Year + '~'
						+ ' Proof_Quarter=~' + @Proof_Quarter + '~'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Proof Year and Quarter have been set to ' 
						 + @Proof_Year + ' and ' + @Proof_Quarter
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

	Select 	  @Fn1 = 'TaxDetail_'+@Proof_Year+'_'+@Month1

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 3
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	Delete From Ledger_Folder


	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 4
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	Select @Command_String = @FilePath + '\*.*'

	Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 5
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	Delete From Ledger_Folder_Parsed

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 6
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
	Select	  Convert(datetime,Substring(dir_output,1,8)
		+ ' ' 
		+ (Substring(dir_output,11,5) 
		+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
		, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size 
		, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
	  From 	  Ledger_Folder
	 Where  Substring(dir_output,1,1) <> ' '
	   And (Substring(dir_output,1,1) <> ' ' 
	   And  Substring(dir_output,25,5) <> '<DIR>')

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 7
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

-- Get File For the 1st Month of a Quarter
	
	If (   Select Count(*)
	  	 From Ledger_Folder_Parsed
	 	Where Substring(File_Name,16,2)= @Month1 And Substring(File_Name,11,4)= @Proof_Year

	    ) = 0
		BEGIN
			SELECT @Error_Loc = 8
			SELECT @Error_Message = 'First Monthly File Not Found.  Check Syntax for File Name. '
						+ ' Syntax is: ' + @Fn1 + '_yymmdd.txt'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END

	Select @MaxDate = Max(Substring(File_Name,19,6)) 
	  From Ledger_Folder_Parsed
	 Where Substring(File_Name,16,2)= @Month1 And Substring(File_Name,11,4)= @Proof_Year

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 9
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END
	
	Select @Fn1 = RTrim(@Fn1) + '_' + @MaxDate
	
	Delete From  Mthly_Ldgr_Dtl_Upld_Temp_1

Commit Tran

-- ---------------------------------------------------------------------------------------------------------------------
-- Load Temporary Tables ---------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------------------------------------

Begin Tran

select @FilePathAndName = RTrim(RTrim(@FilePath) + '\' + RTrim(@Fn1) + '.txt')

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Preparing to load Temp Table with '
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|'+ RTrim(@FilePathAndName)
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @cmd = 'bcp ' + @db_name + '..Mthly_Ldgr_Dtl_Upld_Temp_1 in ' + @FilePathAndName + ' -t"~" -c -S' + @@servername + ' -Utaxuser -Ptaxuser'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''
Select @Command_string 

Exec(@Command_String)

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 22
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

Select @File1_Count =  Count(*) From Mthly_Ldgr_Dtl_Upld_Temp_1

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|S|' + RTrim(Convert(char(50),GetDate(),109))+'|Temp Table 1 loaded with ' 
		+ RTrim(Convert(Char(18),@File1_Count)) + ' Rows'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)


commit tran

-- ---------------------------------------------------------------------------------------------------------------------
-- Prepare File 1 For Batch Processing ---------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------------------------------------

begin tran

-- Since the xp_cmdshell bcps run as an asynchronus tasks we need to make sure they've completed before we move on in 
-- the s/p...if for some reason they fail and @@Error doesn't catch it use a timer to timeout the s/p

Select @File0_Count = 0, @Timer = 0

While (@File0_Count <> (@File1_Count + @File2_Count + @File3_Count))
	Begin
		Select	  @File0_Count = File1_Count + File2_Count + File3_Count
			, @Timer = @Timer + 1
			From 	  (Select Count(*) As File1_Count From  Mthly_Ldgr_Dtl_Upld_Temp_1) As A
				, (Select Count(*) As File2_Count From  Mthly_Ldgr_Dtl_Upld_Temp_2) As B
				, (Select Count(*) As File3_Count From  Mthly_Ldgr_Dtl_Upld_Temp_3) As C

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|S|' + RTrim(Convert(char(50),GetDate(),109))+'|Timer Count Set At ' 
		+ RTrim(Convert(Char(18),@Timer))
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

		If @Timer = 10000
			BEGIN
				SELECT @Error_Loc = 12
				SELECT @Error_Message = 'bcp load time out'
				SELECT @Error_Type = 50002
	  			GOTO Load_Ledger_Init_sp_Error
			END
	End

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|S|' + RTrim(Convert(char(50),GetDate(),109))+'|File0_Count set to ' 
		+ RTrim(Convert(Char(18),@File0_Count))
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)	
		
SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Begining The process For File 1'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

	Select    @Proof_Month = Case When Proof_Quarter = '01' Then '01'
				      When Proof_Quarter = '02' Then '04'
				      When Proof_Quarter = '03' Then '07'
				      When Proof_Quarter = '04' Then '10'
								Else '99'
				 End
	  From	  System_Var

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
	
	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 14
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	If @Proof_Month = '99'
		BEGIN
			SELECT @Error_Loc = 15
			SELECT @Error_Message = 'Proof Month Not set.  Check System Variables.  Values are: '
						+ ' Proof_Year=~' + @Proof_Year + '~'
						+ ' Proof_Quarter=~' + @Proof_Quarter + '~'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END


	Update	  System_Var
	   Set	  Proof_Month 	= @Proof_Month
		, Updated_By	= 'Load1'
		, Updated_TS	= GetDate()


	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
	
	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 16
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	Select @ws_Date = GetDate()

	Select @Batch_Id = Max(ISNull(Batch_Id,0)) + 1 From Batch_Cntl

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 17
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	If @Result_Count = 0
		BEGIN
			SELECT @Error_Loc = 18
			SELECT @Error_Message = 'No Batch ID Obtained to process file 1.  Check the status of the table '
						+ ' BATCH_CNTL'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END
	
	Insert Into Batch_Cntl (
			  Batch_Id
			, Source
			, Source_File_Name
			, Source_File_TS)
	Select		  @Batch_Id
			, 'SmartStrm'
			, @Fn1
			, @ws_Date

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 19
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	If @Result_Count = 0
		BEGIN
			SELECT @Error_Loc = 20
			SELECT @Error_Message = 'No Rows Inserted in to the BATCH_CNTL Table For First Months File'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|S|' + RTrim(Convert(char(50),GetDate(),109))+'|Proof Month Set to ' + @Proof_Month 
		+ ' And Batch Id Set to ' + RTrim(Convert(Char(5),@Batch_Id))
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

	Delete From Mthly_Ldgr_Dtl_Upld
	
	Insert Into Mthly_Ldgr_Dtl_Upld (
					  EffectiveDate
					, DatePosted
					, JournalID
					, JournalSeq
					, JournalLine
					, JournalLineDescription
					, JournalDescription
					, LedgerEntity
					, LineLedgerEntity
					, Account
					, Org
					, State
					, ResField1
					, AmtClass
					, DebitCredit
					, CurrCode
					, Amount
	)
		Select 
					  EffectiveDate
					, DatePosted
					, JournalID
					, JournalSeq
					, JournalLine
					, JournalLineDescription
					, JournalDescription
					, LedgerEntity
					, LineLedgerEntity
					, Account
					, Org
					, State
					, ResField1
					, AmtClass
					, DebitCredit
					, CurrCode
					, Amount
		  From Mthly_Ldgr_Dtl_Upld_Temp_1

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 24
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|S|'+RTrim(Convert(char(50),GetDate(),109))+'|Begining sp_ts_Mthly_Ldgr_Dtl_Upld'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Load Completed Successfully'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @cmd = 'MD ' + @FilePath + '\Archive\' + @MoveDate
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @cmd = 'MOVE ' + @FilePath + '\*.* ' + @FilePath + '\Archive\' + @MoveDate
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

Select @System_State = 'EL'

COMMIT TRAN

DECLARE USER_MSG CURSOR READ_ONLY FOR 
	select distinct user_id from user_work_profile(NoLock) 
 		Where Work_Type = 'A'
--		Where User_Id = 'x057117'
OPEN USER_MSG

FETCH NEXT FROM USER_MSG
	INTO	@User_Id

WHILE @@FETCH_STATUS = 0

	BEGIN
		Select @Command_String = 'Exec master..xp_cmdshell "NET SEND ' + @User_Id + ' TaxReconDB Load Completed Successfully", no_output '
		Select @Command_String
		Exec (@Command_String)

		FETCH NEXT FROM USER_MSG
			INTO	@User_Id
	END

CLOSE USER_MSG
DEALLOCATE USER_MSG

Load_Ledger_Init_sp_Exit:

SET @cmd = 'bcp ' + @db_name + '..Tax_Load_Log in d:\Data\Tax\log_out.txt -t"|" -c -S' + @@servername + ' -Utaxuser -Ptaxuser'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''
Select @Command_string 

Exec(@Command_String)

Update  System_Var
	   Set  Load_Ind      = @Load_Ind
--   	       ,System_State  = @System_State
	       ,Load_Message  = RTrim('INIT|')+@Comp_Msg	
	       ,Updated_By    = 'INITLOAD'
	       ,Updated_Ts    = getdate()

SET NOCOUNT OFF

return @Comp_Code

Load_Ledger_Init_sp_Error:
Rollback TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message, @Load_Ind = 'N'

If @Error_Type = 50001
	BEGIN
		Select @error_message = (Select 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
					     + '  @@ERROR: ' + RTRIM(Convert(char(6),error))
					     + ' Severity: ' + RTRIM(Convert(char(3),severity))
					     + '  Message: ' + RTRIM(description)
				  	   From master..sysmessages
				 	  Where error = @error_out)
	END
If @Error_Type = 50002
	BEGIN
		Select @Error_Message = 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
			              + ' Severity:  UserLevel '
				      + ' Message: ' + RTRIM(@Error_Message)
	END



SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Load Completed UnSuccessfully'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|'+RTrim(@Error_Message)+''
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

RAISERROR @Error_Type @Error_Message

GOTO Load_Ledger_Init_sp_Exit
GO
Brett

8-)
Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS
SQLTeam.com Weblog via RSS
- Advertisement -
Resources
