May 29, 2012 at 9:42 pm
I want to reuse an ssis package that was created by using the import wizard in a sql server 2008 r2 standard edition database. To accomplish this task, I have the following questions:
1. Is it better to save the SSIS package to the file system or the database and why? Can y0u give me directions how have to save and reexecute the package?
2. The input and output file names are the same except for having month/day/year as part of the file name. Thus can you tell me how to modify the SSIS package so the package will take file names that are dependent upon the date? Do I supply the SSIS package with parameters?
May 30, 2012 at 5:54 am
If you want to edit those packages I would save them on the file system, so you can access them with BIDS.
Regarding the timestamp: you need to put an expression on your connection manager.
SSIS: How to add a timestamp to a Excel File output path.[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2012 at 12:32 pm
Thanks for your answer so far!
I have the following additional questions:
1. Can you tell me how to work with a SSIS package in bids. I have only used bids for writinng SSRS 2008 r2 reports. What files/ solution would I open?
2. Once the file server package as been changed can you tell me the following:
a. Do I need to deploy the SSIS package somehwere? If so, what is the deployment method?
b. Do you have directions of how I can execute the SSIS package? How should this package be deployed into production?
May 30, 2012 at 1:32 pm
1. You would create a project like in SSRS, but it will contain packages with the .dtsx extension. On how to create/edit/maintain packages, I would suggest to search some tutorials on the net or to read a good book on SSIS.
2a. Once a package is finished, you deploy it to the server using one of the following methods:
* using BIDSHelper, a free add-on you can download from Codeplex
* by creating a manifest file and using the deployment wizard http://msdn.microsoft.com/en-us/library/ms137900(v=sql.105).aspx
* by logging into the SSIS service and importing the package manually
* by using the DTUTIL utility http://msdn.microsoft.com/en-us/library/ms162820.aspx
You have two options:
* deploy to the file system. The package is like any other file on the server.
* deploy to SQL Server. The package is stored in the MSDB database. This is my preferred method, as it can be secured using SQL Server security.
2b. You can execute a package using the following options:
* executing it in BIDS. This is primarily for debugging.
* double clicking the package in the file system. This launches the DTEXECUI tool, which you can use to execute a package.
* logging into the SSIS service, right clicking on a package and choosing Execute. This will also launch DTEXECUI.
* by using the DTEXEC utility http://msdn.microsoft.com/en-us/library/ms162810(SQL.105).aspx.
* by scheduling it within a SQL Server Agent job (which uses DTEXEC behind the scenes)
(* advanced: by using the .NET framework to load the package and execute it using managed code)
Mostly the SQL Server Agent jobs are used to schedule packages on the production server.
I would recommend you to ask your employer to schedule an SSIS training. It will get you up to speed with SSIS, teach you the basics and the cost of the training is usually less than all the time you need to spend to learn SSIS yourself. SSIS can have quite a steep learning curve, so a training is a good ROI. I did it, and it surely helped me.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 31, 2012 at 6:36 am
Wendy,
The training advice suggested by Koen is a good idea, you may also get the following book Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution which is very well structure with good examples!
Cheers,
Hope this helps,
Rock from VbCity
May 31, 2012 at 1:54 pm
Rock from VbCity (5/31/2012)
Wendy,The training advice suggested by Koen is a good idea, you may also get the following book Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution which is very well structure with good examples!
Cheers,
+1 for this book. It's one of the best SSIS books I ever read.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply