May 24, 2010 at 7:07 am
When I used Execute Task on the individual objects on SSIS, they all work. But for some reason, when I tried to execute the package, I was given the following error.
I have the following connections:
OLEDB
3 CSV Text Files (1 to read from, and 2 to output to)
It looks like it's complaining about one of the connections having a null value defined somewhere. I tried to open up the .dtsx file but can't find anything strange. Any idea what might have happened?
Not sure if it's related. I did copy the package from another SQL Server.
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Mem1_Mem2_Reconciliation: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at Mem1_Mem2_Reconciliation [Log provider "SSIS log provider for SQL Server"]: The connection manager "" is not found. A component failed to find the connection manager in the Connections collection.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
---------------
May 25, 2010 at 2:27 am
It seems it is has something to do with the logging.
Check if the connection manager used to log is configured correctly.
(it should point to a specific log table in the database. Since you copied the package, maybe an error sneaked in?)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2010 at 5:39 am
Well the only database connection in the pool of connections I've created, is to do a select statement only. I configured the original .dtsx package on the original server to move the files into a different directory, and moved the whole thing onto the new server again. It's working now surprisingly. The only thing I need to change is the SMTP configuration for the E-mail Task. For some reason it has been changed to use internal IDs rather than the name of the SMTP connection.
May 25, 2010 at 5:49 am
SSIS uses internal ID's for its connection managers under the hood.
Normally, copying packages will not harm your connection managers (I do it all the time), but it will screw up any tasks/components that reference them.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2010 at 6:01 am
Mm.... Interesting point about the tasks referencing the connection managers. So how do we design something on one machine, and then deploy it intact onto another box?
May 25, 2010 at 6:47 am
Sorry, I was wrong. (In my mind it was OK, but I wrote it down incorrectly :-)).
If you copy entire packages or if you deploy them to other systems, the connections managers should remain intact, as well as any task/component that uses these connections.
However, if you copy a single task from one package to another, the connection will be lost, even if the connection managers have the same name.
I hope this makes more sense 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 25, 2010 at 7:21 am
Yeah it does and that's what I've observed too. I tried copying individual components which reference connection managers but failed. And the worst thing (perhaps you can let us know why also) is even after having deleted the components that were copied, and dragging the components from the Toolbox locally, the package still fails.
BTW, if I use Shared Data Sources for the packages within VS instead of having the same connection manager for each of the package, how would the package work if I only copy the package into the SSIS package store? Do I need to copy the Share Data Sources separately to somewhere in SQL SVR?
May 25, 2010 at 7:59 am
Regarding the first problem: that's quite odd that the package still fails after removing the tasks. But, with SSIS and its underlying metadata, you never know. Without the exact error messages me or the other people on this forum can't do more than just guess.
Regarding data sources: I haven't used them myself before. I always use connection managers configures through a configuration table. This document gives an overview of data sources and also gives some reasons why not to use them:
http://msdn.microsoft.com/en-us/library/cc671619.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply