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.fep_ssis_upload.JPG
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.
Need to get just 1 month of data from this
paultran00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Integration Services (SSIS) Package and Project Parameters
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-package-and-project-parameters?view=sql-server-2017

Configure Dialog Box
https://docs.microsoft.com/en-us/sql/integration-services/catalog/configure-dialog-box?view=sql-server-2017
0
paultran00Author Commented:
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.
0
paultran00Author Commented:
These are the OTHER WINDOWS that I see (see printscreen)ssis_other_windows.jpg
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

paultran00Author Commented:
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.

QUESTION:

In the OLE DB SOURCE EDITOR , I changed the DATA ACCESS MODE choice from TABLE to SQL COMMAND.

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)
0
paultran00Author Commented:
This doesn't work either:

SELECT        dbo.tbl_FEP_69601.*
FROM            dbo.tbl_FEP_69601
WHERE ([Reporting Period] = @[User::varMonth])
SSIS_QUERY_PARAMETER_FROM_DIALOG_BOX.JPG
0
paultran00Author Commented:
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

.

butssis_error_sqlcommand.JPG When I tried DATA ACCESS MODE=SQL COMMAND FROM VARIABLE,  get this error (see printscreen):
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
paultran00Author Commented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.