CODEWITHSUNDEEP
oracle-databasecastingplsqlssis
Stephen Leake
Stephen Leake

Reputation: 37

SSIS Execute SQL Task Output variables

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

Upvotes: 0

Views: 1152

Answers (1)

S.M
S.M

Reputation: 776

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

Upvotes: 1

Related Questions