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!