-1

I am looking at building a SSIS package that will execute complex stored procedures to generate resultsets and store those resultsets in another database as tables. Since the columns that are generated out of the resultsets are known , possibility of manually creating destination tables could be considered. Ideally looking at creative tables dynamically.

I would appreciate if examples of this type of requirement could be shared.

Tom
  • 8,175
  • 41
  • 136
  • 267
  • 2
    Here's one way. https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table you don't even need SSIS – Nick.Mc Jul 10 '17 at 23:02
  • The idea is to create a package and then use window services to trigger a weekly job. I need to handle exception and logging as well. – Tom Jul 11 '17 at 05:35
  • I suggest you don't use "windows services", use SQL Agent (unless you are running SQL Express). Now you need to open some tools and start coding as there is not really any specific question here. – Nick.Mc Jul 11 '17 at 05:37
  • How do I handle mapping of source and destination tables. In my case it isnt a straightforward mapping. The tables in the source and destination do not match. The tables in the destination contain columns from two more sources – Tom Jul 11 '17 at 05:39
  • Generally speaking you need to first identify a common key between the two sources so you can decide whether data from the additional column is _added_ to the target table or _updated_ against an existing record in the target table – Nick.Mc Jul 11 '17 at 05:41
  • So what I understand as per your recommendation is that if I have 20 stored procs , I would create a script file that will contain the scripts to execute against the 20 destination tables. If I didnt use ssis what would be hosting these scripts. I am not clear on the storage mechanism .The solution tells regarding only how to write a script – Tom Jul 11 '17 at 05:49
  • You could put all those scripts in a stored proc and run the stored proc (though there only a certain level of nesting that is allowed so it may not work). In the end use whatever solution you are comfortable with. You might prefer SSIS as it's more visual and easier to work out what column is mapped where. But you need to install and use SSDT, deploy packages etc. – Nick.Mc Jul 11 '17 at 05:51

1 Answers1

0

Maybe I am missing something, but that is the standard way of using SSIS.

Source -> Transformation (stored procedure in your case) -> Destination (tables)

Check out this tutorial: http://www.sqlservercentral.com/stairway/72494/

BI_WORK
  • 1
  • 1
  • How do I handle mapping of source and destination tables. In my case it isnt a straightforward mapping. The tables in the source and destination do not match. The tables in the destination contain columns from two more sources. – Tom Jul 11 '17 at 05:34