question

Gehima2016 avatar image
Gehima2016 asked

Passing previous month startdate and enddate dynamically to stored procedure

Please, I have a requirement below to develop an ssis package to follow this sequence at runtime;

Step 1) Dynamically get previous month startdate and enddate parameter and stored these parameter in a variable table

Step 2) The two parameters should be inserted into a stored procedure , when stored procedure runs it populates a SQL Server table

Step 3) The SQL Server table should eventually be populated to an excel sheet.

I will appreciate if someone can help with the best route to resolve this in SSIS package.

Thanks in advance.

parametersdynamicstored procedure
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Ange avatar image
Ange answered

If it is always going to be the previous month from when the package is called you can use the following within the stored procedure. Otherwise pass an @datevariable and replace GETDATE() with the @datevariable

--1st Day of Previous month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)

--Last Day of Previous month SELECT DATEADD(SECOND,-1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.