LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » Integration Services (SSIS) and DTS » Automate SSIS Package with Changing Dates

Automate SSIS Package with Changing Dates

Options

Previous Topic · Next Topic

Poppey

Posted: Tuesday, July 27, 2010 10:02:15 AM

Rank: Newbie

Joined: 8/24/2009
Posts: 4
Points: 12
Where do you live?: Buffalo, NY

I created an SSIS package in BIDS (I haven’t deployed it yet). The package allows for me to enter a start and end date (via variables). The variables are passed to a stored procedure via an ‘OLE DB Source’ task where I execute a SQL command (EXECUTE dbo.sp_GetMVPData @Startdate = ?,@EndDate = ? ). After the data is gathered the package then generates a flat file then transmits the file via ‘FTP’ task. At the end the package sends an email when complete.

Everything works fine but I would like to further automate the package. I want to deploy the package then have SQL agent run the package every 30 days. How can I populate the variables with dates that change every 30 days?

For example if the package runs on July 1, I want the start date to be June 1 and end date to be June 30. When the runs again 30 days later (on July 31), I'd like the dates to be start July 1 and end July 30.

Scott Whigham

Posted: Tuesday, July 27, 2010 1:28:03 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Why not just do that in the Execute SQL task and then use no parameters? This is much easier to do in SQL than the way you mention. I do things just like this - I have a "GetStartOfMonth" and a "GetEndOfMonth" UDF that I use specifically for this purpose.

Poppey

Posted: Tuesday, July 27, 2010 1:37:59 PM

Rank: Newbie

Joined: 8/24/2009
Posts: 4
Points: 12
Where do you live?: Buffalo, NY

Seriously - That's brilliant.

Sometimes you just need someone to point you in the right direct. Thanks a bunch.
Big Grin

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.