SQL/SSIS Help

Question

SQL/SSIS Help

rocky_498

SSCertifiable

Points: 6532
More actions
May 15, 2014 at 6:47 pm

#293312

Hi Guys,
I need help. Here is my Store Procedure, its a sample store procedure..
ALTER PROC [dbo].[USP_TestSP]
AS
SET FMTONLY OFF
SET NOCOUNT ON
if object_id('tempdb..##Temp1') is not null
DROP table ##Temp1
Select
Column1,
Column2,
Column3
Into ##Temp1
From Table1
Where DateRange Between '20140513' and '20140514'
Select * from ##Temp1
Insert Into TableB
(Column1,Column3)
Select Column1,Column3 from ##Temp1
SET NOCOUNT OFF
Here is the Problem. When I call this Store Procedure from SSIS. Its Inserting data twice or three times.Is anyone can help me. That would be big help for me.
Please let me know if u want to know more information.
Thank You,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How are you calling the stored procedure?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thank you for your reply.

Data Flow >> Ole DB Dource >> SQL Commands/ Exec My Store Procedure.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

rocky_498 (5/16/2014)
Thank you for your reply.
Data Flow >> Ole DB Dource >> SQL Commands/ Exec My Store Procedure.

You have multiple queries/result sets in your stored procedure, that might give a problem.

Why would you execute this in a source component?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

So I can use the data and create a Report and send Report to User in Excel format through Send Email Task.

Could you Please Help me to solve this issue, if u ever see this kinda problem in your career.

Thank You.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

The query is going to return a result to the screen from your select and the insert two columns into your non temp table. If you want to automate email sending, aside from SSIS you can also use a report subscription in SSRS. When you create one you have a form you fill in that defines who you want to send the email to with the report attached. Just thought I'd mention in case this helps. Otherwise you can simply insert the results to a table with your procedure then use a dataflow to capture those new inserts and send them to a send email task. This makes your logic more readable.

----------------------------------------------------

rocky_498 SSCertifiable Points: 6532 More actions · Answer 6

Thank You All for your reply. I think My question is not clear or I couldn't explain very well.

My original question was In My store procedure I am inserting data to my table. When I execute my SP through SSMS runs fine no problem, same SP if I excute in SSIS runs fine without any error, but Inserting data twice or sometime triple (Insert statement that I am using in SP).

I was not asking how to create or send file. My question was is some one how I can fix below ERROR.

Thank You!

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

I'm going to take a wild guess here. You have a exec SP in your ole db datasource ... that itself inserts data to a table... and is also passing the results of the select * statement down to the destination in the data flow task. So this could insert the same records again... and I dont know what else you have in that SSIS package that could be creating a similar effect.

When you run this in ssms, only results to screen before the actual insert.

----------------------------------------------------