kschlap


Once again I have a configuration database (CD) question. I am trying to use SP_HELPSRVROLEMEMBER and SP_CONFIGURE in the CD. I am having difficulty calling the stored procedures in the data flow task. What I would like to do, is to just call both SP's in the OLE DB Source and insert them into the OLE DB Destination. However, this doesn't seem to be that easy. Does anybody have any ideas as to how to insert the results from a SP into a central server Any ideas would be great.
-Kyle



Re: Using Stored Procedures in SSIS

kschlap


I guess that my last post was a little vague about what I would like. I would like to be able to get only the required columns of the SP that I need. Also, I would like to insert another column that would be static. The way I view the insert to look would be like this, however, it doesn't work.

DECLARE @Server [nchar] (100)
SET @Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))));

INSERT INTO <table> (@Server, EXEC SP_HELPSRVROLEMEMBER);

-If anybody has any ideas I would be very appreciative.
Kyle





Re: Using Stored Procedures in SSIS

Danny Crowell

Kyle, you might try using a table-valued user-defined-function (UDF). Here is an blog by Jamie Thomson that explains it. http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

If that does not solve your problem you might want to look at using an OLE DB Command transformation.







Re: Using Stored Procedures in SSIS

kschlap

The final query looked like this...
INSERT INTO <table> (EXEC SP_*)

-Kyle