[Last Call] Learn how to a build a cloud-first strategyRegister Now


Exec SQL Task with Variables

Posted on 2009-12-28
Medium Priority
Last Modified: 2013-11-10
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
Question by:dmlyo150
LVL 30

Accepted Solution

Reza Rad earned 1900 total points
ID: 26134293
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?

LVL 22

Assisted Solution

PedroCGD earned 100 total points
ID: 26136941
I'm with reza_rad...
Create a Stored Procedure inside database (more security and already compiled) and call from SSIS by EXEC MyStoredProcedure ?,?,?

Author Closing Comment

ID: 31670551
Thanx -davlyo

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question