September 24, 2018 at 2:27 pm
I am having difficulty wrapping my head around this, as the concept is too new to me (I’ve only executed them individually via sql task). I have a table with a multiple stored procedures listed. I need to create an SSIS package that would pull from that table, exec the first one, save the results to a flat csv file, and then delete that row from the table. It would then move on to the next SP, follow the same path and so forth. I promise I’m trying to research this but SEO for google and bing both keep returning the likes of “execute SSIS package via stored procedure” and unfortunately I sort of want the opposite of that. I prefer to learn this rather than just being given the answer (I know this one wouldn’t be a quick “do this then that” process anyway), so if any of you know of a post or already-answered question you could point me too, I would be greatly appreciative!
September 24, 2018 at 2:30 pm
.
September 24, 2018 at 2:55 pm
I have a package that does something like that. One script task generates files, and saves the file names in a table. Another script task tries to get a filename to process from the control table, and if none is available it waits 30 seconds and tries again. It uses "DELETE TOP 1 OUTPUT Deleted.Filename FROM dbo.QueueTable" so it is an atomic action that mimics popping the first item from a FIFO queue. When the first task runs out of work it queues a STOP entry, and when the second task finds a STOP item it exits. (It also quits after not seeing any input for an hour or so, in case the first task crashed.)
The interesting thing is that, if the second task is written to create its own SQL connection rather than sharing the connection with other tasks, you can create duplicate tasks and they will run in parallel. The first task has to queue as many STOP entries as there are parallel tasks.
I would control the action from an SSIS Script Task, but maybe instead of using a SqlCommand object to execute the procedure I would build an SQLCMD command line to execute the procedure with an OutputFile parameter.
September 24, 2018 at 3:24 pm
I do something similar, where i loop through a table full of queries, execute them on a server, and capture the results in a dataset, which is then pumped out to HTML xlsx.
so my solution is very script task heavy: a script task to execute the queries and add the results as a DataTable into a DataSet,.
a second to output the data set into the format of my choice.
the advantage is my Script task does not know or care about column definitions, datatypes, etc, whihc is a headache for a "regular" ssis data flow.
string CurrentCommand = (string)Dts.Variables["CurrentCommand"].Value;
string CurrentQueryAlias = (string)Dts.Variables["CurrentQueryAlias"].Value;
using (SqlConnection sqlConn = new SqlConnection(String.Format(connString, ServerName,"master")))
{
try
{
dsDataSet = (DataSet)Dts.Variables["dsDataSet"].Value;
if (dsDataSet == null || dsDataSet.Tables.Count == 0)
{
dsDataSet = new DataSet();
dsDataSet.Tables.Add(dtErrorMessages);
}
}
catch
{
dsDataSet = new DataSet();
dsDataSet.Tables.Add(dtErrorMessages);
}
try
{
sqlConn.Open();
}
catch (Exception exConn)
{
sqlConn.Close();
string err = ServerName + "--" + "Unable To Connect" + "|" + "sqlConn.Open() | "
Dts.Variables["dsDataSet"].Value = dsDataSet;
throw (new Exception(err));
}
SqlDataAdapter adapter = new SqlDataAdapter(CurrentCommand, sqlConn);
//default is 30 second timeout, even when SqlConnection
adapter.SelectCommand.CommandTimeout = 30;
adapter.Fill(dsDataSet, CurrentQueryAlias);
//save it back so it can be consumed next loop
Dts.Variables["dsDataSet"].Value = dsDataSet;
}
so a code similar to the code is in a script task, which is in a loop of for each ADO that contains the query(and an alias/tablename for the query)
Lowell
September 24, 2018 at 3:38 pm
Thank you both for that information! Just as my avatar states, I'm a newbie, so this is a learning process for me. Some of what you discussed I couldn't comprehend, but it gives me something to study and understand.
I've done a lot of small scale jobs over the past few years that never needed anything fancy, or at least I didn't think they did (now I'm second guessing some of them). They did their job though, so everyone left happy. Now I jumped into the deep end, so I'm learning as I go (client is aware of that, too, so it helps).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply