Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

package references - when loading to server storage

one of the package (in the visual studio solution) has reference to a text (.sql file) for some tsql code, in a task. we can run the package from visual studio. Now if I want to move it to the server storage (storing it in msdb),so that it can be called from a job, other than moving the dtsx file to the server (msdb), how should I update the reference for the misc text that is called/attached to tasks.

thanks
0
anushahanna
Asked:
anushahanna
  • 2
  • 2
3 Solutions
 
8080_DiverCommented:
My first thought is , "Why are you doing storing the T-SQL script in a flat file?  Why not put the T-SQL in a Stored Proc or as a view?  Or, failing those, why not put it in the Data Source component or, at the very least, a Variable in the SSIS package?"



0
 
anushahannaAuthor Commented:
hmm.. i guess it could. this was convenient to keep them seperated in files, and easier for developer to just edit just the files they need to from windows explorer, without opening BIDS.

So, apart from the methods you mentioned, there is no alternative to move it to the server in a way it can be run from a job, right?
0
 
8080_DiverCommented:
Well, you could put the T-SQL in a databaqse column in a special table for thaqt purpose but, since I seriously avoid the use of flat files for any controlling elements of my SSIS packages, I don't know of any other options.

If you put the T-SQL into stored procs, they don't have to open up the package in BIDs; they just have to open the SP and edit it.
0
 
derekkrommCommented:
you can setup a package configuration and choose the file connection's filepath as something you want to configure.

from there, you simply need to update the configuration with the filepath if/when it changes
0
 
anushahannaAuthor Commented:
Thanks for the helpful idea.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now