July 11, 2013 at 11:13 am
Okay, my DBA will not give me a month end backup database with a static name. Each month, she gives me a database called DB_MonthEnd_2013MMDD. This month I got a a db rightfully names DB_MonthEnd_20130630 and next month will be DB_MonthEnd_20130731. I have a series of jobs that run off the month end database copy, but in order to make them work, I have to change the connection manager to point to the database every month. I want to automate these jobs... SO, how can I handle the changing database name so SSIS will go and look for the database named DB_MonthEnd_*...?
FYI - She will replace DB_MonthEnd_20130630 with DB_MonthEnd_20130731. No other database are named DB_MonthEnd_*
Thank you
July 11, 2013 at 11:41 am
Use a variable to overrride the connectionstring property of the connection connecting to your everchanging database.
You can set the contents of the variable to components of getdate() to look for the current version of your sql database.
July 11, 2013 at 6:16 pm
Excellent. Thank you for the post.
Here's what I did.
I created two variables: MonthEndDatabase and MonthEndDate as both STRINGS at the PACKAGE LEVEL. I set MonthEndDatabase = DB_MonthEnd_ and the MonthEndDate blank.
Wrote SQL: select convert(varchar(10),getdate(),112) AS [GetDate] to give me the date in YYYYMMDD
Added a Execute SQL Task inthe Control Flow, set the ResultSet to Single row, connected to any database on my instance because it doesnt matter where I get the date from, and added the above SQL statement to the SQLStatement seciton of the task.
Then clicked on the Result Set option on the left side of the window. Added a Result Name of GetDate and set it to the variable USER::MonthEndDate
Added a new OLE DB connection to the server/instance and LEAVE the Database EMPTY. Test connection to make sure we're all good...!
Right click on the new connection just created above and select properties
In the properties window, add an expression of InitialCatalog property to expression of @[User::MonthEndDatabase] + @[User::MonthEndDate]
Added a new Execute SQL Task and connected it to OLE DB connection with the empty Database Name created above
In the SQLStatement, added the EXEC <stored proc>
Press OK save and repeat as needed connecting all other tasks that need to use the new month end backup to the connection above.
Reviewed the output and I was successfully able to extra data from the generic database I created today called DB_MonthEnd_20130711... YEAH! :-D:-D:-D
July 12, 2013 at 11:54 am
Good for you.
you might think about scanning a folder in the package for .bak files, working out the latest one, and then grabbing the name for use in the rest of your package. Then you can just drop the files in a folder, run the package (or schedule it) and let it restore.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply