Execute T-SQL Statement using Parameter Values

Question

Execute T-SQL Statement using Parameter Values

VA123

SSCarpal Tunnel

Points: 4272
More actions
March 9, 2017 at 7:23 am

#321320

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 = @FYVariable
Is this possible to do? If yes, please explain the steps in detail.
Thank you.

Viewing 15 posts - 1 through 15 (of 18 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246715 More actions · Answer 2

VA123 - Thursday, March 9, 2017 7:23 AM
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 = @FYVariable
Is this possible to do? If yes, please explain the steps in detail.
Thank you.

Create variables in your SSIS package for Period and FY.
Populate these variables using an ExecuteSQL task.
Create another ExecuteSQL task with SQL like this

Delete From dbo.MyTableName
Where Period = ?
And FY = ?

And map the parameters to the variables you've just populated.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 3

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 Input

The 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?

Phil Parkin SSC Guru Points: 246715 More actions · Answer 4

VA123 - Thursday, March 9, 2017 1:30 PM
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 Input
The 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?

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 5

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.

Phil Parkin SSC Guru Points: 246715 More actions · Answer 6

VA123 - Thursday, March 9, 2017 1:47 PM
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.

OK. Please describe exactly how this ExecSQL task is configured.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

VA123 - Thursday, March 9, 2017 1:47 PM
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.

You need Select ? = Period, ? = FY From ReportingPeriod.

John

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 8

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.

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 9

VA123

SSCarpal Tunnel

Points: 4272

March 10, 2017 at 5:03 am

#1932674

One more screen shot.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

John Mitchell-245523

SSC Guru

Points: 148809

March 10, 2017 at 5:09 am

#1932675

How many rows are there in cvuReportingPeriod?

John

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 11

There is one row from cvuReportingPeriod. See attachment from ManagementStudio.

Phil Parkin SSC Guru Points: 246715 More actions · Answer 12

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:

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 13

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.

Phil Parkin SSC Guru Points: 246715 More actions · Answer 14

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.