November 17, 2010 at 10:37 am
I'm new to SSIS development, I'm sure this is completely a newbie question.
I created a package to import data and transform it for use (ultimately some of the detail will be exported to a flat file), all this was accomplished in the BI Dev Studio loaded on my desktop. I saved the package to a networked drive folder as I developed it.
Now that I've tested it completely, I want to run the routine daily.
How do I 'move' it to SQL Server 2005 so that it can be launched by either a stored procedure or some other job scheduler? (The package is currently part of a 'Solution' that contains a number of other unrelated packages. Hey, I said I was new to this.)
As a sidebar to this, it would be great to launch an Excel workbook to perform some additional post processing of the data from SSIS too. Is this difficult to implement?
Thanks in advance for the help.
November 17, 2010 at 3:58 pm
Tim Hief (11/17/2010)
I'm new to SSIS development, I'm sure this is completely a newbie question.I created a package to import data and transform it for use (ultimately some of the detail will be exported to a flat file), all this was accomplished in the BI Dev Studio loaded on my desktop. I saved the package to a networked drive folder as I developed it.
Now that I've tested it completely, I want to run the routine daily.
How do I 'move' it to SQL Server 2005 so that it can be launched by either a stored procedure or some other job scheduler? (The package is currently part of a 'Solution' that contains a number of other unrelated packages. Hey, I said I was new to this.)
As a sidebar to this, it would be great to launch an Excel workbook to perform some additional post processing of the data from SSIS too. Is this difficult to implement?
Thanks in advance for the help.
As far as getting the server to run the package is concerned, you're on the right track. Place a copy of the package where the server can access it and set up a job to execute it.
What do you mean by launching Excel?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 17, 2010 at 5:34 pm
Hi Alvin, thanks for the reply.
I have an Excel Workbook with an Workbook_Open event that runs some VBA code to perform work that is best done in Excel. The transformation of the data is completed and written to another file. At the end of execution, the workbook closes Excel. Its quick, effective and painless.
For the time being I use the Windows Scheduled Tasks Utility (on my desktop) to launch (open) the workbook automatically at a specific time each morning. It works well, however, it's not really a good solution to implement on the server.
I would like to understand the easiest way to duplicate (i.e.Open the Excel Workbook on a daily schedule) this same activity using an SSIS package. I have another process that is similar (Excel workbook activity) but at the end of my 'chain' and it would be better to launch that at the conclusion of my SSIS package running, instead of guessing at a safe time to launch it using another means.
Does that help?
November 18, 2010 at 12:03 pm
There are a number of ways to deploy a package from a file system to a server:
* if you have BIDSHelper installed, right click on the package and click deploy. (make sure you have the right deployment settings, right click on your project and see with BIDSHelper)
* if you don't have BIDSHelper (you should): right-click on your project and go to DeploymentUtility. Set the CreateDeploymentUtility to True. Right-click on the project and select build. Go to the folder where your SSIS package resides. Go to the bin folder and then to the deployment folder. You'll find a copy of your package there, as well as any XML configuration file (if configured in your package) as well as any miscellaneous project file and finally a file with the extension .SSISDeploymentManifest. Double click on this file and the deployment wizard shall launch. Follow the steps in the wizard to deploy your package. Pfiew. That's why you should install BIDSHelper.
* use the dtutil command line utility (more advanced). For syntax, see http://msdn.microsoft.com/en-us/library/ms162820(v=SQL.90).aspx
After you have deployed your packages, they are stored in the MSDB database. To see them, connect to Integration Services, open Stored Packages, and then open the MSDB database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2010 at 12:06 pm
I discovered some code that will open my Excel workbook from a VBScript. The beauty of this is I can fully vet the Script before using it in my SSIS package.
I inserted an Active X Script Task at the beginning of my package, saved it and it works fine. Below is the script I inserted, it may be of value to others. As a note here, my earlier failed attempts at using VB coding to solve my problem was in the framework of the Script Task. Script Task is for .NET coding while Active X Script Task easily accepts VBScript without all the IMPORTS.
' Create an Excel instance
Dim objExcel
Dim objWorkBook
Dim strXLWorkbook
Dim strFolder
Set objExcel = CreateObject("Excel.Application")
' Disable Excel UI elements
objExcel.DisplayAlerts = False
objExcel.AskToUpdateLinks = False
objExcel.AlertBeforeOverwriting = False
objExcel.FeatureInstall = msoFeatureInstallNone
strFolder = "<path to folder>"
objExcel.DefaultFilePath = strFolder
strXLWorkbook = strFolder & "\<Excel Workbook Name>"
'******************** WORKBOOK CODE RUNS HERE! **************************
Set objWorkBook = objExcel.Workbooks.Open(strXLWorkbook)
'*********************************************************************
' Clean up and shut down
Set objWorkBook = Nothing
Set objExcel = Nothing
Set WshShell = Nothing
I don't know whom to credit for this precise code model, I reviewed so many that it's become a blur. Kudos to anyone who attempts to post vetted code with enough details of implementation to be meaningful.
Many will advise against using Excel on a server, my situation was best served by doing so although I fully understand the risks. If this server was anything but the low usage server it is I'd have explored other solutions. Good Luck.
November 18, 2010 at 12:08 pm
You cold also use VB.Net instead of vbscript for opening the workbook.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 18, 2010 at 1:41 pm
Glad that you found a working solution, Tim, but a small sidenote from Microsoft:
The ActiveX Script task is provided only for purposes of backward compatibility with the deprecated component, Data Transformation Services (DTS).
Which means it can be removed from a future product. (I should check if it still exists in Denali, as DTS support has been removed from that release)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2010 at 1:51 pm
Thanks for that piece of information. Two things won't be happening in my circumstance, the client won't be adding SSRS so we're going to recycle a legacy Access solution only with the benefit of SQL on the backend, and they will never upgrade this particular server beyond it's current state. Sadly, this particular server likely won't survive my clients slow shift to Or%@le, SQL Server 2005 is it's last stand. What could actually speed the change over is if the machine it's running on dies; however, SQL Server 2005 should be able to stand the test of time for it's current use.
November 18, 2010 at 1:54 pm
Hi Alvin, I couldn't get the .Net samples I found to do what the VBScript did (and so painlessly I might add). I'd be open to seeing actually working .Net samples based on the VBScript above. I do need to learn this stuff after all.
November 19, 2010 at 9:52 am
Tim Hief (11/18/2010)
Hi Alvin, I couldn't get the .Net samples I found to do what the VBScript did (and so painlessly I might add). I'd be open to seeing actually working .Net samples based on the VBScript above. I do need to learn this stuff after all.
I do recommend learning .NET programming (VB.NET for me), unless you plan on only having simple packages.
I don't have any examples of how to do what you're. Might be time to pick up a good book and start reading. Keep in mind that the best resources for this will not likely be SSIS books.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 19, 2010 at 11:04 am
Alvin Ramard (11/19/2010)
Tim Hief (11/18/2010)
Hi Alvin, I couldn't get the .Net samples I found to do what the VBScript did (and so painlessly I might add). I'd be open to seeing actually working .Net samples based on the VBScript above. I do need to learn this stuff after all.I do recommend learning .NET programming (VB.NET for me), unless you plan on only having simple packages.
I don't have any examples of how to do what you're. Might be time to pick up a good book and start reading. Keep in mind that the best resources for this will not likely be SSIS books.
I used this book to learn VB.NET: Programming Visual Basic 2008: Build .NET 3.5 Applications with Microsoft's RAD Tool for Business
It's a really, really great book with lots of humour, but I'm afraid it is already a bit outdated since it is .NET 3.5. But it should certainly suffice for SSIS programming.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2010 at 11:33 am
Good advice, it's high time I took the .Net plunge!
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply