May 20, 2008 at 1:25 pm
Bare with me I am new to SQL Server 2005 but as a test I migrated one package and I do not see it anywhere in SQl Server Management Studio. I did save a log file and it said it was done successfully. So the question becomes where did it go?
I also opened up Business Intelligence Development Studio and it just opens up Visual Studio 2005 (which i have installed on this machine). I do not see where I can actually look at a package, only thing different I see about VS is I now have the ability to set up an analysis services database.
All my databases are still running in SQL Server 2000 mode. Do I need to upgrade database compatibility first?
I figured convert the packages then convert the databases since the SQL Server migration analyzer pointed out mostly DTS issues and only 1 problem with the SQL database engine that I need to fix.
Matt
May 21, 2008 at 3:40 pm
Well in order for you to be able to execute it or to see the package you just migrated, you have to connect to your Integration Services and you can do that on your Microsoft SQL Server Management Studio. Once you get the you'll get connected to your SSIS and then you will be able to see all the packages that you "installed" in that instance.
SSIS is a service different than SQL Server Database Engine Service and its objects don't really belong to a database they belong to the service. They can point to your different databases but that is a different story.
About the compatibility level, you don't need to change it, you can just migrate it to a structured file and then you can import it to your SSIS service.
Hope this helps 😀 in any way
May 22, 2008 at 7:21 am
That helped. I did not realize I had to actually connect to the SSIS service. Thought it would have been all in one like in SQL Server 2000. I also played around a little bit in the BI Studio, very nice.
Matt
May 22, 2008 at 8:50 am
I created a new package in the BI studio and imported it into SQL Server 2005 and executed the package and gave me some jaw dropping error messages.
OnError,,Safety_Logs,{C2D1039F-7B47-4219-8872-AA61DAD8CF34},{7527C3B9-8E4C-4C4B-BFAD-0064008A79B6},5/22/2008 10:36:26 AM,5/22/2008 10:36:26 AM,-1073450754,0x,SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "AFT Logs ASCII" (1984).
OnError,,ARLNETWORK\matt,Safety_Logs,{C2D1039F-7B47-4219-8872-AA61DAD8CF34},{7527C3B9-8E4C-4C4B-BFAD-0064008A79B6},5/22/2008 10:36:26 AM,5/22/2008 10:36:26 AM,-1073668060,0x,The task "Send Mail Task" cannot run on this edition of Integration Services. It requires a higher level edition.
Keep in mind all my databases are still running in SQL Server 2000 compatiblity mode.
Does this mean, I have to migrate the packages first then up the SQL Level compatibility, then redesign any packages I want in BI Studio?
or
Can I up the SQL Level compatibility then just redesign the packages as needed?
I am making the assumpion that I do have access to the Send Mail Task in SQL Server 2005 Standard Edition? It would not make sense for the designer to let me see things that I can't use in Standard Edition.
Matt
May 23, 2008 at 11:20 am
hhhmmmm that is a weird error for me too :crazy: well for your first question I don't think your sql server compatibility level would be an issue here, because what compatibility level does is just to offer you different sintax (ANSI SQL 89) on sql server and that doesn't affect your sql server database engine, it'd only affect your queries.
About that weird thing that says it needs a SSIS with a higher level to be able to run, apparently it doesn't. Check this websites out
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
SSIS is a great thing if you are using it with SQL SERVER STANDARD EDITION or ENTERPRISE EDITION, but I will check on your errors 😀 but try reconfiguring your Send Mail Task. Good luck.
May 23, 2008 at 11:41 am
Right, I figured out the SSIS errors in the log. It happens if you execute the package from SQl Management studio and don't have SSIS installed on your workstation it fails.
2 work arounds I found. 1) Install SSIS on your workstation or
2) set it up as a job on the server and it works
I imported the package to Management studio from my machine in the bin folder. Not sure if this is the correct way to add it to the server but it seems to work.
Send Mail Task still does not work in Visual studio though. Maybe it has a reliance on Database Mail somehow as I only have the client tools installed and SSIS installed on my workstation. It works on the server like a champ including the Send Mail Task.
Should I install everything on my workstation?
Matt
May 23, 2008 at 12:05 pm
What version of SQL Server do you have on the server?
What version do you have on your desktop?
You will get this error if you load the SSIS package onto the server, then connect to SSIS on your desktop, right-click on the package and try to execute the package.
Once you have verified that your client tools and the server are on the same version, try scheduling the package through a SQL agent job and see if you still get the errors.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2008 at 12:34 pm
I am running SQL Server 2005 Standard Edition on the Server + all the tools except for the client tools.
On my workstation I installed Client Tools and SSIS for SQL Server 2005.
Both are running SP2.
I got the SSIS packages to work ok on the server. I am just having trouble with the BI studio and Send Mail Task will NOT work unless I import the package to the server. It does not throw me any error messages saying it can't use Send Mail Task, it just never fires through BI Studio.
On the server the send mail task fires with no issues. I am at a loss here but its not a big deal though it would be nice to be able to test it locally before putting it on the server.
Matt
May 23, 2008 at 3:41 pm
The situation you describe is really strange for me :hehe: but have you checked other things like your firewall or your smtp server configuration??? You don't loose anything by checking up on them right?
I was interested in checking about SSIS "requirements", I never thought of this but apparently other people did. I found out that you don't really have to have a SQL Server Liscence in order to use SSIS but the liscence for it is as expensive as for the SQL Server DataBase Engine.
Check this out, you may find this interesting too.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1697243&SiteID=1
May 27, 2008 at 7:14 am
Yes everything is setup correctly as if I just execute the send mail task it works but if I execute the entire package somehow the error handler never fires (even when there is an error). I already checked to be sure error handling is enabled and it is(otherwise it would not have worked on the server).
That is interesting about SSIS.
Matt
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply