Passin parameter works sometimes.

  • I have a nice simple SSIS package that runs a series of four tasks on three databases.

    Task 1 DB1

    Task 2 DB2

    Task 3 DB1

    Task 4 DB3

    Each extracts data for a particulat date range using an Ssql query similar to this:

    WHERE

    (dbo.TrainingContract.StartDate >= ?)

    AND

    (dbo.TrainingContract.StartDate < DATEADD ( Year, 1, ?)

    )

    In all the tasks the parameters are the same. ie. the ? refers to the same variable(StartDate)

    The parameter is passed fine for the first two tasks and the last, but fails on the third task.

    This still happens even when indivdual tasks are enabled.

    The only difference I can see is that the third task uses the variable three times instead of two, and involves a second use of one of the databases.

    I should add that the output is appended to a .txt file and the output for step 3 is simply missing.

    Can anyone suggest a reason that the variable fails for task three?

  • How did you do the parameter mapping for task ?

    How does the query of task look like?

    What are the errors that you get?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (7/12/2012)


    How did you do the parameter mapping for task ?

    How does the query of task look like?

    What are the errors that you get?

    Thanks for the reply Koen.

    While I've been using SSIS for a year of so I've not been very adventurous so I've probably been doing things wrong, however they have usually worked. 🙂

    I'd been running simple individual SQL queries and decided to automate the process since they were related and the only "variables" were the financial year dates. The output from an OLE Db task is output to a .txt file. The first task overwrites the file, the others append the data. Thedata from tasks 1, 2 and 4 is in the txt file, but no3 is missing.

    The four tasks are very similar and use the same start and end dates and I did the parameter mapping the same way for all the tasks, set up a variable, put "?"s in the code and click the Parameter button in the SQL Command window. Since the same variable is used for all parameters I simply selected the correct variable in each field.

    The SQL in the command window is:

    [font="Courier New"]    SELECT

       dbo.VeeraFundingTypes.FundingTypeName

       , dbo.VeeraResults.AVETMISSoutcomeCode

       , SUM(dbo.VeeraModules.NominalHoursSupervised) AS [Total Hours]

       FROM dbo.VeeraModules

       INNER JOIN dbo.VeeraEnrolments ON dbo.VeeraModules.ModuleID = dbo.VeeraEnrolments.ModuleID

       INNER JOIN dbo.VeeraResults ON dbo.VeeraEnrolments.ModuleResult = dbo.VeeraResults.ResultID

       INNER JOIN dbo.VeeraClientCourses ON dbo.VeeraEnrolments.ClientCourseID = dbo.VeeraClientCourses.ClientCourseID

       INNER JOIN dbo.VeeraFundingTypes ON dbo.VeeraFundingTypes.FundingTypeID = dbo.VeeraClientCourses.CourseFunding

       WHERE

       (dbo.VeeraResults.AVETMISSoutcomeCode IS NOT NULL)

       AND

       (

          dbo.VeeraFundingTypes.FundingTypeID = 23

         OR

         dbo.VeeraFundingTypes.FundingTypeID = 36

       )

       AND

       (dbo.VeeraEnrolments.ResultDate >= ?)

       AND

       (dbo.VeeraEnrolments.ResultDate < DATEADD ( year, 1, ?))

       AND

       (dbo.VeeraEnrolments.StartDate < DATEADD ( year, 1, ?))

       AND

       (dbo.VeeraEnrolments.StartDate > 0)

       GROUP BY

       dbo.VeeraFundingTypes.FundingTypeName

       , dbo.VeeraResults.AVETMISSoutcomeCode

       Order by

       dbo.VeeraFundingTypes.FundingTypeName

       , dbo.VeeraResults.AVETMISSoutcomeCode[/font]

    By adding the following declare block:

        [font="Courier New"] Declare @BeginYear Datetime

       DECLARE @EndofYear DATETIME

       Select @BeginYear = '2011-07-01 00:00:00'

       Select @EndofYear = DATEADD ( Year, 1, @BeginYear) [/font]

    and changing the WHERE statement as below:

       [font="Courier New"] AND

       (dbo.VeeraEnrolments.ResultDate >= @BeginYear)

       AND

       (dbo.VeeraEnrolments.ResultDate < @EndofYear)

       AND

       (dbo.VeeraEnrolments.StartDate < @EndofYear) [/font]

    everything works as it should and the txt file contains all four sets of data.

    This was the code I used in all four steps previously and it obviously worked, I just thought it would be easier (and less risky) if I only had to change the financial year in one place!

    As for the last question. I hadn't had any errors because I wasn't catching them. So I captured the error output from the OLE db step into a second text file which unfortunately also produced a blank file. So no errors. And still no output.

    Thanks Steve

  • May I suggest that you try implementing this (your SQL code) as an SSIS expression, rather than by using parameters. It's worked for me in the past ...

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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