Hi Karan.

Your issue sounds similar to mine - if I understand your's correctly?

My scope: Pass SSIS package data to stored procedure parameters. In my case it is email information (EG, To, From, etc).

I'm developing in VS 2008, utilizing Execute SQL Task.

In the SqlStatementSource text box (see Properties pane for task) I entered ...

EXEC usp.your_stored_procedure

In the SqlStatementSourceType drop down box (see Properties pane for task) I selected ...

DirectInput

Read your stored procedure for the parameters it is expecting. In my case some parameters were @To_Address, @From_Address, @Subject_Line, @status.

In the SqlStatementSource I added/appended the following ...

EXEC usp.your_stored_procedure @To_Address='to@address.com', @From_Address='from@address.com' , @Subject_Line='Test Subject Line', @status=1

Note:

Values passed to parameter must be in synch with defined sp parms. IE, characters in quotes, numeric not.

Each additional parameter must be delimited with a comma.

The email addresses, stored procedure and parameters are made up for this example.

This solution can be tedious (good ol' fashion hard coding) and not future-support-friendly. :w00t:

My next hurdle is to utilize SSIS user variables. I've created the variables but not certain the syntax.

It's test, test, test time. I've read of others having similar errors but have yet to find solution. More opportunity to learn! 🙂

Hope this helps.

Steve