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

paultran00
paultran00 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

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

Author

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.

Author

Commented:
These are the OTHER WINDOWS that I see (see printscreen)ssis_other_windows.jpg
Ensure you’re charging the right price for your IT

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

Author

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)

Author

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

Author

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):
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial