4

We have .sql files that test our ETL. Manual execution in SSMS is with SQLCMD enabled and results are written to flat file. This is done via :out (SQLCMD command) followed by the desired filepath location. We now are working to automate the execution of these within SSIS and have hit a snag. How do we enable SQLCMD in SSIS as we loop through the .sql files?

Further clarification: our .sql file is written as

:out \\(desired file destination folder and file name).txt
sel top 10 a,b,c from table A
sel top 10 d,e,f,x,t from table B
sel top 10 a,b,g,q,o,p from table C

When this is run from SSMS it generates the result to the destination stacking the answers one after the other. our intention is to use this .sql file run from VisualStudio (SSIS). We tried to use the .sql file as storproc but the result gives the answer of the first sel. while using Script Task. The execute SQL task (with full result set) does not produce the file at all because the answer is stored as variable. any suggestion on this please?

Mike
  • 85
  • 8
  • Are you actually using `sqlcmd` mode for the particular features it offers, and if so, which -- variable replacement? While you *can* run `sqlcmd` explicitly, this is likely to be quite slow and inefficient -- if it's the only thing you're doing, I wouldn't even use SSIS for that, as a simple PowerShell script is likely to be far easier. On the other hand, it may turn out that there are alternative approaches in SSIS that are more effective and don't require scripts in `sqlcmd` mode. – Jeroen Mostert Jan 28 '19 at 15:11
  • edited per your feedback, thx – Mike Jan 28 '19 at 15:13
  • The files are run with sqlcmd in SSMS in order to use the :out command. The .sql files themselves are static. Only update when test case needs updating. The plan is to use SSIS to loop through these files, execute the queries, output summary info to desired flat files. Using SSIS is desirable as it allows us to tie into other internal scheduling processes. – Mike Jan 28 '19 at 15:16
  • What is your output that you are writing? Is it the same for each SQL script? – KeithL Jan 28 '19 at 16:18
  • No, it varies. Each script contains a set of test cases. A test case is made up of a few queries and then something like an "except" or duplicate records check. Wrap each check in a validation step and flag the test case as passed/failed and output the result. In the event of a failure, we also output a bit of data to help debug. – Mike Jan 28 '19 at 17:45
  • 1
    @Mike i think that the answer provided contains what you need, if you need more details i can provide an answer with more details. But i think you can utilizes from information mentioned in the links provided without the need to another answer – Hadi Jan 28 '19 at 20:44
  • @hadi - I have tried to further clarify our question above. Would you have a minute to review that and provide feedback. – Mike Feb 07 '19 at 13:00

1 Answers1

2

You can use a Foreach loop container to loop over .sql files, and inside the container you can use an Execute Process Task to execute the SQLCMD commands.

You can refer to the following links to learn more about For-each loop container and Execute Process Task:

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
  • Unless we are missing something, I am not sure we can get the desired output based on this response. I have added info to the initial question to help further clarify the problem we are facing. – Mike Feb 07 '19 at 13:02