• 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
0
dmlyo150
Asked:
dmlyo150
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?

0
 
PedroCGDCommented:
I'm with reza_rad...
Create a Stored Procedure inside database (more security and already compiled) and call from SSIS by EXEC MyStoredProcedure ?,?,?
Regards,
Pedro
0
 
dmlyo150Author Commented:
Thanx -davlyo
0
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