sql server 2005 - How to handle NULL values returned by a stored procedure from within SSIS package? -

i newbie ssis , having issues loading results of stored procedure includes null values table ssis. have follows:

step 1

execute stored procedure on database1 return full result set , put ado object user::callresults

step 2

then loop through results of user::callresults mapping 2 variables:

variable         index ado object type     nullable   user::id         0      object              no   user::result     1        object              yes     

step 3

then in insert row database2 takes each row , executes "insert dbo.mytable id, result values (?,?)"

i map id int , result long respectively.

when execute error:

failed following error: "an error occurred while extracting result variable of type (dbtype_i4)". possible failure reasons: problems query, "resultset" property not set correctly, parameters not set correctly, or connection not established correctly.

seems errors when there null in result. suggestions make ssis allow nulls?

you can achieve process have described in question using data flow task. here step step description of fetching data using stored procedure , inserting table, done inside data flow task. example give idea of how can done , uses single instance of sql server.

step-by-step process:

1. create 2 tables named dbo.source , dbo.destination , populate table dbo.source data shown in screenshot #1. create table scripts provided under scripts section.

2. create stored procedure named dbo.getdata using script provided under scripts section.

3. on ssis package, create variable named storedprocedure shown in screenshot #2. variable contain stored procedure execution statement.

4. create ole db connection in connection manager connect sql server instance.

5. on control flow tab of ssis package, place data flow task shown in screenshot #3.

6. double-click on data flow task navigate data flow tab. inside data flow tab, place ole db source , ole db destination shown in screenshot #4.

7. configure ole db source shown in screenshots #5 , #6. notice source using variable created in step #3. data returned stored procedure source input.

8. configure 'ole db destination` shown in screenshots #7 , #8. insert data destination table.

9. screenshot #9 displays sample package execution.

10. screenshot #10 shows data in tables after package execution. note destination table contains null values. possible because column qty can accept null values. however, if had passed null values itemnumber column, package have failed because column non-nullable.

hope helps.

scripts: .

create table [dbo].[destination](     [id] [int] identity(1,1) not null,     [itemnumber] [varchar](50) not null,     [qty] [int] null, constraint [pk_destination] primary key clustered ([id] asc)) on [primary] go  create table [dbo].[source](     [id] [int] identity(1,1) not null,     [itemnumber] [varchar](50) not null,     [qty] [int] null, constraint [pk_source] primary key clustered ([id] asc)) on [primary] go  create procedure [dbo].[getdata] begin     set nocount on;      select      id             ,   itemnumber             ,   qty            dbo.source end go 

screenshot #1:

screenshot #2:

screenshot #3:

screenshot #4:

screenshot #5:

screenshot #6:

screenshot #7:

screenshot #8:

screenshot #9:

screenshot #10: