Question on Conditional Split

Question

 

I am attempting to place this code in SSIS

DECLARE @dte                        SMALLDATETIME
DECLARE @Prior3Months               SMALLDATETIME

SET @dte             = GETDATE()
SET @Prior3Months    = (SELECT DATEADD(MONTH,-3,DATEADD(DAY,1-DAY(GETDATE()),CONVERT(CHAR(8),GETDATE(),112))))

 

SELECT DISTINCT H.*
FROM IDLJC.dbo.productgroup H
  INNER JOIN NH_LJC.dbo.NTL   N ON   N.ILID =  H.ILID
  INNER JOIN NH_LJC.dbo.SJ   SJ ON  SJ.NTLID =  N.NTLID
  INNER JOIN NH_LJC.dbo.JRT JRT ON JRT.ID           = SJ.JRTID
WHERE     JRT.Name IN ('Daily','Monthly','Weekly')
   AND SJ.NTLID IS NOT NULL   
      AND N.ValuationDate < ' +  CAST(@Prior3Months  AS VARCHAR(250))     + '

 

I believe that I have the joins below.  However, I am not sure how to do the conditional split.  I saw on the web that for the date that I could do something like this

ValuationDate < (DT_DBDATE)"2012-02-01"

But I don't want to hard code the date so I was hoping someone could show me how I should code the line like the code above.

 

Also, I could not find an example of how to do

JRT.Name IN ('Daily','Monthly','Weekly')

AND SJ.NTLID IS NOT NULL

---

lcerni

Answer 1

---

lcerni

Answer 2

define two ssis variables, execute an sql task store your dates into the variables and pass that variable as a paramete to the data source query

SELECT DISTINCT H.*
FROM IDLJC.dbo.productgroup H
  INNER JOIN NH_LJC.dbo.NTL   N ON   N.ILID =  H.ILID
  INNER JOIN NH_LJC.dbo.SJ   SJ ON  SJ.NTLID =  N.NTLID
  INNER JOIN NH_LJC.dbo.JRT JRT ON JRT.ID           = SJ.JRTID
WHERE     JRT.Name IN ('Daily','Monthly','Weekly')
   AND SJ.NTLID IS NOT NULL   
      AND N.ValuationDate < ' +  CAST(? AS VARCHAR(250))     + '

and this becomes your datasource. I apologize if I missunderstood your question as it looks too simple to me....

Answer 3

Let's take one step at a time

• Why have you placed Sort, Sort 1,3,5 tasks in the flow. Why not use a ORDER BY clause in the inline SQL
• Provide the opertaion you wish to achieve in the conditional split and the type of meta data flowing from MERGE JOIN 2 on which you need to apply the split operation

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 4

The example for casting and comparing dates appears correct.  To use a variable date, SSIS includes the GETDATE() function (like T-SQL).  It also has a DATEADD function similar to T-SQL (the first argument is a string) so you can do the same "math".  In order to just get the date part of GETDATE() quickly, you need SSIS 2008, and you can cast GETDATE() to (DT_DBDATE) - you can't do that with SSIS 2005.

SSIS' expressions also do not have an "IN" construct.  You'd have to break that up into several equivalence (==) conditions with OR operands (||).  The analogue to "IS NOT NULL" would be to use the SSIS function ISNULL with a "not" operator (!) preceding it.

---

Talk to me now on