Exec SQL Task with Variables

Posted on 2009-12-28
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

    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

    I'm with reza_rad...
    Create a Stored Procedure inside database (more security and already compiled) and call from SSIS by EXEC MyStoredProcedure ?,?,?
    LVL 2

    Author Closing Comment

    Thanx -davlyo

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now