SSIS Execute SQL Task Output variables

Question

I have a SSIS package which takes data from a database table and outputs it into csv files. At some point along the way, there is a Execute SQL Task to retrieve the file name (A type and a sequence number which are then used to make the file name, e.g. CAR, 12 would make C0000012.csv. I need to be able to do this in such a way that there are no errors thrown in the case that there are no cars in the table when the package is run.

To do this, I have made a sql package which will check the count, and then either return 'CAR' and the sequence number if there is data or 'NUL' and 0 if there isnt. I have two variables which are mapped as output parameters:

      NAME                   | Direction| Type   | Name | Size
      User::DownloadFileType | Output   | String | TYPE | 3
      User::DownloadFileNum  | Output   | int32  | SEQN | 8

When I run the package I keep getting the error:

The type of the value being assigned to variable "User::DownloadFileType" differs from the current variable type.

I have looked online for help and there seems to be an issue with trying to load a VARCHAR(MAX) into an SSIS string value, my database strings are of the type VARCHAR2 (PL SQL) but I have set max size to be 3 it throughout the package and still no luck.

I think the issue may be with the fact it is a VARCHAR2 type but I'm unsure how I can make it compatible with the SSIS. Any help you could offer would be greatly appreciated.

Thanks, Steve

S.M · Accepted Answer · 2013-07-05 21:28:13Z

1

return 'CAR' and the sequence number if there is data or 'NUL' and 0 if there isnt

String typed variable cannot be assigned NULL value in SSIS. Assign some empty string value instead.

cast the return string value of the type varchar2 in your select statement to varchar(3)

eg. select cast('CAR' as varchar(3)) from dual

edited Jul 5, 2013 at 21:28

answered Jul 5, 2013 at 14:20

S.M

7763 silver badges8 bronze badges

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

2 Comments

S.M Over a year ago

I thought it was typo in your post, error you mentioned is due to the reason as mentioned

S.M Over a year ago

cast the return string value of the type varchar2 in your select statement to varchar(3) eg. select cast('CAR' as varchar(3)) from dual

Collectives™ on Stack Overflow

SSIS Execute SQL Task Output variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related