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 Fail connections on moving to server

I am working with the ssis packages.I am working in the development enviroment.Once i place them on the server all my connection to text files fails.I dont want to sit and Keep changing with respect to server again and again.

What is the best way to set connection like for example:in my development it can be on e:\data folder

But on server F:\Data

With me changing again and again, can this be set within the package in the development environment.

Please le me know.

Use a package configuration to populate a variable with the location of the files. Then use an expression on the connection manager to combine the file location variable with the hard-coded filenames.
|||

Would you please explain in little more details

Thanks in advance

|||If you aren't familiar with Package Configurations, you should read the topic in Books Online. You can use them to set the connection string on your flat file connection managers, as Jay suggests.

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 please help

I would like to set package configuration.But dont know how to.

What i want to achieve is that i want to change connections based on test ,development and production environment of the same package...

How can i do that without me changing the connections manually to each task based on environment..

using package configuration i dont know how to...Please let me know.

http://www.sqlis.com/26.aspx|||

http://msdn2.microsoft.com/en-us/library/ms167153.aspx

http://rafael-salas.blogspot.com/2007_01_01_archive.html

|||

>>http://rafael-salas.blogspot.com/2007_01_01_archive.html

Form this link is it like i have to create one table in sql server database which has connection string with a particular connection

so is this connection applied to all the tasks like lookups ,oledb commands,oledb destinations etc...and in my cast the text fine connection dosent change at all....so what do i do...

|||i dont find InitialCatalog & ServerName properties.. ....from the steps in this tutorial........|||FYI - you don't create the table manually, SSIS will do it for you. (That is if I'm assuming correctly what you are talking about and what Rafael wrote.)

When you create a new SQL Server based package configuration, it will create the table for you if it doesn't exist.

(I can't get to Rafael's site at the moment as we have a mis-configured proxy that blocks his blog page......)|||

sureshv wrote:

i dont find InitialCatalog & ServerName properties.. ....from the steps in this tutorial........

Those are found on database connections.|||

sureshv wrote:

>>http://rafael-salas.blogspot.com/2007_01_01_archive.html

Form this link is it like i have to create one table in sql server database which has connection string with a particular connection

so is this connection applied to all the tasks like lookups ,oledb commands,oledb destinations etc...and in my cast the text fine connection dosent change at all....so what do i do...

Phil is right, the table is created by SSIS, if it does not exist. Every row in that table represent a configuration value to be applied to a specifc property in a package's object. A row in that table looks like

ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType DBConnection-MARINER_EDW Data Source=xxxxx;Initial Catalog=xxxx;Provider=SQLNCLI.1;Integrated Security=SSPI; \Package.Connections[MARINER EDW DS].Properties[ConnectionString] String

Where ConfigureFilter is what SSIS uses to know which row to pick from the table; ConfiguredValue is the value to be used when overwriting the property value; PAckagePath will tell SSIS which property will be override.

In your case that property might be the connection string of a connection manager. Then all other objects/tasks using that connection manager, like a lookup component, should pick up the configuration value from the table.

Does this make sense?

|||

thanks phill...i was able to view his page though...

Let me explain my situation clearly...

here goes the process...

I have a package which has two task

1. execute sql

2. data flow

With in data flow..

i have a flat file connection to my text file

i update or insert records to my destination.

i have lookups,oledb commands,oledb destinations...

so far i am using oledb command...

when i insert or update what i do is insert few fields of incoming record in one table get the identity field number and insert rest of the data and the identity field to another table...which uses connection1

now then i take identity fields of the records inserted from above two tables and insert them to another table in a different database with different connection 2...

all these are in the demo environment...

Now my question is i want to using the same package to run but one change which i want to make is change the connection1 and connection2 to connection 3 and coneection 4.Is that possible using package configuration...

please let me know...

|||

sureshv wrote:

Now my question is i want to using the same package to run but one change which i want to make is change the connection1 and connection2 to connection 3 and coneection 4.Is that possible using package configuration...

please let me know...

Yes, this is what we've been illustrating to you.

You need to chose the database connectionstring property (that's all I use) of the connection manager object you are using in your data flows. Storing the connectionstring property in the SSIS package configuration table will allow you to change the connections whenever you wish.

Note, if you use the same connection manager object in your package configuration setup as you do in your data flows, this isn't going to work -- it will never change from the server defined in the connection manager setup. You should create a separate connection manager object for the package configuration table and store its configuration in an XML config file. (Again, that's what I do.)|||

It am trying to get the point but it would be great with an example..

Ok one last bit of confusion here

i have configured as u suggested me but dosent seem to work...Let me make it clear on what i do...

Lets take two conections

Connection 1

Connection 2

These are used in the data flow

So in the control flow i am setting it this way...let me know if its right

Package configuration->enable package configuration->Add

Configuration type->xml file

Configuration File->file.dtsConfig

with in select properties to export

under Connection Managers->should i select connectionstring of connection1 and connection 2 or should i select connectionstring of connection 3 and Connection 4

It would be very helpfull if u let me know about it...

Thanks in advance...

|||

sureshv wrote:

