I need to create a package that will monitor a table in a source system and when a flag is set, load data from other tables in this source system to my destination system. Today this is accomplished with a SQL Agent job that executes every 15 minutes. If there is no work to do the job simply exits. I would like to create a SQLIS package that checks this control table every 30 seconds. Can I create a package that runs continuously?
View 16 RepliesI have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.
It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).
Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?
Here is the very long procedure:
CREATE PROCEDURE [dbo].[ISP_RPT_RENEWAL_RETENTION_TEST]
(
@FROM DATETIME,
@TO DATETIME,
@ASOF DATETIME,
@REPORT VARCHAR(10) = 'ADHOC')
AS
SET NOCOUNT ON
--DECLARE @FROM DATETIME
--DECLARE @TO DATETIME
--DECLARE @ASOF DATETIME
--DECLARE @REPORT VARCHAR(10)
--
--SET @REPORT = 'YTD'
--SET @FROM = '1/1/2006'
--SET @TO = '10/31/2006'
--SET @ASOF = '10/31/2006'
IF (@REPORT = 'ADHOC' OR @REPORT IS NULL OR @REPORT = '')
BEGIN
--Check to see if user has input data
--First of the previous month
IF (@FROM IS NULL OR @FROM = '') SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1
IF (@TO IS NULL OR @TO = '') SET @TO = dbo.PreviousMonthEndDate(GetDate())
IF (@ASOF IS NULL OR @ASOF = '') SET @ASOF = dbo.PreviousMonthEndDate(GetDate())
END
ELSE IF @REPORT = 'MTD'
BEGIN
--Disregard user input and run for MTD
--First of the previous month
SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1
SET @TO = dbo.PreviousMonthEndDate(GetDate())
SET @ASOF = dbo.PreviousMonthEndDate(GetDate())
END
ELSE IF @REPORT = 'YTD'
BEGIN
--Disregard user input and run for YTD
SET @FROM = dbo.FirstOfTheYear(GetDate())
SET @TO = dbo.PreviousMonthEndDate(GetDate())
SET @ASOF = dbo.PreviousMonthEndDate(GetDate())
END
SET @FROM = dbo.MidnightDate(@FROM)
SET @TO = dbo.MidnightDate(@TO)
SET @ASOF = dbo.MidnightDate(@ASOF)
/*** if user runs report before month end is closed, then use data from premium table
if however user runs report after month end close, then pull from reinsurance table ***/
DECLARE @CHECKDATE DATETIME
SELECT @CHECKDATE = MAX(MONTHENDDATE) FROM MPATREINSURANCE
WHERE MONTHENDDATE = @TO
/*********************************************** IDENTIFY ALL RENEWALS ***************************************************/
SELECT DISTINCT
RC.POLICYNO POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,
MCITCUST.FULLNAME ,MAX(ISNULL(RC1.POLICYNO,RC.PREVIOUSPOLICYNO)) PREVIOUSPOLICYNO ,RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS PMRSTATUS,V.COVERAGE
INTO #MPAV_RENEWALS
FROM MPATPMRC RC
INNER JOIN MCITCUST ON
RC.COMPANYID = MCITCUST.COMPANYID AND
RC.INSUREDNAMENO = MCITCUST.NAMENO
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V.POLICYID = RC.POLICYID AND
V.PMRSEQUENCE = RC.PMRSEQUENCE AND
V.SUBCOMPANYID = RC.SUBCOMPANYID
INNER JOIN MCITCUST M1 ON
MCITCUST.COMPANYID =M1.COMPANYID AND
MCITCUST.CURRENTNAMENO = M1.CURRENTNAMENO
INNER JOIN MPATPMRC RC1 ON
RC1.COMPANYID = M1.COMPANYID AND
RC1.INSUREDNAMENO = M1.NAMENO
LEFT OUTER JOIN MPATCGLUWVERSIONS V1 ON
V1.POLICYID = RC1.POLICYID AND
V1.PMRSEQUENCE = RC1.PMRSEQUENCE AND
V1.SUBCOMPANYID = RC1.SUBCOMPANYID
WHERE
RC.POLICYTYPE ='CGL' AND
RC.POLICYEFFDATE >= '1/1/1998' AND
--FIND THE VERY FIRST VALID TRANSACTION BY LOOKING AT WHICH ONE OF RN/NB/RW IS THE LATEST
RC.TXNISSUED <= @ASOF AND
RC1.TXNISSUED <= @ASOF AND
RC.PMRSEQUENCE = (SELECT MAX(PMRSEQUENCE) FROM MPATPMRC RC2
WHERE RC2.POLICYID = RC.POLICYID AND
RC2.SUBCOMPANYID = RC.SUBCOMPANYID AND
RC2.POLICYYEAR = RC.POLICYYEAR AND
RC2.TXNISSUED <= @ASOF AND
RC2.TXNTYPE IN ('RN','NB','RW','RE') AND
( (RC2.POLICYNO = RC.POLICYNO AND RC.POLICYNO IS NOT NULL)
OR (RC.POLICYNO IS NULL)
) AND
RC2.PMRSTATUS IN ('HS', 'EX', 'IN','PC','PA')AND
RC2.PROCESSINGSTATUS IN ('*','P','A','I','Q')
)
AND ((RC1.TXNTYPE ='CN' AND RC1.CANCELMETHOD <> 'F') OR (RC1.CANCELMETHOD IS NULL OR RC1.TXNTYPE <> 'CN'))
AND RC1.PMRSEQUENCE = (
SELECT MAX(PMRSEQUENCE) FROM MPATPMRC
WHERE MPATPMRC.POLICYID = RC1.POLICYID AND
MPATPMRC.POLICYNO = RC1.POLICYNO AND
MPATPMRC.TXNISSUED <= @ASOF AND
MPATPMRC.PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND
MPATPMRC.PROCESSINGSTATUS IN ('*','C')AND
MPATPMRC.TXNTYPE NOT IN ('EA','ER' )) AND
(
(RC1.TXNTYPE <>'CN' AND RC1.POLICYEXPDATE = RC.POLICYEFFDATE ) OR
(RC1.TXNTYPE ='CN' AND RC1.TXNEFFECTIVE = RC.POLICYEFFDATE )
)
AND (V.COVERAGE = 'B' AND V1.COVERAGE = 'P' OR V.COVERAGE = 'P' AND V1.COVERAGE = 'B' OR
V.COVERAGE = V1.COVERAGE)
GROUP BY RC.POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,
MCITCUST.FULLNAME , RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS ,V.COVERAGE
/**************************************FIND LAST VERSION*********************************/
SELECT
RC.POLICYNO,
RC.POLICYYEAR,
RC.POLICYID,
RC.SUBCOMPANYID,
RC.PMRSEQUENCE,
RC.TXNTYPE,
POLICYEXPDATE = CASE WHEN RC.TXNTYPE = 'CN' THEN RC.TXNEFFECTIVE ELSE RC.POLICYEXPDATE END,
REPORTDATES =CASE
WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE <= RC.TXNISSUED THEN RC.TXNISSUED
WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE > RC.TXNISSUED THEN RC.TXNEFFECTIVE
ELSE RC.POLICYEXPDATE END,
CN_REASON = CASE WHEN RC.TXNTYPE = 'CN' THEN
(CASE WHEN RC.TXNSUBTYPE IN ('PF','NP') THEN 'Non-pay'
WHEN RC.TXNSUBTYPE IN ('IN') THEN 'Ins Request'
WHEN RC.TXNSUBTYPE IN ('UW') THEN 'UW Reasons' ELSE 'Other' END)
Else '' END,
CN_METHOD = CASE WHEN RC.TXNTYPE = 'CN' THEN CANCELMETHOD ELSE '' END,
INS.FULLNAME INSURED,
RC.INSUREDNAMENO,
INS.CURRENTNAMENO,
RC.STATE,
RC.ORIGINCEPTIONDATE
INTO #LAST_VERSION
FROM MPATPMRC RC
INNER JOIN --LOOKING UP THE LATEST VERSION OF POLICIES
(SELECT DISTINCT POLICYNO, POLICYID, SUBCOMPANYID, MAX( PMRSEQUENCE) LASTPMR
FROM MPATPMRC
WHERE
POLICYTYPE ='CGL' AND
PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND
PROCESSINGSTATUS IN ('*','C') AND
TXNTYPE NOT IN ('EA','ER')
AND MPATPMRC.TXNISSUED <= @ASOF
GROUP BY POLICYNO, POLICYID, SUBCOMPANYID) LV ON
RC.POLICYNO = LV.POLICYNO AND
RC.SUBCOMPANYID = LV.SUBCOMPANYID AND
RC.POLICYID = LV.POLICYID AND
RC.PMRSEQUENCE = LV.LASTPMR
LEFT OUTER JOIN MCITCUST INS ON
RC.INSUREDNAMENO = INS.NAMENO
/*=============================================IDENTIFY RENEWALS FOR THE PERIOD==============================*/
SELECT R.POLICYNO,
R.CURRENTNAMENO,
R.POLICYID,
R.PMRSEQUENCE,
R.SUBCOMPANYID,
R.POLICYEFFDATE,
R.FULLNAME,
R.PREVIOUSPOLICYNO,
R.INSUREDNAMENO,
R.PROCESSINGSTATUS,
R.PMRSTATUS,
R.COVERAGE
INTO #RENEWALS
FROM #MPAV_RENEWALS R
LEFT OUTER JOIN #LAST_VERSION LV ON
R.POLICYNO = LV.POLICYNO
--take off pendings
WHERE LV.CN_METHOD <> 'F' AND
POLICYEFFDATE BETWEEN @FROM AND @TO
/* LETS GET RENEWAL BROKER */
SELECT
R.POLICYNO,
P.NAMENO,
C.FULLNAME,
A.CITY
INTO #RN_BRK
FROM #RENEWALS R
INNER JOIN MPATPRODUCERS P ON
R.POLICYID = P.POLICYID AND
R.PMRSEQUENCE = P.PMRSEQUENCE AND
R.SUBCOMPANYID = P.SUBCOMPANYID AND
PAYOR = 1
INNER JOIN MCITCUST C ON
P.NAMENO = C.NAMENO
INNER JOIN MCITADDR A ON
C.MAILINGADDRESS = A.ADDRESSID
/*==============================================GET RENEWING PREMIUMS=========================================*/
--this gives us written premium as of report date
SELECT DISTINCT
R.POLICYNO,
R.POLICYID,
MAX(R.SUBCOMPANYID) SUB,
MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,
MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,
MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,
MAX(MPATPMRC.UNDERWRITER)UW,
RN_DIRECT = SUM(WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),
RN_CEDED_REIN = SUM(WRITTENPREM3),
CEDED_RETAINED = 0
INTO #RN_PREM
FROM #RENEWALS R
LEFT OUTER JOIN MPATPMRC MPATPMRC ON
MPATPMRC.POLICYNO = R.POLICYNO
LEFT OUTER JOIN MPATPREMIUMS MPATPREMIUMS ON
MPATPMRC.COMPANYID = MPATPREMIUMS.COMPANYID AND
MPATPMRC.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND
MPATPMRC.POLICYID = MPATPREMIUMS.POLICYID AND
MPATPMRC.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID
WHERE @CHECKDATE IS NULL AND
MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND
((MPATPMRC.TXNISSUED >= MPATPMRC.TXNEFFECTIVE AND MPATPMRC.TXNISSUED BETWEEN @FROM AND @TO) OR
(MPATPMRC.TXNEFFECTIVE >= MPATPMRC.TXNISSUED AND MPATPMRC.TXNEFFECTIVE BETWEEN @FROM AND @TO))
GROUP BY R.POLICYNO , R .POLICYID
UNION ALL
--this gives us written premium as of report date
SELECT DISTINCT
R.POLICYNO,
R.POLICYID,
MAX(R.SUBCOMPANYID) SUB,
MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,
MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,
MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,
MAX(MPATPMRC.UNDERWRITER) UW,
RN_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,
RN_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,
CEDED_RETAINED= SUM(
(CASE WHEN CEDEDPERCENT1 <> 0 THEN
((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT2 <> 0 THEN
((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT3 <> 0 THEN
((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)
)
FROM #RENEWALS R
LEFT OUTER JOIN MPATPMRC MPATPMRC ON
MPATPMRC.POLICYNO = R.POLICYNO
LEFT OUTER JOIN MPATREINSURANCE MPATREINSURANCE ON
MPATPMRC.COMPANYID = MPATREINSURANCE.COMPANYID AND
MPATPMRC.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND
MPATPMRC.POLICYID = MPATREINSURANCE.POLICYID AND
MPATPMRC.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID AND
MPATPMRC.TREATYID = MPATREINSURANCE.TREATYID
WHERE @CHECKDATE IS NOT NULL AND
MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND
((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED BETWEEN @FROM AND @TO) OR
(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE BETWEEN @FROM AND @TO))
GROUP BY R.POLICYNO , R .POLICYID
/*======================= looks for all policies EXPIRED during report time==========================*/
SELECT
LV.POLICYYEAR,
LV.POLICYNO,
LV.POLICYID,
LV.SUBCOMPANYID,
LV.PMRSEQUENCE,
LV.TXNTYPE,
LV.POLICYEXPDATE,
REASONDROPPED =
CASE WHEN LV.TXNTYPE = 'CN' AND CN_REASON = 'Non-pay' AND R.DESCRIPTION IS NULL
THEN 'Cancelled for non pay'
ELSE
ISNULL(ISNULL(R.DESCRIPTION, CLOSEREASONDESC),'No reason entered') END,
CN_METHOD,
V.COVERAGE,
CURRENTNAMENO,
INSUREDNAMENO,
INSURED,
LV.STATE,
ORIGINCEPTIONDATE,
CLOSECATEGORYDESC,
CLOSEREASONDESC,
COMPETITORDESC,
MKTFOLLOWUPDESC
INTO #EXPIRED
FROM #LAST_VERSION LV
LEFT OUTER JOIN MCITINSURED ON
LV.INSUREDNAMENO = MCITINSURED.NAMENO
LEFT OUTER JOIN MSOTDROPREAS R ON
MCITINSURED.REASONDROPPED = R.REASONID
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V.POLICYID = LV.POLICYID AND
V.PMRSEQUENCE = LV.PMRSEQUENCE AND
V.SUBCOMPANYID = LV.SUBCOMPANYID
LEFT OUTER JOIN MSOTCLOSECATEGORIES CC ON
MCITINSURED.CLOSECATEGORYID = CC.CLOSECATEGORYID
LEFT OUTER JOIN MSOTCLOSEREASONS CR ON
MCITINSURED.CLOSEREASONID = CR.CLOSEREASONID
LEFT OUTER JOIN MSOTCOMPETITORS CO ON
MCITINSURED.COMPETITORID = CO.COMPETITORID
LEFT OUTER JOIN MSOTMKTFOLLOWUP MK ON
MCITINSURED.MKTFOLLOWUPID = MK.MKTFOLLOWUPID
WHERE REPORTDATES BETWEEN CAST(CAST(@FROM AS VARCHAR(11)) AS DATETIME) --DF CHANGE
AND CAST(CAST(@TO AS VARCHAR(11)) AS DATETIME)
/*==============================================GET EXPIRED POLICY INFO=========================================*/
--GET INFO FOR ALL TXNS SO U ALSO GET THE PREMIUMS
SELECT DISTINCT
MAX(MCITCUST.NAMENO) NAMENO,
R.RISKSEQUENCE,
MAX(MCITCUST.CURRENTNAMENO) CURRENTNAMENO,
MAX(MCITCUST.FULLNAME) FULLNAME,
RC.POLICYNO,
RC.POLICYID,
MAX(RC.SUBCOMPANYID) SUB,
MAX(RC.POLICYYEAR) POLICYYEAR,
EXPDATE = MAX(EX.POLICYEXPDATE),
SALES = SUM(CASE WHEN PR.COVERAGETYPE ='P' AND PR.PMRSEQUENCE = EX.PMRSEQUENCE THEN ISNULL(PR.SALES,0) ELSE 0 END),
MAX(RC.UNDERWRITER) UW,
P.HAZARDCLASS,
P.CLASSCODE PRODUCTCODE,
MAX(P.DESCRIPTION) [DESCRIPTION],
DISTRIBUTOR = (CASE WHEN P.DISTRIBUTOR = 1 THEN 'YES' ELSE 'NO' END),--R.PRODUCTCODE
MEMBER = MAX(CASE WHEN MEMBER = 1 THEN 'DEVICE' ELSE 'EXP' END),
SUM(WRITTENPREM1 + WRITTENPREM2) LAYER1_2,
EX_LIMIT = MAX(CASE WHEN V.COV_PROD_AGGREGATE = '1000000' OR V.COV_CGL_AGGREGATE = '1000000' THEN 1
WHEN V.COV_PROD_AGGREGATE = '2000000' OR V.COV_CGL_AGGREGATE = '2000000' THEN 2
WHEN V.COV_PROD_AGGREGATE = '3000000' OR V.COV_CGL_AGGREGATE = '3000000' THEN 3
WHEN V.COV_PROD_AGGREGATE = '4000000' OR V.COV_CGL_AGGREGATE = '4000000' THEN 4
WHEN V.COV_PROD_AGGREGATE = '5000000' OR V.COV_CGL_AGGREGATE = '5000000' THEN 5
WHEN V.COV_PROD_AGGREGATE = '6000000' OR V.COV_CGL_AGGREGATE = '6000000' THEN 6
WHEN V.COV_PROD_AGGREGATE = '7000000' OR V.COV_CGL_AGGREGATE = '7000000' THEN 7
WHEN V.COV_PROD_AGGREGATE = '8000000' OR V.COV_CGL_AGGREGATE = '8000000' THEN 8
WHEN V.COV_PROD_AGGREGATE = '9000000' OR V.COV_CGL_AGGREGATE = '9000000' THEN 9
WHEN V.COV_PROD_AGGREGATE = '10000000' OR V.COV_CGL_AGGREGATE = '10000000' THEN 10 END),
EX_BROKER_GROUP =
(SELECT [DESCRIPTION] FROM MSOTCOMMPLAN
WHERE
SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND
COMMISSIONPLAN =
(SELECT TOP 1 COMMISSIONPLAN FROM MAGTCOMMPLAN
WHERE NAMENO = MPATPRODUCERS.NAMENO AND SUBCOMPANYID =MPATPRODUCERS.SUBCOMPANYID AND
COMMRATEEFFDATE <= EX.POLICYEXPDATE
ORDER BY COMMRATEEFFDATE DESC )),
MAX(BROKER.FULLNAME) BRK_NAME,
MAX(MPATPRODUCERS.COMMISSIONRATE * 100) RATE,
MAX(AA.CITY) BRK_CITY
INTO #EX
FROM MPATPMRC RC
INNER JOIN MPATPREMIUMS PR ON
RC.POLICYID = PR.POLICYID AND
RC.SUBCOMPANYID = PR.SUBCOMPANYID AND
RC.PMRSEQUENCE = PR.PMRSEQUENCE
INNER JOIN #EXPIRED EX ON
EX.POLICYNO = RC.POLICYNO
INNER JOIN MCITCUST ON
RC.COMPANYID = MCITCUST.COMPANYID AND
RC.INSUREDNAMENO = MCITCUST.NAMENO
LEFT OUTER JOIN MPATCGLUWRISKS R ON
PR.RISKSEQUENCE = R.RISKSEQUENCE AND
EX.POLICYID = R.POLICYID AND
EX.PMRSEQUENCE = R.PMRSEQUENCE AND
EX.SUBCOMPANYID = R.SUBCOMPANYID
LEFT OUTER JOIN MSOTPRODUCTS P ON
R.PRODUCTCODE = P.PRODUCTCODE
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V .POLICYID = EX.POLICYID AND
V .PMRSEQUENCE =EX.PMRSEQUENCE AND
V .SUBCOMPANYID = EX.SUBCOMPANYID
INNER JOIN MPATPRODUCERS ON
EX.SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND
EX.POLICYID = MPATPRODUCERS.POLICYID AND
EX.PMRSEQUENCE = MPATPRODUCERS.PMRSEQUENCE AND
MPATPRODUCERS.PAYOR = 1
INNER JOIN MCITCUST BROKER ON
MPATPRODUCERS.COMPANYID = BROKER.COMPANYID AND
MPATPRODUCERS.NAMENO = BROKER.NAMENO
LEFT OUTER JOIN MCITADDR AA ON
BROKER.MAILINGADDRESS = AA.ADDRESSID
WHERE RC.PMRSTATUS IN ('IN','EX','HS','CN') AND
RC.PROCESSINGSTATUS IN ('*','C')
GROUP BY R.RISKSEQUENCE ,RC.POLICYNO, RC.POLICYID,P.HAZARDCLASS, P.CLASSCODE,
MPATPRODUCERS.SUBCOMPANYID, MPATPRODUCERS.NAMENO, EX.POLICYEXPDATE,P.DISTRIBUTOR
ORDER BY RC.POLICYNO
/*****************************************************************************************************************/
SELECT
R.POLICYNO,
EX_DIRECT = SUM( WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),
EX_CEDED_REIN = SUM(WRITTENPREM3),
EX_CEDED_RETAINED= 0
INTO #EX_PREM
FROM MPATPMRC R
INNER JOIN #EXPIRED E ON
R.POLICYNO = E.POLICYNO
LEFT OUTER JOIN MPATPREMIUMS ON
R.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND
R.POLICYID = MPATPREMIUMS.POLICYID AND
R.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID
WHERE @CHECKDATE IS NULL AND
((R.TXNISSUED >= R.TXNEFFECTIVE AND R.TXNISSUED <= @TO) OR
(R.TXNEFFECTIVE >= R.TXNISSUED AND R.TXNEFFECTIVE <= @TO))
GROUP BY R.POLICYNO
UNION ALL
SELECT
R.POLICYNO,
EX_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,
EX_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,
EX_CEDED_RETAINED=
SUM(
(CASE WHEN CEDEDPERCENT1 <> 0 THEN
((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT2 <> 0 THEN
((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT3 <> 0 THEN
((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)
)
FROM MPATPMRC R
INNER JOIN #EXPIRED E ON
R.POLICYNO = E.POLICYNO
LEFT OUTER JOIN MPATREINSURANCE ON
R.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND
R.POLICYID = MPATREINSURANCE.POLICYID AND
R.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID
WHERE @CHECKDATE IS NOT NULL AND
((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED <= @TO) OR
(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE <= @TO))
GROUP BY R.POLICYNO
/******************************************************************************************************************/
EXECUTE ISP_RPT_MKTSEGMENT
SET NOCOUNT OFF
SELECT
E.POLICYNO EXP_POLICY,
E.POLICYEXPDATE,
A.UW,
E.INSURED INS_NAME,
STATE INS_STATE,
BROKER,
BROKER_GROUP = CASE WHEN BROKER_GRP LIKE '%Others%' THEN 'Others'
WHEN BROKER_GRP LIKE '%Preferred%' THEN 'Preferred'
WHEN BROKER_GRP LIKE '%Marsh%' THEN 'Marsh'
WHEN BROKER_GRP LIKE '%Champion%' THEN 'Champion'
ELSE 'Others' END, COMM_RATE,
DOMINANT_PROD DOM_PROD,
DOMINANT_PROD_DESC DESCR,
MK.DISTRIBUTOR,
DOMINANT_HAZ DOM_HAZD,
EX_LIMIT,
MEMBER,
A.SALES,
MKT_SEGMENT,
ISNULL(EX_DIRECT,0) TOTAL_WRTTN,
(ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0)) TOTAL_CEDED,
CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_REIN,0) END ,
CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_RETAINED,0) END ,
NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ((ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0))) END,
XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ISNULL(EX_CEDED_REIN,0) END,
REASON_LOST =
CASE WHEN R.POLICYNO IS NULL THEN
(SELECT 'LOST -'+REASONDROPPED FROM #EXPIRED WHERE POLICYNO = E.POLICYNO)
ELSE '' END,
ISNULL(R.POLICYNO, '') RN_POLICY,
ISNULL(RN_DIRECT,0) RN_TOTAL_WRTTN,
(ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0)) RN_TOTAL_CEDED,
RN_CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_CEDED_REIN,0) END ,
RN_CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(CEDED_RETAINED,0) END ,
RN_NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ((ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0))) END,
RN_XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ISNULL(RN_CEDED_REIN,0) END,
[INSURED INFORCE TODAY?] = CASE WHEN (SELECT COUNT(*)
FROM MPATPMRC
WHERE PMRSTATUS ='IN' AND
INSUREDNAMENO IN
(SELECT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)
)>0 THEN 'YES' ELSE 'NO' END,
[FIRST_JOINED_DATE] =
ISNULL(
(SELECT MIN(DATEJOINED) FROM MCITINSURED
WHERE NAMENO IN
(SELECT DISTINCT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)), ORIGINCEPTIONDATE),
[# OF POLICY YEARS] = (
SELECT COUNT(DISTINCT POLICYYEAR)
FROM MPATPMRC
WHERE PMRSTATUS IN ('EX','HS','IN') AND TXNTYPE IN ('CV','NB','RN','RW') AND PROCESSINGSTATUS ='*'
AND POLICYNO IS NOT NULL AND
INSUREDNAMENO IN (SELECT DISTINCT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)),
MONTHS = (CASE WHEN MONTH(E.POLICYEXPDATE) = 1 THEN 'January'
WHEN MONTH(E.POLICYEXPDATE) = 2 THEN 'February'
WHEN MONTH(E.POLICYEXPDATE) = 3 THEN 'March'
WHEN MONTH(E.POLICYEXPDATE) = 4 THEN 'April'
WHEN MONTH(E.POLICYEXPDATE) = 5 THEN 'May'
WHEN MONTH(E.POLICYEXPDATE) = 6 THEN 'June'
WHEN MONTH(E.POLICYEXPDATE) = 7 THEN 'July'
WHEN MONTH(E.POLICYEXPDATE) = 8 THEN 'August'
WHEN MONTH(E.POLICYEXPDATE) = 9 THEN 'September'
WHEN MONTH(E.POLICYEXPDATE) = 10 THEN 'October'
WHEN MONTH(E.POLICYEXPDATE) = 11 THEN 'November'
WHEN MONTH(E.POLICYEXPDATE) = 12 THEN 'December' END),
BRK_CITY,
RB.FULLNAME RN_BROKER,
RB.CITY RN_BRK_CITY,
ORIG_EXP =
(SELECT MIN(POLICYEXPDATE) POLICYEXPDATE
FROM
MPATPMRC
WHERE POLICYNO = E.POLICYNO AND TXNTYPE IN ('NB','RN','RW')) ,
DROP_CATEGORY = CASE WHEN R.POLICYNO IS NULL THEN CLOSECATEGORYDESC ELSE '' END ,
DROP_REASON = CASE WHEN R.POLICYNO IS NULL THEN CLOSEREASONDESC ELSE '' END ,
COMPETITOR = CASE WHEN R.POLICYNO IS NULL THEN COMPETITORDESC ELSE '' END ,
MKTFOLLOWUP = CASE WHEN R.POLICYNO IS NULL THEN MKTFOLLOWUPDESC ELSE '' END,
MKT_SEGMENTSORT = MS.SORTORDER
FROM #EXPIRED E
LEFT OUTER JOIN #RENEWALS R ON
E.POLICYNO = R.PREVIOUSPOLICYNO
LEFT OUTER JOIN
(SELECT POLICYNO, SUM(SALES)SALES, MAX(UW) UW, SUM(LAYER1_2) PREM_2MIL, MAX(EX_LIMIT) EX_LIMIT,
MAX(BRK_NAME) BROKER, MAX(EX_BROKER_GROUP) BROKER_GRP, MAX(RATE) COMM_RATE, MAX(BRK_CITY) BRK_CITY
FROM #EX
GROUP BY POLICYNO ) A ON
E.POLICYNO = A.POLICYNO
LEFT OUTER JOIN #EX_PREM EP ON
E.POLICYNO = EP.POLICYNO
LEFT OUTER JOIN #RN_PREM RP ON
R.POLICYNO = RP.POLICYNO
LEFT OUTER JOIN MPAT_MKTSEGMENTS MK ON
A.POLICYNO = MK.POLICYNO
LEFT OUTER JOIN MSOTMARKETSEGMENT MS ON
MK.MKT_SEGMENT = MS.MARKETSEGMENT
LEFT OUTER JOIN #RN_BRK RB ON
R.POLICYNO = RB.POLICYNO
WHERE ISNULL(E.CN_METHOD,'X') <> 'F'
ORDER BY R.POLICYNO
Hi All,
I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.
Sometimes when I am creating a package I get the following error in a message box:
SQL Server Import and Export Wizard
The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.
Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)
Other times when I run a package that has run successfully before I get the following error:
Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.
Other times I get this error message:
.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)
And still other times
The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.
I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.
Does anyone have any other suggestions to try?
Thanks.
Hi there
We have a SSIS run which runs as follows
The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1:
The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €ścannot connect to database xxx€?
Info - €śpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €śtimeout when connect to database xxx€?
Info - €śpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
Thanx,
/Nils M - Copenhagen
Hi all,
I am creating a dts package to export files from one database to another database.
I tried to search for ways to execute the files and found out that i need to add
reference to Microsoft.Sql.managedDts. However, I cannot find this reference from
my reference. Do i have other alternatives to run this file?
I am receiving an error on my master package that executes a number of other packages. The individual packages work fine when executed by themselves. However, I am getting the following error when I attempt to execute it from another package:
Error: Failed to acquire connection "conneciton". Connection may not be configured correctly or you may not have the right permissions on this connection.
Thanks in advance for your help.
I am using the DTS wizard to import tables from an AS/400. Some of the tables in the job are quite large and Sql Server either hangs up or completely crashes. My current configuration is WIN NT, SP4.0 SQL Server 7.0 no Service pack. Any suggestions?
View 1 Replies View RelatedI have small db and load is very less. I am set full backup at  9:00 pm once a day,and set  transaction log backup to every 15 min.then i am taking the transaction log backup in 9:15,9:30,9:45....... My question is  I lost my data between 9:15 to 9:30 . in those time i will do some transactions.Then how to recover my data even with out lasing single  transactions.
View 4 Replies View RelatedHi,
Bit of a newbee question:
I'm after hosting a website with basically a table in it that is linked to a MS SQL Server 2005 database, which I want to update on a pretty much continual basis from my own server PC which I'm running a data mining tool that updates the MS SQL Server 2005 database.
Any idea on how I would achieve this, or any pointers would be much appreciated.
Cheers,
Tom
I am trying to run a SSIS package (called "Parent") that calls another package (called "Child"). The packages are stored in SQL Server (MSDB database) and are called from a console app by the code below:
Public Shared Function RunPackage(ByVal server As String, _
ByVal userName As String, _
ByVal password As String, _
ByVal packageName As String, _
ByVal packagePassword As String) As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim result As String = ""
app.PackagePassword = packagePassword
pkg = app.LoadFromSqlServer(packageName, server, userName, password, Nothing)
pkgResults = pkg.Execute()
result = pkgResults.ToString() & vbNewLine
For Each err As DtsError In pkg.Errors
result += "Task: " & err.Source & vbNewLine
result += ParseErrorMessage(err.Description) & vbNewLine
Next
Return result
End Function
If I just run the Child package with this code, then it works! But is I run the Parent package from the same code, then it fails with this error: "Error 0x80004002 while preparing to load the package. No such interface supported".
The only difference between the 2 packages is that the Parent calls the Child, and the Child calls nothing.
The Connection Manager in Parent used to connect to Child uses SQL Server Authentication and it needs to be that.
My question: From VB.NET, how do I run a SSIS package that calls another package?
I was wondering if it is possible to continuously query a real-timedatastream using SQL Server. Does anyone have any experience of this? Ihave found LINUX based systems such as Borealis and STREAM. I wouldprefer to use a Windows based system as the program using the queryresults is Windows based.
View 4 Replies View RelatedWe have transactional replication. After creating replication, we found that many blocking and deadlocks are occurring continuously. Will it cause any blocking on publisher db.
View 4 Replies View RelatedHey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
Thank you
Tej
Hi
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
Can anyone point me to some sample code ord give me an example. I'm trying to run a DTS package from my asp.net/vb.net application. I don't need to pass it any variable, just need it to run. I can't seem to find anything on the net for this. Thanks in advance, Ryan
View 7 Replies View RelatedHi
I have one DTS package looks for specific file in a directory to process the data into sql. I have schedule this package to run every 5 mins.
I would like to invoke the package when a file arrive at the folder instead up running it every 5 mins.
Thanks
Hi Folks
I am calling SSIS package from my .net code. I want to know whether my package is still running or not. And if it is taking longer than a limit then execution of package should stop. I am executing package in different thread like this
DelegateExecute del = new DelegateExecute(pkg.Execute);
del.BeginInvoke(null, null);
As I explored, I learnt that RunningPackage class could help me in not only determining whether package is running butalso in stopping too. Unfortunately RunningPackage class is saying that there is no package running! Is Runningpackage works only with the packages running from MSDB?
Following is the code I am using.
public string LoadPackage(string path, long ThrashHold)
{
string pkgLocation;
Package pkg;
Application app;
//DTSExecResult pkgResults;
string str;
pkgLocation = path;
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
//pkgResults = pkg.Execute();
long startDT = DateTime.Now.Ticks/10000;
DelegateExecute del = new DelegateExecute(pkg.Execute);
del.BeginInvoke(null, null);
DealPackage();
while (pkg.ExecutionStatus != DTSExecStatus.Completed)
{
//str = "pkg.ExecutionStatus = " + pkg.ExecutionStatus.ToString();
//str = str + "pkg.StartTime = " + pkg.StartTime.ToString();
//str = str + "pkg.StopTime = " + pkg.StopTime.ToString();
//str = str + "pkg.ExecutionDuration = " + (DateTime.Now.Ticks / 10000 - startDT).ToString();
//Console.WriteLine("********************************");
//Console.WriteLine(str);
//Console.WriteLine("********************************");
if ((DateTime.Now.Ticks / 10000 - startDT) > ThrashHold)
{
Console.WriteLine("********************************");
Console.WriteLine("package should be stopped");
//Console.WriteLine(str);
Console.WriteLine("********************************");
break;
}
}
str = "pkg.ExecutionStatus = " + pkg.ExecutionStatus.ToString();
str= str+"pkg.StartTime = " + pkg.StartTime.ToString();
str= str+"pkg.StopTime = " + pkg.StopTime.ToString();
str = str + "pkg.ExecutionDuration = " + pkg.ExecutionDuration.ToString();
DBOperations dbr=new DBOperations(ServerName,"PEPE_SSIS","sa","sa@123");
dbr.EnterPackageDetail(path,pkg.ExecutionStatus.ToString(),pkg.StartTime.ToString(),pkg.StopTime.ToString(),pkg.ExecutionDuration.ToString());
return str;
}
private void DealPackage()
{
RunningPackages pkgs = app.GetRunningPackages(se);
int pkgsRunning = pkgs.Count;
Console.WriteLine("Packages before stop: " + pkgsRunning);
foreach (RunningPackage p in pkgs)
{
Console.WriteLine("InstanceID: " + p.InstanceID);
Console.WriteLine("PackageDescription: " + p.PackageDescription);
Console.WriteLine("PackageID: " + p.PackageID);
Console.WriteLine("PackageName: " + p.PackageName);
Console.WriteLine("UserName: " + p.UserName);
}
pkgs = app.GetRunningPackages("yourserver");
foreach (RunningPackage package in pkgs)
{
package.Stop();
}
pkgsRunning = app.GetRunningPackages("yourserver").Count;
Console.WriteLine("Packages after stop " + pkgsRunning);
}
one more guidance needed with regard to the Quotation marks
path = @"D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx";
jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f "'path '" /Set package.Variables[User::ArchiveFileType].Properties[Value];""Excel""'", cconn);
How Do i set the path's value in c# the quotation marks are wrong again. cos i want to set value of path according to selection in the UI.
Select("deviceUniqueId = '" + deviceUniqueId + "'"); is an e.g of Doing in C# but when i implement it through text it does not recognize
Thanks
Jas
When I ran a package directly on the machine with sql 2005 installed it ran at a fine speed. But when I used Remote Desktop to access the same client and run the same package it was much slower.
1. I think that the package is running in the memorycpu of the local machine. Is this correct?
2. Is there any way for the remote client to run the package in the server's memorycpu?
TIA,
barkingdog
Hi all,
I am trying to run a Excel macro from a DTS package.I've created the macro and Batch file to run a macro, and i am executing a batch file from DTS package.I am able to run a macro when i am executing a DTS package manually.but its not working when i schedule the process. any solutions?
its not the security problem also.I've checked it.
I have a DTS package hat copies several tables to another Reporting database.
When executing it as a apachkage, it works fine. However, when I try to schedule it as a job, it fails. I tried this with another package and it didn't work also. Any ideas?
Thanks,
Steve
Is there a way to run a local package from a stored procedure?
Thanks,
Ken Nicholson
I have a DTS package that is owned by Joe.
A job that is owned by Domain Admin runs Joe's job every night.
Joe has left the company and his account will be deleted.
2 Questions:
1. Will the job still be able to run the DTS package ?
2. If I need to change the owner of Joe's DTS package, how do I do that ?..just a simple 'Save as' ?..and if so, I will not be able to save the DTS package with the same name...and thereby the job will not recognize the new DTS package name...will I have to re-shedule the new DTS package ?
thank you
Hi,
Does anyone know if it is possible to run a DTS Package from MS-Access database (.mdb)? If yes, could you please specify how to do that? Thanks!
Regards,
av
Hi
I have a SSIS package stored on the database server under Stored Packages within the Integration Services section.
Does anyone know how to run this package using SQL?
Thanks
I'm trying to stop a DTS package from continuing to run after the result of the following ActiveX script:
Function Main()
If day(date) > 2 and day(date) < 10 and weekday(date) = 2 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End if
End Function
Any help would be appreciated.
We have 2 Windows 2000 Servers, Server2 and Server3. Server3 alsohosts a SQL Server 2000 Instance. Server2 DOES NOT host any SQL Server2000 instances and is used as our Application Server. We are trying tostop a "service" that is setup on Server2 by executing a DTS packagethat resides in SQL Server on Server3. So far, we have had little luckdoing this. In other words, if you execute this package, it tries tostop "service" on Server3 as opposed to stopping it on Server2.Appreciate any help and feedback.ThanksJagannathan Santhanam
View 1 Replies View RelatedI'm trying to run a SSIS package (dtsx) from inside an sql job (SQL Server agent). This works fine if the user running (run as) the step is a local admin on the server. If it's not, I get the error message "The package could not be loaded. The step failed". This happens even if the user has all possible serverroles such as "sysadmin" etc in SQL.
So, my question is, is there any way to load an SSIS package without being local admin on the machine? In case it is, what is needed?
regards Andreas
Hi everybody,
I have to create a package that executes other packages I've already created... Every one of these packages run with the same Configuration File, but if I try to execute the main one, including the path of the file, I get errors from the other packages because they can't find it... How can I manage to pass this file and its content to the other packages??
Here a little explanation of te process:
Main Package needs configuration file X.dtsConfig, and calls: package1, which needs configuration file X.dtsConfig; package2, .......
I hope everything is clear...
Hi,
I have a solution, and I have a few projects in this solution. Each project has a few packages in them. The problem/question I have is this: when I am working on one of the packages, I have only this package open. When I try to run/test this package, every single package in every project gets opened, recompiled/rebuilt before my current package gets to run. It's very frastrating and time consuming. Is there a way to somehow disable this weird behavior ? Is there a way to just build the package I am currently working on ?
Please, help.
Thanks,
Victor.
Hi,
I have a package that calls an "Execute Process" task which calls a .bat file. The .bat file references a mapped drive, as in:
s:
cd s:mypathftp
type %1 | c:gnupggpg --yes --batch --logger-fd 1 --passphrase-fd 0 --decrypt-files %2 > logsautodecrypt.log
Everything runs fine in BIDS, however from the job the .bat file portion just hangs forever.
Any ideas?
Thanks
Do I able to track the exact process of a running package without logging this in to any of the tables.
For example if i am running a batch of SQL's through a application or in query analyzer I can able to track it through profiler or SP_WHO2 like this, do I able to track the SSIS Package (running from SQL server not from BIDS) which transformation it is performing and what is the status of it?
I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.
When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:
Executed as user: LOMMELDOMAdministrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.
The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.
Any clues?