Import--returning the next importid, sequenceNumber. Also incrementing batchNumber by 1 per import group

Question

Import--returning the next importid, sequenceNumber. Also incrementing batchNumber by 1 per import group

dbdeli

Say Hey Kid

Points: 686
More actions
July 8, 2008 at 6:23 pm

#194837

/*
I have an Import process.
I am working with two tables, tblImportConfig and ImportHistory. In addition, I have two Temp tables #LatestImportHistory (last import logged) and #LatestImportConfig which gets the latest import.
tblImportConfig is static; it hold all the imports that need to be executed.
Three columns decide which package should excute next (importid, sequenceNumber, and packageName).
In my query, I left joined tblImportConfig with #latestImportHistory to return the imports that have not been executed as null. If an import has been executed, it will be logged and will easily show in #LatestImportConfig.
Meaning the import will be logged with a StartTime, EndTime, and Status in #latestImportConfig.
I am trying to write a query that returns the next importid, sequenceNumber, and packageName that should be executed.
The business rule is that if an import is successful it should continue with the next associated sequenceNumber; However, if it fails, it should skip all the associated SequenceNumbers, and go to the next importId (group)
I have 3 questions:
1.I need to increment the BatchNumber by 1 (+1) per Import group.
Not per sequenceNumber...per import for example:
BatchNumber ImportID SequenceNumber
100 1 1
100 1 2
100 1 3
101 2 1
101 2 2
101 2 3
102 3 1
102 3 2
2. If an import execution status is success it should continue executing the associated
sequenceNumbers per each import. But if it fails, it should skip to the next import group.
BatchNumber ImportID SequenceNumber Status
100 1 1 Success
100 1 2 Fail
100 1 3 skip
101 2 1 Fail
101 2 2 skip
101 2 3 skip
102 3 1 success
102 3 2 success
102 3 3 success
3. Once all the imports have been executed it should execute all the failed and skipped
packages.
*/
CREATE TABLE [dbo].[ImportHistory](
[ImportLogID] [int] IDENTITY(1,1) NOT NULL,
[ImportID] [int] NOT NULL,
[SequenceNumber] [int] NOT NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Status] [varchar](20) NULL,
[BatchNumber] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblImportConfig](
[importConfigID] [int] NULL,
[ImportID] [int] NULL,
[SequenceNumber] [int] NULL,
[PackageName] [varchar](30) NULL,
[active flag] [int] NULL
) ON [PRIMARY]
Go
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(1,1,'pkgMaster.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(1,2,'pkg1.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(1,3,'pkg2.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(2,1,'pkg3.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(2,2,'pkg4.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(2,3,'pkg5.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(3,1,'pkg6.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(3,2,'pkg7dtsx',1)
insert into dbo.ImportHistory(ImportID, SequenceNumber, Start_Time, End_Time, [Status], [BatchNumber])
values(1,1,GetDate(),GetDate(),'Success', '100')
insert into dbo.ImportHistory(ImportID, SequenceNumber, Start_Time, End_Time, [Status], [BatchNumber])
values(1,2,GetDate(),GetDate(),'success', '100')
insert into dbo.ImportHistory(ImportID, SequenceNumber, Start_Time, End_Time, [Status], [BatchNumber])
drop table #LatestImportHistory, #LatestImportConfig
Create Table #LatestImportHistory (
ImportID INT,
SequenceNumber INT,
ImportLogID INT,
Status varchar(25) )
Create Table #LatestImportConfig (
ImportConfigID INT,
BatchNumber INT,
ImportID INT,
SequenceNumber INT,
PackageName VARCHAR(100),
ActiveFlag INT,
StartTime DATETIME,
EndTime DATETIME,
[Status] VARCHAR(25) )
--the last import logged
INSERT INTO #LatestImportHistory
SELECT
ImportID,
SequenceNumber,
MAX(ImportLogID) ImportLogID,
Status
FROM dbo.ImportHistory
GROUP BY ImportID, SequenceNumber, status
ORDER BY ImportID, SequenceNumber, status
--
INSERT INTO #LatestImportConfig
SELECT
IC.ImportConfigID,
IH.BatchNumber,
IC.ImportID,
IC.SequenceNumber,
IC.PackageName,
IC.[Active Flag],
IH.Start_Time,
IH.End_Time,
IH.Status
FROM dbo.tblImportConfig IC
LEFT JOIN #LatestImportHistory LIH
ON LIH.ImportID = IC.ImportID
AND LIH.SequenceNumber = IC.SequenceNumber
LEFT JOIN dbo.ImportHistory IH
ON IH.ImportLogID = LIH.ImportLogID
order by IC.ImportID, IC.SequenceNumber
--here if ImportHistory is empty it will return the min(importid) and min(SequenceNumber)
IF not Exists(Select * from #LatestImportConfig where [Status] like '%fail%' or Status like '%success%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig
WHERE ActiveFlag = 1
AND EndTime is null
AND StartTime is null
AND Status is null
AND ImportID in (
select
ImportID
from #LatestImportConfig)
End
--Else
If Not Exists (Select * from #LatestImportConfig where Status like '%fail%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig
WHERE ActiveFlag = 1
AND EndTime is null
AND StartTime is null
AND Status is null
AND ImportID in(select ImportID from #LatestImportConfig where EndTime
in (select max(EndTime) from #LatestImportConfig where status is null
or Status like '%success%'
and status not like '%fail%'
)
)
AND StartTime is null
AND EndTime IS NULL
AND Status is null
AND ActiveFlag = 1
End
Else
--select * from #LatestImportConfig
--here I am stuck...really stuck...
--it should skip import return the next import if there is a specific fail for an import
If Exists (Select * from #LatestImportConfig where Status like '%fail%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig lic
Where ImportID in(select ImportID from #LatestImportConfig where status not like '%fail%'
and EndTime in (select max(EndTime) from #LatestImportConfig where status is null
or Status like '%success%' and status not like '%fail%'
)
)
and lic.importid not in (select importid from #LatestImportHistory where status like '%fail%' or status like '%success%')
--and not exists (select * from #LatestImportHistory where status like '%fail%' or status like '%success%')
and endtime is null
and starttime is null
and status is null
End
SELECT * FROM #LatestImportConfig
select * from #LatestImportHistory
--to begin the import again:
If not Exists (Select * from #LatestImportConfig where Status like '%fail%' and status is not null)
Begin
SELECT
min(lic.ImportID),
min(lic.SequenceNumber)
FROM #LatestImportConfig lic
WHERE (
Not Exists
(
Select * from #LatestImportHistory lih
Where lic.importid = lih.ImportID and lic.SequenceNumber = lih.SequenceNumber
)
or
Exists
(
Select * from #LatestImportHistory lih
Where lic.importid = lih.importid and lih.status like '%success%'
)
)
AND ActiveFlag = 1
End
Select * from #LatestImportHistory

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

raja_saminathan SSCommitted Points: 1607 More actions · Answer 1

Hi,

Can you put your expected Results for the given sample datas at the end of each conditional select Statement for the given sample datas

Rajesh

dbdeli Say Hey Kid Points: 686 More actions · Answer 2

If you create the two tables I included and insert the two rows and then run the script you should be getting this data. I am trying to write a query that meats all the conditions. Meaning every time a row is inserted into importHistory. It should know which importId, SequenceNumber should run next...also, I want to assign a batchNumber to each import group...incrementing by 1. thanks

Here it is result set:

Select * from #latestImportConfig

importConfigIDBatchNumImportIdSequenceNPackageNameActive FlagStartTimeEndTimeStatus

1100 11MasterPackage.dtsx130:01.230:01.2Success

2100 12pkgAssignCompanies.dtsx130:05.330:05.3success

3NULL 13pkgAssignServices.dtsx1NULLNULLNULL

4NULL 14pkgAssignServiceDate.dtsx1NULLNULLNULL

5NULL 21 pkgAssignLostCompanies.dtsx1NULLNULLNULL

6NULL 22pkgAssignHomework.dtsx1NULLNULLNULL

7NULL 23pkgAssignWork.dtsx1NULLNULLNULL

8NULL 24pkgAssignDate.dtsx1NULLNULLNULL

9NULL 31pkgAssignThree1 1NULLNULLNULL

10NULL 32pkgAssignThree2 1NULLNULLNULL

11NULL 32pkgAssignThree3 1NULLNULLNULL

12NULL 33pkg7dtsx 1NULLNULLNULL

Select * from #LatestImportHistory

ImportidSequenceNumberImportConfigIDstatus

1 1 1 Success

1 2 2 success

--here if ImportHistory is empty it will return the min(importid) and min(SequenceNumber)

IF not Exists(Select * from #LatestImportConfig where [Status] like '%fail%' or Status like '%success%')

Begin

SELECT

min(ImportID),

min(SequenceNumber)

FROM #LatestImportConfig

WHERE ActiveFlag = 1

AND EndTime is null

AND StartTime is null

AND Status is null

AND ImportID in (

select

ImportID

from #LatestImportConfig)

End

---Notice that I have the importid and SequenceNumber...I want to generate a batchnumber per import group as well

ImportIdSequeceNumber

11

--Else

If Not Exists (Select * from #LatestImportConfig where Status like '%fail%')

Begin

SELECT

min(ImportID),

min(SequenceNumber)

FROM #LatestImportConfig

WHERE ActiveFlag = 1

AND EndTime is null

AND StartTime is null

AND Status is null

AND ImportID in (select ImportID from #LatestImportConfig where EndTime

in (select max(EndTime) from #LatestImportConfig where status is null

or Status like '%success%'

and status not like '%fail%'

)

AND StartTime is null

AND EndTime IS NULL

AND Status is null

AND ActiveFlag = 1

End

--result set---

ImportidSequcneNumber

13

Else

--select * from #LatestImportConfig

--here I am stuck...really stuck...

--it should skip import return the next import if there is a specific fail for an import

If Exists (Select * from #LatestImportConfig where Status like '%fail%')

Begin

SELECT

min(ImportID),

min(SequenceNumber)

FROM #LatestImportConfig lic

Where ImportID in(select ImportID from #LatestImportConfig where status not like '%fail%'

and EndTime in (select max(EndTime) from #LatestImportConfig where status is null

or Status like '%success%' and status not like '%fail%'

)

and lic.importid not in (select importid from #LatestImportHistory where status like '%fail%' or status like '%success%')

-- and not exists (select * from #LatestImportHistory where status like '%fail%' or status like '%success%')

and endtime is null

and starttime is null

and status is null

End

importidSequenceNumber

nullnull

---result set

SELECT * FROM #LatestImportConfig

select * from #LatestImportHistory

--to begin the import again:

If not Exists (Select * from #LatestImportConfig where Status like '%fail%' and status is not null)

Begin

SELECT

min(lic.ImportID),

min(lic.SequenceNumber)

FROM #LatestImportConfig lic

WHERE (

Not Exists

(

Select * from #LatestImportHistory lih

Where lic.importid = lih.ImportID and lic.SequenceNumber = lih.SequenceNumber

)

or

Exists

(

Select * from #LatestImportHistory lih

Where lic.importid = lih.importid and lih.status like '%success%'

)

AND ActiveFlag = 1

End

Select * from #LatestImportHistory