Link to home
Start Free TrialLog in
Avatar of paultran00
paultran00Flag for United States of America

asked on

How can SSIS prompt user to get just 1 month of data from a SQL SERVER table?

Hi, I'm using SSIS 2008 and SQL SERVER 2008R2.

I have a table that has several months of data.User generated image
How do I make SSIS prompt the user for a string such as 'JUN 2018' to pass to a stored procedure query or VIEW that gets the data?

Currently, my SSIS calls the table that has data.  Please see the attached 2 printscreens.
User generated image
Avatar of lcohan
Flag of Canada image

I think you can add some SSIS input parameter and dialog box as described below:

Integration Services (SSIS) Package and Project Parameters

Configure Dialog Box
Avatar of paultran00


I'm looking at the link about Configure Dialog Box but I don't see the PARAMETERS PAGE it's talking about.  I'm using SSIS 2008.
These are the OTHER WINDOWS that I see (see printscreen)User generated image
In SSIS variables tab, I created a variable named varMonth.  I created a SCRIPT TASK to and successfully prompted a DIALOG BOX and assigned a value 'JUN 2018' to variable varMonth.



What is the correct syntax to get the SELECT STATEMENT to get the WHERE to see the varMonth?
Mine looks like this:

SELECT        dbo.tbl_FEP_69601.*
FROM            dbo.tbl_FEP_69601
WHERE ([Reporting Period] = varMonth)
This doesn't work either:

SELECT        dbo.tbl_FEP_69601.*
FROM            dbo.tbl_FEP_69601
WHERE ([Reporting Period] = @[User::varMonth])
User generated image
I created a variable varSQLMonth and a SCRIPT TASK:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum

	Public Sub Main()
        Dim message, title As String
        Dim myValue As String
        Dim myString As String

        message = "Enter a value for parameter month/year format such as 'JUN 2018'"
        title = "3 letter Month, space, 4 digit Year"
        myValue = InputBox(message, title)

        Dts.Variables("User::varMonth").Value = myValue
        MsgBox("month: " + Dts.Variables("User::varMonth").Value)

        Dts.Variables("User::varSQLMonth").Value = "SELECT * FROM dbo.tbl_FEP_69601 WHERE ([Reporting Period] = '" & myValue & "')"
        myString = Dts.Variables("User::varSQLMonth").Value
        MsgBox("sql month: " + myString)

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

Open in new window


butUser generated image When I tried DATA ACCESS MODE=SQL COMMAND FROM VARIABLE,  get this error (see printscreen):
In addition to the above correct answers, a simpler workaround would be to...
(1) Create a table that holds single-value variables, in this case your current month. I don't have a good SQL Server example of this, but an Access example is the article Table Based Access Variables, and it wouldn't be much of a jump to apply this to SQL Server.
(2) Create a view that SELECTs FROM your main table, with a WHERE clause for current month = {the value stored above}.  Test to make sure the query only returns rows for that month.
(3) Figure out the support of this table and how the value would change when the Current Month changes.
(4) Modify your SSIS data flow task to use the view instead of the table.

Good luck.
Avatar of paultran00
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial