pckg config.

Hi,

I have configure our ssis project that will take the connection string from the database

but when i change the connection string so the pckg will take the data from another server with exact tables name it fails.

what do you think the problem is ?

I think the problem is a mistake in the changed connection string or the preparation of the additional server. Perhaps if you gave us the full error message you get, we can make an informed guess?

Can you also be clear about which connection string has changed, one contained within the configuration store, or the connection that points to the SQL Server holding the configuration table?

|||

Hi,

this is the error i get

Message
Executed as user: FLYING-CARGO\dwhadmin. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:48:14 Error: 2007-07-10 12:48:16.00 Code: 0xC0202009 Source: Load AirPort_City_Codes airport_city_codes [31] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name 'export_db_drp.dbo.airport_city_codes'.". End Error Error: 2007-07-10 12:48:16.02 Code: 0xC004706B Source: Load AirPort_City_Codes DTS.Pipeline Description: "component "airport_city_codes" (31)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2007-07-10 12:48:16.02 Code: 0xC00470... The package execution fa... The step failed.

what i've changed is the connection string of my erp db.

|||

what i've changed is the connection string of my erp db which store in my configuration table.

10x.

|||10x i've fix it.|||Could you post the resolution?|||

Hi steam5,

Description: "Invalid object name 'export_db_drp.dbo.airport_city_codes'." draws my attention. If you are executing a query, check if that runs successfully on the SSMS Query Editor after connecting to the ServerName you have changed. If succeeded, use the OLEDB source preview to look for top few rows.

Thanks

Subhash Subramanyam

sql

Passwords in the SSIS configuration file

Thanks to some earlier help from this forum, I have my package running using configuration files and Windows Authentication. However, when I try to execute the package using Sql authentication, login fails for the user i am specifying. When I check out the configuration file, the connection string does not contain the password. I added the password to the connection string, but it does not cone back. I followed the instructions found on the following site:

http://www.nigelrivett.net/DTS/SSISConfigurationFile.html

After trying what was suggested there, I was still unsuccessful with my login attempt.

Anyone know how to set up a package to run using Sql authentication?

Thanks!

Craig

At the time of development, set the protection level of package to EncryptSensitiveWithUserKey, after this sertting the package will store the sensitive information like password in itself.

Atul

Password Protected Excel File

Hi

I'm in need of a bit of assitance here. Basically I am currently creating a SSIS package which works in principle with the exception of my data flow.

On my data flow I am reading an excel file using the excel source, this works fine for a number of my examples but one of the excel files is password protected. This is throwing an error when I try to run the package.

Does anyone know how to read a password protected excel file?

Thanks
Kismet123You cannot access a password-protected Excel file using the Jet OLE DB Provider, period.

I probably shouldn't mention this, but as an interesting tidbit of useless knowledge, the Provider can access the file if it is open at the same time in the Excel application...and without even providing the password. But doing so causes a huge memory leak in the Excel process that's going to bring things down sooner or later.
Q319998 BUG: Memory Leak When You Query Open Excel Worksheet with ADO
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319998

-Doug
|||Thanks for that information Doug. Could you possibly tell me what the Password property is referring to when you create an Excel Connection Manager. I thought it would have something to do with if the connection you are making is password protected.

Kismet

Password Property in ConnectionManager

I'm developing a custom manager in SSIS that has several properties defined. One property is a password string that is visible in clear text in the properties pane. I'm trying to figure out how to create a masked field in the properties pane that will mask the text and not present this in clear text. Can someone send me instructions or code samples in C# for doing this? BOL doesn't provide any information on doing this.Hi,

we're facing the same problem, but haven't found any helpful information about it.

This post doesn't really help with the issue not displaying pwds as plain text.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=887558&SiteID=1

Hope you guys can help.
F.|||I believe you need to implement a UITypeEditor (http://msdn2.microsoft.com/en-us/library/system.drawing.design.uitypeeditor(VS.71).aspx) to handle this type of functionality.

Password Property in ConnectionManager

I'm developing a custom manager in SSIS that has several properties defined. One property is a password string that is visible in clear text in the properties pane. I'm trying to figure out how to create a masked field in the properties pane that will mask the text and not present this in clear text. Can someone send me instructions or code samples in C# for doing this? BOL doesn't provide any information on doing this.Hi,

we're facing the same problem, but haven't found any helpful information about it.

This post doesn't really help with the issue not displaying pwds as plain text.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=887558&SiteID=1

Hope you guys can help.
F.

|||I believe you need to implement a UITypeEditor (http://msdn2.microsoft.com/en-us/library/system.drawing.design.uitypeeditor(VS.71).aspx) to handle this type of functionality.

Password Issue

Hi All,

We are developing a package in SSIS where we are using a connection to Mainframe AS/400 using ODBC. The problem is that when we fill in the User ID and Password and check the remember password textbox this seems not to be taking effect.

What this triggers off is that whenever we open the package a NULL password is sent to the AS400 server and it locks the ID.

Any ideas what to do

Manish

By default, password is not stored in the .dtsx file. You should search BOL for ProtectionLevel property.

-Jamie

|||

I don't belive teh DB2 provider stores passwords. Not used it, but seen plenty of posts about it doing this. I'd say this is not a problem, and agree with jamie, you don't want to save sensitive data anyway.

The better solution alll round is to use Configurations. They help with deployment issues, but work perfectly well in development as well. See SSIS - Configutaions menu, and have a read in BOL.

|||

Hi,

We tried the configuration option, putting the config data in a SQL Server table. It works fine. But if we copy the job and rename it and try to open it, it again locks the id:)

Password Encryption in XML File

I am unable to encrypt password of sqlserver database in XML file by Package Configuration in SSIS.

I think that is not possible (though it might be possible if you develop something). According to the SSIS help you can either save the config file in SQL Server or use Windows access control lists to protect access to your config file. See ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.de/extran9/html/2e3ddea9-5289-4289-a70e-11c018f34977.htm|||Yes in config file i have to put the password manually in config file.If i choose only connection string property in configuration wizard then it is working fine.But When i reset the server password then it fails.So i have to give the password.But unable to encrypt it for security reasons.

password cannot be saved

Hi, there;
I have a SSIS package which has SQL2005 as its destination. When I setup the connectionmanager, I'd like to use SQL authentication. But I found that the password field cannot be saved (Checkbox "Save password" is selected).Everytime I open the this package's connectionmanager, that password field is cleard. The result is that my C# application that loads that package to execute failed with "Login failed..."

Did I do something wrong or anybody know how to save that password field?

CheersThe password in a SQL-authenticated connection is considered sensitive data. SSIS wants to encrypt any sensitive data in the package (see ProtectionLevel). By default it does this with your user key. Unless you've changed the ProtectionLevel, you should be able to save the package and reopen it (as the same user) and have it preserve your password. If you're going to be opening it from an application, your best bet for ProtectionLevel is probably to use the "EncryptSensitiveWithPassword" option, which uses a password to encrypt the sensitive data. The application (and any users) will need to supply this password when opening the package.

A good alternative to storing sensitive data in the package is to read the password into the package using a Package Configuration mechanism.
|||

Thanks.

Do you mean I need another password to open my package?

Where can I set the "ProtectionLevel" in the package?

|||See the link that JayH included.|||

Thank you.

I will try.