March 21, 2016 at 7:09 am
I need help understanding the process of editing an SSIS package, what do I do next?
I have created an SSIS package through the wizard in SSMS to extract all data from a table and write it to a flat file. Then I edited the package by using Visual Studio (add existing package) to transform a date column and it works just the way I need it to.
Now I am under the impression that I must get the package back into SSMS in order to have it run as a job step in SQL Agent. Is this correct? If so, do I simply (from SSMS) connect to SSIS, expand the File System (+), right click on the package (which I put there previously, before my VS edits) and Import. What do I select for Package Location? (SQL Server, File System, or SSIS Package Store)
SQL Server is 2008 r2
SSIS is 10.50.1600
Thanks!
March 21, 2016 at 8:50 am
Adding to my confusion is the fact that changing the package in VS and importing it to SSMS/SSIS does not seem to guarantee the correct version will execute with SQL Agent. So, in SQL Agent, I chose File System for the SSIS job step Package Source, and point it to the same package location that is used in VS.
What needs to be where? What is the best structure (I feel as though things are not located consistently right now)
I'm working in an environment where I have to get things done according to a schedule of deadlines that my department had no say in, I have no one to guide me in this and I am being asked for many extracts due to a merger with another (parenting) organization. While no one is being completely unreasonable, I am struggling with a lack of knowledge about SSIS (thank you for the Stairway series!) and trying to do things the right way!
March 21, 2016 at 12:27 pm
alicesql (3/21/2016)
Now I am under the impression that I must get the package back into SSMS in order to have it run as a job step in SQL Agent. Is this correct? If so, do I simply (from SSMS) connect to SSIS, expand the File System (+), right click on the package (which I put there previously, before my VS edits) and Import. What do I select for Package Location? (SQL Server, File System, or SSIS Package Store)SQL Server is 2008 r2
SSIS is 10.50.1600
Thanks!
1) Yes, you must push your package back up to the server in order for the updated version to run. Whether you do this via SSMS or via the BIDS deployment tool is your choice. I use SSMS myself.
2) Where you store the package depends on where your packages are currently stored. Are they stored in MSDB? If so, then use SQL Server. Are they stored on a SAN or NAS file share? Then use File System. Your choices should be obvious once you start the import wizard.
3) A piece of advice. Before importing your new package, connect to Integration Services via SSMS and rename the older package (I usually add _bakMyDate to the end of the name). This way if the new package doesn't work, you didn't lose the old package. You can delete the backed up package as soon as you've verified your new package works as expected. I usually give this a minimum of one week (depending on how often the package runs).
March 21, 2016 at 12:31 pm
alicesql (3/21/2016)
So, in SQL Agent, I chose File System for the SSIS job step Package Source, and point it to the same package location that is used in VS.
This is not a good idea. You don't want SQL Server touching your physical storage for the packages, especially if the VS location is local to your machine or on TFS. The packages should be located on the server running them or on an application server that is easily accessible to the database server. You should check with any existing DBAs at your company to find out where these locations are. If nobody knows, then that is an entirely different (and dangerous) issue.
Currently my company uses a File System that points to a specific folder on the SAN of a clustered App Server. As we upgrade to SQL 2012, we'll be using MSDB for our storage on the local database server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply