No ADO type command for Data Flow?

I'm probably not looking in the right place, but all I could find when creating a data flow task was OLE DB Commands. I was trying to utilize a dataaccesslayer piece of code that we use every where in our projects, but because it uses ADO and not OLE DB, it caused an issue between the column data types.

Is there an ADO command object available? Or are we forced to use the OLE DB command object? All I was looking to do was to Execute a SQL command. There's an object on the Control Flow level to do that, but not on the Data Flow levelnot sure why that is.

Thanks,

Jeff Tolman
E&M Electric

Its because executing a stand-alone SQL statement isn't relevant for a data-flow. As you have observed you can do that in the Execute SQL Task.

What exactly is it that you want to do? What is your SQL statement?

-Jamie

|||

You would only use the data flow task if you wanted to process many rows of data - either aggregating them to pass them to your command, or issuing the command for every row that passes.

If you have existing code, you can likely acheive that using the Script Component - but be sure your scenario is appropriate first.

Can you describe some more about what you are trying to acheive?

Donald

|||

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

|||

I have a set of records coming in to a Script Component and based on the data and lookups into the destination table I'm determining if data should be deleted, inserted or updated in the destination table. It's basically a table copy routine with a little intelligence for deletes.

Jeff

|||

JazzGeek wrote:

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

Sorry, I'm really really confused. In SSIS you don't access data using the OLE DB Command component. You do it with a source adapter. There are many source adapters including one for OLE DB and one for ADO.

Are you saying that you want apply modifications using ADO rather than OLE DB?

-Jamie

|||

Hey Jamie,

I was just hoping to use our DataAccessLayer code library (which utilized ADO) to perform DB commands against our databases. I realize that with the OLE DB Command object you can execute a SQL command based on any of the input column data. Since I couldn't find an ADO Command object, I thought I'd use our DataAccessLayer library within a Script component to do it.

Thanks for the comments!

Jeff

Newbie: Now getting return value, but need help debugging OLE DB Command Transformation

Good morning, all,

OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.

I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.

My dataflow has the following components:

An Excel Source, containing my test data, consisting of just one row of data,

which points to a

Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @.[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.

which points to a

Data Conversion Transformation, which takes care of all the datatype conversions

which points to a

OLE DB Command, which contains the following as the SQL Command:

exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?

In the OLE DB Command, I have mapped returnValue, my user variable to @.RETURN_VALUE.

Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.

How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.

Thanks,
Kathryn

OK,

I have made a few changes to the package and I can get a return value. The problem is that the data, which is currently only one row in an Excel worksheet, is not getting loaded.

The OLE DB Command's SQL Command is now:

exec spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? output

with the final parameter being an output parameter. On the Column Mappings tab, I have mapped the output parameter @.returnValue which is in the Available Destination Columns to the returnValue variable (in the Available Input Columns) I created earlier in the Derived Column Transformation.

I have added a recordset Destination after the OLE DB Command and I only have the returnValue column in it.

The returnValue is always being returned as 0, which is either because I am not mapping it correctly or because the data is not getting loaded, I'm not sure which.

I can run the stored procedure from SQL Server Management Studio as

DECLARE @.returnValue int

EXEC [dbo].[spImportAgencyData]

@.URN = NULL,

@.FirstName = N'Kathryn',

@........

@.returnValue = @.returnValue OUTPUT

SELECT @.returnValue as N'@.returnValue'

and the correct new ClientID is returned, so the stored procedure is working. I have put a Data Viewer right before the OLE DB Command and run the stored procedure with the exact data coming from the Excel spreadsheet, just to double check and it runs and returns the new Client ID.

Does anyone have any tips on debugging this problem?

Thanks,
Kathryn

|||I had a similar issue (return a value from a stored procedure with 2 Varchar parameters), and eventually this is how I configured the Execute SQL Task:

General:

Connection Type: OLE DB
SQL Statemenet: EXEC ? = AUDIT.LoggingStoredProcedure ?, ?
Bypass Prepare: True

Parameter Mapping:

[Variable Name] | [Direction] | [Data Type] | [Parameter Name]
User::ReturnValue | Return Value | Long | 0
User::Parameter1 | Input | VARCHAR | 1
USer::Parameter2 | Input | VARCHAR | 2

Newbie: Now getting return value, but need help debugging OLE DB Command Transformation

Good morning, all,

OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.

I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.

My dataflow has the following components:

An Excel Source, containing my test data, consisting of just one row of data,

which points to a

Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @.[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.

which points to a

Data Conversion Transformation, which takes care of all the datatype conversions

which points to a

OLE DB Command, which contains the following as the SQL Command:

exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?

In the OLE DB Command, I have mapped returnValue, my user variable to @.RETURN_VALUE.

Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.

How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.

Thanks,
Kathryn

OK,

I have made a few changes to the package and I can get a return value. The problem is that the data, which is currently only one row in an Excel worksheet, is not getting loaded.

The OLE DB Command's SQL Command is now:

exec spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? output

with the final parameter being an output parameter. On the Column Mappings tab, I have mapped the output parameter @.returnValue which is in the Available Destination Columns to the returnValue variable (in the Available Input Columns) I created earlier in the Derived Column Transformation.

I have added a recordset Destination after the OLE DB Command and I only have the returnValue column in it.

The returnValue is always being returned as 0, which is either because I am not mapping it correctly or because the data is not getting loaded, I'm not sure which.

I can run the stored procedure from SQL Server Management Studio as

DECLARE @.returnValue int

EXEC [dbo].[spImportAgencyData]

@.URN = NULL,

@.FirstName = N'Kathryn',

@........

@.returnValue = @.returnValue OUTPUT

SELECT @.returnValue as N'@.returnValue'

and the correct new ClientID is returned, so the stored procedure is working. I have put a Data Viewer right before the OLE DB Command and run the stored procedure with the exact data coming from the Excel spreadsheet, just to double check and it runs and returns the new Client ID.

Does anyone have any tips on debugging this problem?

Thanks,
Kathryn

|||I had a similar issue (return a value from a stored procedure with 2 Varchar parameters), and eventually this is how I configured the Execute SQL Task:

General:

Connection Type: OLE DB
SQL Statemenet: EXEC ? = AUDIT.LoggingStoredProcedure ?, ?
Bypass Prepare: True

Parameter Mapping:

[Variable Name] | [Direction] | [Data Type] | [Parameter Name]
User::ReturnValue | Return Value | Long | 0
User::Parameter1 | Input | VARCHAR | 1
USer::Parameter2 | Input | VARCHAR | 2