Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SQL Database Name Change - How to handle this in SSIS on SQL Tasks Expand / Collapse
Posted Thursday, July 11, 2013 11:13 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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
Post #1472718
Posted Thursday, July 11, 2013 11:41 AM



Group: General Forum Members
Last Login: Tuesday, April 26, 2016 2:23 PM
Points: 477, Visits: 2,213
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.
Post #1472726
Posted Thursday, July 11, 2013 6:16 PM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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!

Post #1472831
Posted Friday, July 12, 2013 11:54 AM



Group: Administrators
Last Login: Yesterday @ 11:16 AM
Points: 33,526, Visits: 17,660
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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473185
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse