• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Exec SQL Task with Variables

I know the title isn't very original... sorry
I have detail I am importing from an XLS -the Content of some cells is more than 255 char. I saved the XLS to CSV and got the same error importing. so I took the contents of the 5 cells that had more than 255 char... deleted them from the XLS and made 5 varcha varables for the content in question. after importing the XLS into the Table (less the content of hte 5 cells with more than 255 char) I now want to update those recs with the content of the variables. using an Execute SQL Task I mapped the 5 variables in the Parameter Mapping number them 0 to 4
I got as for as  the following
declare @DomAcctExpr22 as varchar(1000);
declare @DomAcctExpr23 as varchar(1000);
declare @DomAcctExpr24 as varchar(1000);
declare @DomAcctExpr25 as varchar(1000);
declare @DomAcctExpr26 as varchar(1000);
  -- no problem
when I enter
Select @DomAcctExpr22 = ?
Select @DomAcctExpr23 = ?
Select @DomAcctExpr24 = ?
Select @DomAcctExpr25 = ?
Select @DomAcctExpr26 = ?
  -- it errors out. can someone help me
2 Solutions
Reza RadConsultant, TrainerCommented:
Don't use sql scripts that contain declare variables directly in execute sql task
Create a stored procedure with these 5 parameters and in execute sql task just execute this procedure.

this was reason of your error,

but I don't recommend to use this way for enlarging your cell contents more than 255 character. why you don't expand the columns length in data flow task itself? what is wrong there?

I'm with reza_rad...
Create a Stored Procedure inside database (more security and already compiled) and call from SSIS by EXEC MyStoredProcedure ?,?,?
dmlyo150Author Commented:
Thanx -davlyo
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now