89 How to read data from Stored Procedure and pass input parameter from a variable using SSIS

[@antwanwimberly1729]

Wow. Some very useful tidbits in here. I thought that based on your tutorials that the only way to export .csvs was via a scripting task. I was also not aware that a Flat File Destination exists (I was only aware of flat file source). I was wondering how to export multiple files based on a criteria within a loop. This video clears it up for me. I will have to watch this again and document how it’s done so I can reuse it. Thank you so much!!

[@learnssis]

You are most welcome Antwan. Thank you.

[@abelthomas]

Thanks for the video. It was really informative.
Expression created into a new variable is the workaround.
The issue when I tried, was with the SP call from the Data Flow path. Unlike a select query, whose parameter name(while clicking Parameters button) can be Parameter0, a stored procedure expects the same parameter name as it was used to create the SP, i.e @countryName under parameter instead of Parameter0. I did this and it worked.
Simply put, Parameter0 works for select query not for SP.

[@learnssis]

thanks for sharing it.

[@teunkruijer]

Thanks Aqil you're the best. You should make the video public so other people can find the video more easily, but I understand if you don't want to.

[@learnssis]

No problem Teun Kruijer, I will make the video public, but there are some other videos in the queue, so probably next week, this video will be published.

[@gvscreations4182]

Thanks....its more use full

[@learnssis]

thank you.

[@maqsoodmehdi]

Thanks Brother, its Great !!!

[@learnssis]

Always welcome

[@maqsoodmehdi]

@@learnssis i did it with oracle database with OLEDB Source then i get.
------------------------------
Exception from HRESULT: 0xC0202009
Error at Data Flow Task [OLE DB Source [2]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-00911: invalid character".

[@maqsoodmehdi]

"select * from gd_items_lookup where catid = ("+ @[User::CatID] +");" {when evaluted then} select * from gd_items_lookup where catid = ('000001');
if procedure the this code is
CREATE OR REPLACE
PROCEDURE read_data_sprocedure (P_catid IN VARCHAR2)
as
c1 SYS_REFCURSOR;
BEGIN
open c1 for
select *
from gd_items_lookup
where catid = P_catid;
dbms_output.put_line( 'No data Found For Category ID : ' || P_catid);
END;
"begin read_data_sprocedure('"+ @[User::CatID] +"'); end;" {when evaluate the } begin read_data_sprocedure(''000001''); end;
error message is
Exception from HRESULT: 0xC020204A
Error at Data Flow Task [OLE DB Source [2]]: No column information was returned by the SQL command.
Error at Data Flow Task [OLE DB Source [2]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "The system cannot find message text for message number 0x80040e4a in the message file for OraOLEDB.".
Error at Data Flow Task [OLE DB Source [2]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

[@learnssis]

@@maqsoodmehdi Sorry I never tried to pull data from Oracle, thus can't say what might be the reason behind this.

[@maqsoodmehdi]

jee Aqil bahi, i know the same platform is good practice but try this to enjoy more :-)

[@debasishpadhi1837]

Bro that's really informative.. I have a stored proc which takes input and two output parameters as well . As per data extraction failure I am storing them in those two output parameters and will log those output in a flat file everything the ssis runs so that if there are any issues I can go to log file and check error code or error message.. Any idea if we can handle this output parameter ?
Thanks in advance brother

[@mahmoudhassanine9905]

Very Helpful
Can you please record video for call API and pass a parameter to it

[@learnssis]

Hi Mahmoud, I created the videos how to call the web service from C# and from Web Service Task.
th-cam.com/video/CXT5CjolH_U/w-d-xo.html
th-cam.com/video/CtlDNn6DPQo/w-d-xo.html
May be I will check if there is any public api available then will try to make a video on that one.

[@catulopsae]

Clever! Like always

[@learnssis]

Glad you found the video helpful.

[@ExonaQ]

nice video, keep going

[@learnssis]

Thank you Yassine.

[@ASIFALI-pn3uv]

"Thanks" will sound less effective .But hats off to your efforts..👍

[@learnssis]

No problem Asif.

[@keerthanaravi7596]

How to give three input variable in expression
Like exec procname 'variable1', 'variable2', 'variable3'.

[@learnssis]

Hi Keerthana,
You can use the same method to pass 3 input parameters as you provide 1 input
exec procname 'variable1', 'variable2', 'variable3'
So if you have 3 ssis variable variable1, variable2 and variable3 then you can write the code in a SQL Query SSIS variable like below
"exec [dbo].[ procname ] '"+ @[User:: variable1 ] +"', '"+ @[User:: variable2 ] +"', '"+ @[User:: variable3 ] +"' "

[@Shazpathan]

How to read data from stored procedure and export data in .XML file using SSIS

[@learnssis]

I have a video where I shown reading data from sql table and export it to .XML file using C#.
th-cam.com/video/NuT3CpiUSrM/w-d-xo.html

[@dilipkumar-ck1nc]

Provide code in notepad

[@learnssis]

I have already uploaded the required file and code.

[@dilipkumar-ck1nc]

@@learnssis it is not correct

[@learnssis]

@@dilipkumar-ck1nc Can you please tell which specific code is missing ?

[@learner_SSIS]

Awesome sir 😀

[@learnssis]

Thank you Naveen Raja.