Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script task using batch file parameters Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 3:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi all,

I have created a batch file to run my SSIS package. I will be able to pass parameters in my batch file such as 20121228 (YYYYMMDD format) I have created a package variable named ExecDate of string type in SSIS. And inside script task i have a line of code as:

Dim runDate As DateTime = Now

now instead of declaring runDate as current datetime, i would like to change it to if ExecDate = "Default", runDate = Now

else

runDate = to the parameter i passed in the batch file.

Note: my ExecDate is of String datatype

Thanks in advance, pros please help

update: i think i have solve the above problem by advising user to only pass in parameters of 2012/12/28 format and using a CDATE function to convert it to date.
Post #1406631
Posted Monday, January 14, 2013 7:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
I met with another problem. Lets say using the same package variable ExecDate, data type : String, Default value : "Default"

So if my batch file pass a date parameters to the variable it will used the date pass, else it shall use today date and not the value "Default". How to do this in a Execute SQL task? I got a statement of

Delete From T1 Where creationDate = ? 

This wont work as i wont want the value "Default". Any advice? My column creationDate is of datetime type

thanks
Post #1406746
Posted Monday, January 14, 2013 10:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:32 PM
Points: 1,045, Visits: 2,722
When building a package that allows the user the option of specifying a value for a date variable, I'll set the value of that variable to something unusual, such as 01/01/1899. I'll then use a script task to check that variable to see if it is still set to the default, and if it is, I'll use the current date. If the value is not set to the default, I can assume that a date value has been specified, and I'll use that instead.

hth,
Tim




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1406830
Posted Monday, January 14, 2013 7:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi,
heed your advice i did exactly, and it solved my problem, thanks:)
Post #1407003
Posted Monday, January 14, 2013 10:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:32 PM
Points: 1,045, Visits: 2,722
Great! Glad it worked out. Thanks for the follow up.



Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1407020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse