Package still referencing old parameters from an old connection

I deleted and created a new OLE DB connection string then set all my connections to that string in my components in my SSIS package however below where it talks about EBN_TEMP1, that's an old database table that no longer exists after I unistalled and reinstalled SQL Server on this box. Why is it still refrencing old stuff? Is there some sort of refresh I have to do on my entire package due to the fact that I

1) Reinstalled SQL Server 2005

2) Deleted an old OLE DB Conenction my package was using (based on an old database that was in the previous SQL Server install) and createad a new OLE DB Connection in my package to point to a new database name after my reinstall of SQL Server 2005

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 1" and "Screen" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 2" and "CaseNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 3" and "BKYChapter" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 4" and "FileDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 6" and "DispositionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 23" and "BKUDA1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 24" and "RMSADDR2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 25" and "RMSCMPNAME_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 26" and "RMSADDR_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 27" and "RMSCITY_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 28" and "RMSSTATECD_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 29" and "RMSZIPCODE_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 30" and "RMSWORKPHN" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 31" and "BKYMEETDTE" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 34" and "RMSCMPNAME_2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [DTS.Pipeline]: "component "EBN_TEMP1" (528)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I don't remember the format of error message, but this string (EBN_TEMP1) is probably the name of data flow component (source, destination, etc), not the connection name. Just look around the package to find an object with such name. Or double click the error message to get editor for this component.sql

Package Configurations

Hi,

I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.

Thanks

With the above in mind, my end achievement would be to have the same packages deployed to my test and live environment at the same time but when I execute the packages in the test environment, it will use the connection string to the test environment and likewise when I execute the packages in the live environment, it must use the connection string to the live environment.

So, effectively, I am going to have a configuration file containing the connection string on both the live and test server. The packages therefore need to point to the respective files. Has anyone does something similar?

|||

Not sure is that is the reason of your issue; but BIDS will pickup changes to the environment variable only after close it and open it back.

The approach that you described in your second post is pretty common when you have 2 environments in separete machines. I don't see an issue on that.

Rafael Salas

|||Okay, sorted that out. Another issue that I have identified however is that the connection string that is now used seems to depend on who you have logged in to SSIS as (serious problem). For example, lets say on my test server, the configuration file is specifying 'Test' as the server name to use in the connection string. When I go to the physical server and get onto SSIS and check what the connection string is, it is correctly using 'Test' as the server name. Now, if I have to connect to SSIS on the test server from my machine and check what server name is been used in the connection string, it is showing the original server name that the package was deployed with (this is different to the server name specified in the config file on the test server). Do you know if this is a bug in SQL 2005 or am I missing something here?|||

Not sure if i am understanding this correctly. Anyway, when you use package configurations for setting up values at run time; they exist just at that time (at run time); whatever value are stored in the package the last time it was saved will be always there.

Rafael Salas

Package Configuration in SQL

I have an OLEDB SQL Connection that has a connection string that is set as and Expression using a User Variable (@.[User::server_name]). I pass the server name as a command line option. The Package Configuration table is being accessed using the setting for User::server_name in the package and not using the parameter entry for it.
The command being issued is similar to:

dtexec /FILE "packagename.dtsx" /SET "\package.variables[server_name]";dbserver

I am wondering if this by design? I would think that all parameter variables would be set prior to any other configuration entries are processed.

Thanks,
MikeHi Mike,

Do you get any warnings or errors with the command line you specified? Is there more than one variable called server_name with a different namespace or at different scope? You can also add the /rep v option and see if there is any other information that might be useful to determine what is happening.

Thanks,
Matt|||Here is the error I was getting:

Started: 11:27:03 AM
Warning: 2005-12-13 11:27:06.54
Code: 0x8002F304
Source: packagename
Description: A problem occurred with the following messages: "Invalid object
name 'dbo.SSISConfigurations'.".
End Warning
Warning: 2005-12-13 11:27:06.54
Code: 0x80012059
Source: packagename
Description: Failed to load at least one of the configuration entries for the
package. Check configurations entries and previous warnings to see descriptions
of which configuration failed.
End Warning

The table dbo.SSISConfigurations does not exist on the server I had set in the package, but does on the server I am passing as a parameter. This was done specifically to test for this type of issue, where when deployed to a client that does not have the server configured in the package.

Thanks,
Mike|||It looks to me that you try to apply configuration from a server that you want to be specified at runtime.

The problem is that configuration happens at load time and before any set from the command line.

The way I see it is to have define din the package a configuration that would set the desired server name in your variable and this configuration is applied before any other configuration.

HTH,
Ovidiu Burlacu|||Thanks for the feedback. You confirmed what I suspected about the order of evaluation between Configuration and Parameters.

I have attempted to run dtexec with /ConfigFile parameter but it errors with unable to locate the configuration file. I put the full path of the configuration file and it still errors:

dtexec /FILE "C:\SSIS\Package.dtsx" /CONFIGFILE "C:\SSIS\TestConfig.dtsConfig" /MAXCONCURRENT 1 /CHECKPOINTING OFF

I am running on IA64 system, so I tried both 32bit and 64bit dtexec programs and they error the same. So not sure where to go from here.

Thanks,
Mike|||The solution you are trying will not work and the reason is the sequence of configurations:
1. Configurations defined in the package
2. Configurations specified in the command line
You need to open the package in the designer and specify the configuration for your variable in the package and make sure that this is the first configuration in the list.

HTH,
Ovidiu|||

I have tried in the designer to set the configuration file, but it has a fully qualified path to the configuration file. So when deployed, it will not match correctly on a that system.

I would think that the system should process all command line parameters prior to loading any configurations. Maybe I should submit this as a wish list item. I just don't see how to override on a deployed system. This seems a lot like the Chicken and the Egg...

Thanks,
Mike

|||Since you can have multiple configurations in a you should have one configuration (the first one) that loads the server name from something that can be setup on each computer that will run packages (this could be an environment variable or a configuration file that will always be in a fixed location). Then the second configuration would be able to work because the first one would be able to already configure the server name.

What you want is problematic because it implies interaction in loading configurations from the utility that loads the package. If this was done then anyone could easily circumvent configuration loading by not requesting the package to load up its configurations. While you could argue the same thing is happening now just by the package not being able to find the configuration it is actually different because in the first case there would be no warning informing you that the configuration was not applies, whereas in the second there is always a warning and therefore you are aware of the problem as opposed to the package just not running and you having no idea why. All this being said please feel free to submit this as a wish list item, I was just trying to give you insight as to why it is the way it is.

Thanks,
Matt

package configuration are ignored

hi everyone

I am putting my connection string in package configuration file, which i create through package configuration option.

But connection is not picking up value where as other varibles are picking up values from same file.

Is there is any other setting that we have to set for connection string.

Thanks and regards

Rahul Kumar

Chek the execution log of the package and see if there is any warning about configurations not taking place. It sounds weird that other variables are picking up the configuration values right.|||

hi

I got it right now.

Actually i was not giving connection string in design time and wanted package to pick it from config file.So package was not running.

Mistake i was making was i wasn't setting delay validation as true.