SQL Task - Set Package variable

Question

I have declared a variable at the package level compdate and am testing data flow to the variable by droping an Execute SQL Task in the Control Flow of the package.

In the task,

SQL Statement:

select ? = (getdate() - 1)

Parameter Mappings:

Variable Name: User::compdate
Direction: Output
Data Type: DATE
Parameter Name: 0
Parameter Size: -1.

Why am i getting error:

[Execute SQL Task] Error: Executing the query "declare @compdate date
set @compdate = (getdate() ..." failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Are you trying to store the result of an SQL query in your variable? — Milen Kindekov
– Milen Kindekov, Commented Nov 8, 2012 at 15:57
yeah trying to return the getdate - 1 result into the package variable from within the package. I have done this before but cant seem to find the reference to how to do it. Or at least something that is straight forward enough to actually digest its meaning. — GoldBishop
– GoldBishop, Commented Nov 8, 2012 at 16:01

Milen Kindekov · Accepted Answer · 2012-11-08 16:22:11Z

2

I do not see why you need to execute an SQL statement to get the previous day as this can be done in various other ways.

To answer your question though, since you are trying to store the result of the SQL query from your Execute SQL Task you have to change the SQL statement that you have provided.

Your new query:

SELECT (GETDATE() - 1) AS DateVar

Where DateVar will be the single parameter that is returned which you need to map to your variable.

You need to delete your Parameter Mappings as they are not needed. Open up the Result Set tab and Add a new result. Set the Result Name to be DateVar and set the Variable Name to be your variable User::compdate

You then need to set up your Execute SQL Task to return a Single Row result set in the General tab, mapped to your variable. Select Single row for the ResultSet option.

Working with result sets is explained in great details here. Scroll down to the 'Working with a Single-Row Result Set' section, it has a great example which you can follow.

edited Nov 8, 2012 at 16:22

answered Nov 8, 2012 at 16:05

Milen Kindekov

1,9731 gold badge23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GoldBishop Over a year ago

can i do this without using ResultSet? Tried using Output Parameter Mapping but wasnt getting anywhere there either

Milen Kindekov Over a year ago

Is there a particular reason why you do not want to use ResultSet?

Milen Kindekov Over a year ago

Try this out: julian-kuiters.id.au/article.php/…

GoldBishop Over a year ago

im looking at full use and understanding....every article uses Result Sets and none of them use the ReturnValue direction of ParameterMapping. Looks like ReturnSet is going to be my only option unless someone can provide a good write-up on how to utilize the Input/Output/ReturnValue Direction without utilizing ResultSet

Milen Kindekov Over a year ago

Did you look at the link I provided in my previous comment.

|

Gowdhaman008 · Accepted Answer · 2012-11-09 09:46:09Z

0

If you want to use without using the result set. try with following steps.

Create the stored procedure in your respective database. Following code is an example.

CREATE proc GetYesterDay(@yesterday datetime output)
as
Select @yesterday=getdate()-1

Create the ADO.NET connection to run the stored procedure. In which, you can mention the direction of the input and output of the parameters.
Create the execute task and configure it as following screenshot.
Click on Parameter Mapping and configure as following screenshot.

Now SSISCompletedDate variable will be filled with respective data.

Hope this helps!

answered Nov 9, 2012 at 9:46

Gowdhaman008

1,3331 gold badge10 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL Task - Set Package variable

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related