Error when using SP in data flow

I want to execute a stored procedure in an OLE DB source in a Data Flow Task.

The stored procedure has a parameter.

When I put in an SQL command as :

EXEC sp_readcustomers 1

(thus passing 1 as the parameter value I can use the Preview button and I get a list of columns being returned.

The data flow task should run inside a ForEach Loop where I assign the value of an ADO resultset to a variable and it is this variable I want to pass to the SP :

EXEC sp_readcustomers ?

In the Parameter mapping I then name the parameter @.par_company_id (which is the exact same name as in the SP) and map it to the variable var_company_id.

@.par_company_id has been declared as int, var_company_id as Int16

When I now try to Parse or Preview the query from the OLE DB Source Edit window I get the following errors :

Parse : Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Preview : No value given for one or more required parameters (Microsoft SQL Native Client)

I have already installed SQL 2005 SP2 and VS2005 SP1.

I have tried everything I know, please help ?!?!

Have you tried putting the SQL Statment in a variable? You can parametrized queries using expressions in variables; then the source component will get the sql statemnt from the variable. There are a lot of example on this forum.|||

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

|||

Ronald Dirkx wrote:

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

To use the variable in an OLE DB Source, select the data access mode of "SQL command from variable." Then your variable will show up in the drop down.

Error when I try to add Output Column in in Data Conversion

Here is what I get. Is this an install problem or is this how this software works?

===================================

Error at Data Flow Task [Data Conversion [720]]: An output cannot be added to the outputs collection.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.InsertOutput(DTSInsertPlacement eInsertPlacement, Int32 lOutputID)
at Microsoft.DataTransformationServices.Design.Controls.ComponentMetaDataTreeView.AddOutput()

What did you do to cause the error?

-Jamie

|||When in a package I added a data flow object then drilled into to add data source etc.

One of the items in the tool bar is Data Conversion. I whent to Advance edit on DataConversion on the last tab there was a button to "Add Output"

The result was the error above|||

PeterFreeb wrote:

When in a package I added a data flow object then drilled into to add data source etc.

One of the items in the tool bar is Data Conversion. I whent to Advance edit on DataConversion on the last tab there was a button to "Add Output"

The result was the error above

This is completely correct. it is not possible to add an output to the Data Conversion component. It only has one output and that exists as soon as you drag it onto the design surface.

What exactly are you attempting to do?

-Jamie

|||I have a unicode field in SQL and am trying to go non unicode or VARCHAR2 in Oracle.|||

PeterFreeb wrote:

I have a unicode field in SQL and am trying to go non unicode or VARCHAR2 in Oracle.

OK, well you have no need to go anywhere near the Advanced Editor. Just open the component up by double-clicking on it and add your data conversion in the GUI.

-Jamie

|||OK thanks I have a more involved question for Jamie or whoever knows...

I need to do an update or insert - I could do this easyly in SQL am not sure how to accomplish this in SSIS.

1. Check if the record is in the destination
2. If true -> Update
3. Else ->Insert|||

PeterFreeb wrote:

OK thanks I have a more involved question for Jamie or whoever knows...

I need to do an update or insert - I could do this easyly in SQL am not sure how to accomplish this in SSIS.

1. Check if the record is in the destination
2. If true -> Update
3. Else ->Insert

All documented here Peter:

Checking if a row exists and if it does, has it changed?
(http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)

-Jamie