Execute T-SQL Statement using Parameter Values
-
I'm using Visual Studio 2012 and also new to SSIS.
I have a simple package with a data flow that reads from an OLE DB Source and writes to an OLE DB Destination. There is a control flow that executes an T-SQL statement to delete from a table such as:
Delete From dbo.MyTableName
Where Period = DatePart(mm, DateAdd(d, -1, GetDate())
And FY = DatePart(yy, DateAdd(d, -1, GetDate())I want to replace the DateParts with variables that I will get from a different table using a different connection. Like this:
Delete From dbo.MyTableName
Where Period = @PeriodVariable
And FY = @FYVariableIs this possible to do? If yes, please explain the steps in detail.
Thank you.
-
Yes, it's possible. You use output parameters to get the values from your different connection into variables, then you use those same variables as input parameters to your delete task. Sorry that's not very detailed, but I'm not sure which part you're unsure of.
John
- VA123 - Thursday, March 9, 2017 7:23 AM
Create variables in your SSIS package for Period and FY.
Populate these variables using an ExecuteSQL task.
Create another ExecuteSQL task with SQL like thisDelete From dbo.MyTableName
Where Period = ?
And FY = ?And map the parameters to the variables you've just populated.
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 -
I'm struggling with getting this to work
1. Created two variables named var_fy and var_period
2. Created a Execute SQL Task to get the period and fy values
a) Select Period, FY From ReportingPeriod
b) Parameter Mapping - User::var_period and User::var_fy with parameter names 0 and 1 and direction is Output
3. Created a Execute SQL Task to delete from table using the variables set in step 2.
a) Delete From azuInvoices Where Period = ? and FY = ?
b) Parameter Mapping - User::var_period and User::var_fy with names 0 and 1 and direction is InputThe records are not getting deleted in step 3; I believe the variable values are not being passed from step 1 to step 3 or the variables are never set in step 1 or variables are never retrieved in step 3. I hard-coded variables values and still no records were deleted from the azuInvoices table.
Any ideas on what is wrong?
- VA123 - Thursday, March 9, 2017 1:30 PM
Use multiple Breakpoints (select component and press F9) to pause execution between different components on your control flow, so that you can view the contents of local variables at run time and work out which part is failing.
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 -
It appears the var_period and var_fy variables are never set in the first Execute SQL Task where I select period and FY from ReportingPeriod. table.
- VA123 - Thursday, March 9, 2017 1:47 PM
OK. Please describe exactly how this ExecSQL task is configured.
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 - VA123 - Thursday, March 9, 2017 1:47 PM
You need Select ? = Period, ? = FY From ReportingPeriod.
John
-
Thank you all; however, this is still not working. It appears the Period and FY variables never get populated. I've attached screen shots of the steps. Please have another look.
-
One more screen shot.
-
How many rows are there in cvuReportingPeriod?
John
-
There is one row from cvuReportingPeriod. See attachment from ManagementStudio.
-
I tend to do this differently from John. Let's say I have a select statement that returns two results in one row.
Create the variables.
Create the ExecSQL task.
Important points:
Set Result Set to Single Row
Use an OLEDB connection type
Map the results of the query to the variables using the Result Set node. Here is an example:
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 -
I set the result set to single row and completed the Result Set panel (attached). This produced an error (also attached): Error: No result rowset is associated with the execution of this query.
-
So your SQL Statement now says this?
SELECT Top (1) CurrentReportMonth, CurrentReportYear from CVUReportingPeriod
? I added in the Top (1) as a safety measure. Any more than one row will create an error & the top (1) avoids that possibility.
And can you confirm that you have removed the parameter mappings too? You don't need them if you use this result set method.
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 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply