How to write this package - need help

[katbear]

Hello,

I need to write a package the does the following, and not sure the best approach:

The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.

That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.

Also, each time I create the new output file, I need to delete the old named file.

I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.

Any help appreciated!!

Thank you

 

[katbear]

I guess what I'm confused about is, I don't want to have to define a "flat file connection manager" for every file I want to delete and write to because the list of stored procedures I am running will be updated from time to time, that is, dynamic.

 

[mrdenny]

Use a .NET code block to change the name of the connection object with your FOR LOOP.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Hi mrdenny,

I am working on the beginning of this package so far.

So far I have:

EXECUTE SQL TASK ---> FOREACHLOOP container

The EXECUTE SQL TASK is running a SELECT report_name FROM Reports query. The "report_name" is the name of a stored procedure.

Now, I'm not sure what to put in the FOREACHLOOP container??

All I want to do is, for each "report_name" returned from the EXECUTE SQL TASK, is to run the "report_name"!!

I know there is some trick involved in adding the right component inside the FOREACHLOOP container, that will pick up the variable I have defined in the FOREACHLOOP container, which should be the "report_name".

I just want to feed this variable to another EXECUTE SQL TASK, correct? But I don't see how?

Thanks for your help

 

[MDXer]

Assign the results of the select statement to a variable type object (which you are already doing). In the properties of your For Each Loop container assign the Value of the Current Variable value to a Variable that is a string, for example you have ProcNames of type object and ProcName as string (this is the format I personally use to avoid confusion). Within you For Each Loop container use a Script Task that accepts your Variable as an input, and executes the stored procedure which is returned to a dataset, which you can the write to a file whose name you can derive within the script.

Even if you don't have VB.NET skills this is pretty easy stuff to learn. Any book VB worth it's money will have instruction on OLE DB or SQL Client data sets.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[katbear]

Thanks MDXer, I wasn't sure if what I needed to accomplish would require scripting.

I don't suppose you (or anyone!) have an example script that illustrates how to run a stored procedure within it?

Thanks

 

[MDXer]

Let me first state that my VB.NET Skills are at a level that allow me to do what I need to do. I don't build applications I build data bases.

That being Said this is a splicing of Some code I use to retrive and output values in a SSIS package. The one thing not shown here is that you have to add a reference to the XML.dll, you can do this by right clicking on the Refrence node in the project explorer in the Script editor. This code is untested. SQLIS probably has articles on the other pieces you need.

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Private objDT As DataTable

    Public Sub Main()
	Dim spName as String = CTYpe(dts.Variables("ProcedureName").Value, String)
	
	ExecMySP(spName)
	OutToFile()

	
        Dts.TaskResult = Dts.Results.Success
    End Sub

    '
    Public Sub ExecMySP(ByVal spName As String)
        Dim strSQL As String
        Dim objDA As OleDb.OleDbDataAdapter
        Dim objDS As New DataSet()
        Dim paramLogID As OleDb.OleDbParameter

        Dim strConn As String = Dts.Connections("MyConn").ConnectionString
        Dim oConn As New OleDb.OleDbConnection(strConn)




        strSQL = "exec " & spName & " 

        objDA = New OleDb.OleDbDataAdapter(strSQL, oConn)


        Try
            objDA.Fill(objDS)
            objDT = objDS.Tables(0)
        Finally
            oConn.Close()
        End Try

    End Sub

    Public Sub OutToFile()
	'I don't have this code anywhere handy but you can probably google "VB.Net write to file" and get a number of methods there or possibly in the VB.NET Forum.

	

    End Sub


    End Function

no laughing

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck