0

In SSIS Script task how to execute a stored procedure using oledb connection.

Sreepathi
  • 93
  • 13

1 Answers1

0

Why restricted to oledb?

  • 1st Option: if you can use ADONET connection then you can use below solution in your SSIS script task.

enter image description here

  • 2nd Option: Fetch and store records in some object type variable using SQL TASK (use OLEDB connection here) and then pass that parameter to Script task and perform operation like in the below screenshot. Then you have all records available in datatable to play around. enter image description here
Dhruv Sanan
  • 36
  • 4
  • Thanks for your answer. Actually I'm ok with the ado.net but the issue is that it is working fine in visual studio. But after deploy the package in sql server, the package not working. So I though to try with ole db connection. Do you have any idea why it is not working after deployment. – Sreepathi Aug 18 '22 at 08:27
  • what is the error you are getting after deployment? – Dhruv Sanan Aug 18 '22 at 08:32
  • No error. It will show as running for long time. So I will cancel that execution. Normally in visual studio it will complete with in 2 or 3 seconds. After deployment I waited around 10-15 minutes but that will show as running. – Sreepathi Aug 18 '22 at 08:39
  • Run with CMD and catch the exact error. – Dhruv Sanan Aug 18 '22 at 08:50
  • through cmd also it is executing successfully. – Sreepathi Aug 18 '22 at 09:06
  • In my ssis what I'm doing is that there will be one svg file which is a image file. Through script task I will convert this svg to xaml and png files and after this conversion will call one stored procedure to insert the file converted records into a table. When I run using cmd what I noticed is that, the converted out out file data displaying and then i need to hit "enter" key to process further. – Sreepathi Aug 18 '22 at 09:14
  • I would suggest to share the code. There is some problem there. – Dhruv Sanan Aug 18 '22 at 10:06
  • thanks Dhurv for your comments. Issue figured out and resolved. It is due to some console write code in between. – Sreepathi Aug 26 '22 at 09:47