Variable needed for dynamic ff destination not being retrieved from sql

Question

Variable needed for dynamic ff destination not being retrieved from sql

alicesql

SSCrazy

Points: 2649
More actions
December 9, 2016 at 10:15 am

#338149

I have an SSIS package that has an Execute SQL task to
SELECT SUBSTRING(MAX([InvoiceDate]),1,2) as Monthsql
FROM BJmm (Table has a string date field per recipient requirement)
The Sql Source Type is Direct Input, the Result Set type is Single Row.
The Result Name is Monthsql and the Variable Name is User::Monthsql.
I mapped a parameter with Variable Name = User::Monthsql, Direction=Input, Datatype=VARCHAR, Parameter Name =0 and Parameter size=-1
The variable is set up as Name=Monthsql, Scope=The Package Name, Datatype=String, Value=0.
The properties of DestinationConnectionFlatFile connection show the connection string as
D:\directory.L999HDO.0 and when I expand the + at Expression, I can see the expression I entered which evaluates successfully as "D:\\Directory\\L999HDO.0"+(DT_STR,2,1252)(@[User::Monthsql]).
There is also a dataflow task which has an OLE DB Source from which all rows are selected, and a Flat File Destination. The properties of this Flat File Destination have Name=Destination - L999HDO.0.
When the package executes, the filename that is created is L999HDO.00, but the query returns '10' as Monthsql. I would expect the filename to be L999HDO.10.
What am I doing wrong...what else should I be looking at to get this to work correctly.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

Did you try putting a break point on post execute event of the execute SQL task try watching the variable value getting assigned?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

alicesql SSCrazy Points: 2649 More actions · Answer 2

I just tried setting a breakpoint on the Execute SQL task, there is nothing in the locals window...shouldn't the variable be showing up here?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

alicesql (12/9/2016)
I have an SSIS package that has an Execute SQL task to
SELECT SUBSTRING(MAX([InvoiceDate]),1,2) as Monthsql
FROM BJmm (Table has a string date field per recipient requirement)
The Sql Source Type is Direct Input, the Result Set type is Single Row.
The Result Name is Monthsql and the Variable Name is User::Monthsql.
I mapped a parameter with Variable Name = User::Monthsql, Direction=Input, Datatype=VARCHAR, Parameter Name =0 and Parameter size=-1
The variable is set up as Name=Monthsql, Scope=The Package Name, Datatype=String, Value=0.
The properties of DestinationConnectionFlatFile connection show the connection string as
D:\directory.L999HDO.0 and when I expand the + at Expression, I can see the expression I entered which evaluates successfully as "D:\\Directory\\L999HDO.0"+(DT_STR,2,1252)(@[User::Monthsql]).
There is also a dataflow task which has an OLE DB Source from which all rows are selected, and a Flat File Destination. The properties of this Flat File Destination have Name=Destination - L999HDO.0.
When the package executes, the filename that is created is L999HDO.00, but the query returns '10' as Monthsql. I would expect the filename to be L999HDO.10.
What am I doing wrong...what else should I be looking at to get this to work correctly.
Thanks!

As this query has no parameters, you should remove all lines from the Parameter Mapping section.

If your SQL Server connection is OLEDB, your Result Name (under the Result Set node) should be 0 (zero), not MonthSQL.

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

alicesql SSCrazy Points: 2649 More actions · Answer 4

I changed the Result Set Name to be 0 and changed the query to be

SELECT SUBSTRING(MAX(InvoiceDate), 1, 2)

FROM dbo.BJmm

(removed AS Monthsql)

I'm now getting an error (The Execute SQL task):

Error: 0xC001F009 at Ims1: The type of the value being assigned to variable "User::Monthsql" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Task failed: Get Month

Error: 0xC0019001 at Get Month: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

But The variable is defined as String.

I next tried changing the query to this

DECLARE @InvDt CHAR(2)

SET @InvDt =(

SELECT CAST(SUBSTRING(MAX(InvoiceDate), 1, 2)

AS VARCHAR(2))

FROM dbo.BJmm)

SELECT @InvDt

Result is still '10'

But this had no effect. I've attached a screenshot showing the variable definition:

I'm not sure what to look at next...

alicesql SSCrazy Points: 2649 More actions · Answer 5

Found the solution here at SSC from Fraggle back in 4/4/2012:

Actually, I figured it out. The error was more helpful than original thought.
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property
Hitting F4, I found the property (which isn't listed in the task), and found that it had a user::variable assigned to it. Apparently, it was causing the issues, because as soon as I removed it, the entire process started working just fine without errors getting thrown.
Just find it odd that I was getting the part about "unable to set the value of the variable" when I use the same variable in the expression I originally posted.
Thanks for the assistance.
Fraggle

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

--Comment deleted

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