It am trying to get the point but it would be great with an example..

Ok one last bit of confusion here

i have configured as u suggested me but dosent seem to work...Let me make it clear on what i do...

Lets take two conections

Connection 1

Connection 2

These are used in the data flow

So in the control flow i am setting it this way...let me know if its right

Package configuration->enable package configuration->Add

Configuration type->xml file

Configuration File->file.dtsConfig

with in select properties to export

under Connection Managers->should i select connectionstring of connection1 and connection 2 or should i select connectionstring of connection 3 and Connection 4

It would be very helpfull if u let me know about it...

Thanks in advance...

You should choose the string connections you want to change (eg. from dev to prod) at run time; so if you only want to change 1 and 2; that should be your selection...

|||so in the package configuration i select connection string of connection 1 and 2...but it dosent work....it does not go to insert in the demo database instead it inserts into dev only...|||

Ok, something is not properly set up.

Just to recap; how are you implmenting package configurations? Using only an xml file? or using a SQL table-xmlfile combination?

either way; make sure the configuredValues points to the DEMO env (the connection string is correct).

Also, check the log or progress tab when running the package to see if there is any warning about package configurations not taking place....

|||

ok rafael...

it dosent seem to work at my end...

let me make myself clear...

Under control flow i have data flow task and with it i have oledb destination right now the destination tbale connected is dev environment

i have one more connetion setup which is demo environment but right now none of my tasks uses it..

Now i set up package configuration

i use xml file

i select the connetion string of the demo environment...

now i run my package...

i dont have any error or warning

the data gets stored in the development...is this the way i got to set..

Please help me out...

Package Configuration for Oracle Connections

Hi there,

I created some configurations for my package as Jamie described in http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx
for my Connections. I use ADO Connections as well as OLEDB Connections for Oracle (MSDAORA).

Everything works very fine, until I store passwords in the file. Yes I know, I shouldn't, but
the main idea was to keep the machine-dependent data in an external configuration file,
so you don't need to re-build your package if you want to put it onto another
machine. This scenario is only possible, if the passwords are the same for the two machines, but in my case the passwords are different (even the user which isn't the prob).

The point is: Storing passwords in a configuration file works for OLEDB Connections to
an SQL provider but NOT to the Oracle provider mentioned above.

Any suggestion would be appreciated
Fridtjof

Hi,
If you have 2 different machines then that means 2 different connection strings which means 2 different configurations. You can't use the same configuration for both.

[As an aside, you can store multiple configurations in a single XML configuration file.]

-Jamie|||Hi Jamie,

you're right that you have to use two different configuration files.

In fact I do have two different configuration files, but it seems that the pwd for the
Oracle connection is not read when the package is opened for each of that configs.

In contrast to that, the pwd I have stored for the SQL-Connection is beeing read properly.

So... :(
|||Friedel,
I am able to change the password in the config file for an ADO oracle connection. I have not used theOLEDB Connections for Oracle (MSDAORA) however. Although I do not do this in my configs normally, so I tested it through VS not in a server environment. Does it work for you when testing through VS?

Adrian
|||Hi Adrian,

thanks that helped. I tried it with an ADO Connection. That worked!

By the way: Weren't there some posts about a performance issue concerning ADO.ORA vs. OLEDB.ORA Connections?

Greetings
Fridtjof
|||Friedel,
Yeah I had posted a question about performance a few months back...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87417&SiteID=1

Since then I've had the best performance with ADO for Oracle, however I don't have any concrete tests to site for this, just an observation. Plus the added benefit of using expressions on the datareader source in a dataflow(which I believe can only use ADO), gave me some dynamic flexibility as well.

Who knows, maybe some of the MSOFT guys have some concrete info on Oracle performance now with different providers?

Adrian
|||

Friedel wrote:

Hi Jamie,

you're right that you have to use two different configuration files.

In fact I do have two different configuration files, but it seems that the pwd for the
Oracle connection is not read when the package is opened for each of that configs.

In contrast to that, the pwd I have stored for the SQL-Connection is beeing read properly.

So... :(

I didn't say that that you have to use 2 different configuration files - I said you have to use 2 different configurations. They can both go in the same .dtsconfig as long as all packages that use the file contain all the objects referenced by the configurations with it.

I am using OLE DB Connection Manager against oracle and it works fine with the password stored in the config file. here's the connection string that I'm storing:

Data Source=ssafeukp.WORLD;User ID=*****;Password=*****;Provider=MSDAORA.1;Persist Security Info=True;

-Jamie
-Jamie

Package Configuration

Hello,

I'm developing some packages in SSIS and I need to implement a configuration file to load the server's connections and other parameters, so I can use this same connection for all packages.

Have anyone a step by step to perform this configuration ?

I exported a Connection Manager to a .dtsConfig file, but I don't know how to use this config file.

Any help is welcome !

Thanks,

Guber

As long as your package knows about the configuration (which it will do because that is where you created it) then it should just work!

Try editing the config file to contain some nonsensical value and run your package again. If it doesn't do what it is supposed to then you know the file is being used.

-Jamie