Using Stored Procedures in SSIS

• Kyle Schlapkohl

  SSCrazy

  Points: 2463

  More actions

  August 22, 2007 at 7:22 am

  #118219

   

  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

• Kyle Schlapkohl

  SSCrazy

  Points: 2463

  More actions

  August 22, 2007 at 7:45 am

  #728563

  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

  (@Server, EXEC SP_HELPSRVROLEMEMBER);

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

  Kyle

• Ed Zann

  SSCrazy Eights

  Points: 8694

  More actions

  August 23, 2007 at 11:39 am

  #729052

  Here I think is one way to approach it:

  1) SQL Task to set @server into an SSIS variable

  2) OLEDB Source to get results of EXEC SP_HELPSRVROLEMEMBER

  3) Derived Column to add back servername and static column

  4) OLEDB Destination to write out

  Ed