Saving SSIS package connection passwords

Hello All,

I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.

If you search this forum for "connection password" you'll get plenty of results. Basically, SSIS does not save passwords in connection strings.

Verify that your package property, ProtectionLevel, is set to EncryptSensitiveWithUserKey. If that doesn't work, then you can put the connection string for the Oracle database in a configuration file, of which you can manually update to contain the password. All of this is posted throughout this forum.|||

Hi,

Thanks for your reply. The thing about that is, that the package is owned by the OS user I use, but the Server Agent runs on another profile (System), so encrypting with a user key will make the Agent unable to execute de package. I wil check out the configuratio file thing, though.

|||Then you can do EncryptSensitiveWithPassword and specify the package password in SQL Agent by altering the dtexec command line parameters.|||I ran into your problem and my solution was to change the CreatorName property of the package to the account that SQL Agent runs under, and I also changed the CreatorComputerName to the name of the production server, then it finally workedsql

SAP Infocube

How do I connect to SAP Infocube from SQL Server Integration Services?

I am looking at connectivity to SAP Infocube from SSIS so that I can migrate from SAP Infocube to SQL Server 2005 or Oracle. Based on the connectivity, I am planning to go for SSIS or Oracle WareHouse Builder.

Thanks,

S Suresh

I am moving this to the SQL Server Integration Services forum.|||

I'm not familiar with Infocube. Is there an alternative source that could be used. There are a couple of SAP connectivity options here, hopefully one may work for you -

http://ssis.wik.is/Data_Sources/SAP

|||Once you have the SAP Infocube data on SQL or Oracle, what do you want to do with it ? Guess that will help you decide between SQL and Oracle (e.g. OLAP and Reporting support). If you use SSIS, you can use the Provider mentioned in the SSIS Wiki on SAP Infospokes (you could use Open Hub Services to export data from a SAP BW Infocube to tables).|||

FYI: The SSIS wiki is here: http://ssis.wik.is/

SAP BW Coonection

Is it possible to have a Reporting Services Data Source connect to a SAP BW
cube?I have limited knowledge in this area, but this much I know:
SAP adopted Microsoft's OLEDB for OLAP interface for talking to BW cube.
Look at the following URL:
https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/documents/a1-8-4/OLE%20DB%20for%20OLAP.pdf
You should therefore be able to use MDX to query the cube. If you don't like
writing MDX queries, you can check out Proclarity Reporting Services Add-in
which will enable you to create the queries using a graphical designer. check
it out on Proclarity's website.
So the answer to your question is "most definitely, yes".
HTH
Charles Kangai, MCT, MCDBA
"Dave Morrow" wrote:
> Is it possible to have a Reporting Services Data Source connect to a SAP BW
> cube?

Sample Databases Won't install

When I installed SQL Server 2008 32-bit Dev from the ISO at connect.microsoft.com, I carefully chose to install the sample databases as well. They did not install. When I later enter Add/Remove Programs, to add the Sample Databases, they (correctly) shows as not installed. I change the selection to be installed, but when the Setup Progress window appears it states that no components are being configured. No error message occures.

Is the sample databases available as a separate download so that I can get them installed. I guess Microsoft have done something to them since 2005.

Never mind. I had to scroll down one of the dialog to find a short but precise notice guiding me to http://go.microsoft.com/fwlink/?LinkId=87843|||

Hi,

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4605 is the shortest way to reach the msi downloads of sample databases I think.

Sample Databases Won't install

When I installed SQL Server 2008 32-bit Dev from the ISO at connect.microsoft.com, I carefully chose to install the sample databases as well. They did not install. When I later enter Add/Remove Programs, to add the Sample Databases, they (correctly) shows as not installed. I change the selection to be installed, but when the Setup Progress window appears it states that no components are being configured. No error message occures.

Is the sample databases available as a separate download so that I can get them installed. I guess Microsoft have done something to them since 2005.

Never mind. I had to scroll down one of the dialog to find a short but precise notice guiding me to http://go.microsoft.com/fwlink/?LinkId=87843|||

Hi,

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4605 is the shortest way to reach the msi downloads of sample databases I think.