July 12, 2012 at 4:58 pm
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?
July 12, 2012 at 11:42 pm
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
July 15, 2012 at 5:58 pm
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
July 18, 2012 at 6:09 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply