SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Database Name Change - How to handle this in SSIS on SQL Tasks


SQL Database Name Change - How to handle this in SSIS on SQL Tasks

Author
Message
SQL_Enthusiast
SQL_Enthusiast
SSC Eights!
SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)

Group: General Forum Members
Points: 862 Visits: 422
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
ManicStar
ManicStar
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3459 Visits: 3972
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.
SQL_Enthusiast
SQL_Enthusiast
SSC Eights!
SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)

Group: General Forum Members
Points: 862 Visits: 422
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)

Group: Administrators
Points: 141788 Visits: 19420
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search