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

Saving Packages and Custom Component data

I am looking for some advice regarding saving custom component data when saving packages.

For custom "properties", this is not an issue, as saving the package will save these properties. Howver, I also have information for each column (besides the properties that columns provides, like Name and DataType) that I need to save if a package were to be saved, and right now it does not save because I am using my own objects to store the data.

I am wondering as to how I can save this information. I have looked up on serialization, but I would like to know if there is another way besides serialization to save this inforamtion as I'd rather not save this to a seperate file.

ThanksI think I found another method, and this method would make more sense...

There is a topic in msdn about persisting custom objects and implementing SaveToXml and LoadFromXml methods. The only problem is that this topic is vague and not very helpful for me in determing how to use this. If anybody can point me to some good documentation, or that could explain it here that would be greatly appreciated.

I have my own class, and I created my own custom object. I have many instances of this object stored currently in a Hashtable. I want to be able to save this.

Thanks|||Search Books Online for "Persisting Custom Objects" (under SSIS). You will either have to implement ISerializable, or write your own routines to save your custom objects as XML.|||Thanks for your reply.

I actually stumbled on a different solution that I have tested and works great.

Because the data that I wanted to save was for each output column, I created custom properties in the IDTSOutputColumn90.CustomPropertyCollection

These custom properties are automatically saved in the XML for you.

Considering that I've been working with custom properties for the actual component I was a little surprise that I did not pick up on creating custom properties for output columns earlier.

Saving DTS Packages & Copying them to some other server...

I created some DTS Packages through EM on SQL Server 2000. Is there a way I
can generate scripts on those packages and execute the same scripts on some
other DB Server with the similar Database?
Any help in this regard is greatly appreciated.
Thanks & Regards,
RatnaRatna,
you can actually save the DTS package on to the other servers if accessible
in EM. Otherwise you can save them as file and transfer from there.
Quentin
"Ratna Garapati" <rgarapati@.hotmail.com> wrote in message
news:u1zVUPFjDHA.1332@.tk2msftngp13.phx.gbl...
> I created some DTS Packages through EM on SQL Server 2000. Is there a way
I
> can generate scripts on those packages and execute the same scripts on
some
> other DB Server with the similar Database?
> Any help in this regard is greatly appreciated.
> Thanks & Regards,
> Ratna
>|||On the Package Designer, you can do a Save As to another
server
or
you can save the package as Structured Storage File and
then open the .dts file and save it to the same server
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I created some DTS Packages through EM on SQL Server
2000. Is there a way I
>can generate scripts on those packages and execute the
same scripts on some
>other DB Server with the similar Database?
>Any help in this regard is greatly appreciated.
>Thanks & Regards,
>Ratna
>
>.
>|||You can open the existing package in Design Package Mode in the current
server and then Package-->SAVE AS with Location "Structured Storage File".
Give a filename name of the package. You can copy this file to other SQL
Server machine and open this package.
"Ratna Garapati" <rgarapati@.hotmail.com> wrote in message
news:u1zVUPFjDHA.1332@.tk2msftngp13.phx.gbl...
> I created some DTS Packages through EM on SQL Server 2000. Is there a way
I
> can generate scripts on those packages and execute the same scripts on
some
> other DB Server with the similar Database?
> Any help in this regard is greatly appreciated.
> Thanks & Regards,
> Ratna
>|||Thank you guys.
Saving to a structured storage file is my option since I have to move these
packages to the servers at various locations. I tried it and I suceeded in
saving the file.
But how do I open that file as a package in the other server? I looked for
the options but I didn't find one.
Any help is appreciated.
Thanks,
Ratna.
--
"Edgardo Valdez" <edgardovaldez@.hotmail.com> wrote in message
news:079c01c38c57$ac6a31f0$a301280a@.phx.gbl...
> On the Package Designer, you can do a Save As to another
> server
> or
> you can save the package as Structured Storage File and
> then open the .dts file and save it to the same server
>
> Edgardo Valdez
> MCSD, MCDBA, MCSE, MCP+I
> http://www.edgardovaldez.us/
> >--Original Message--
> >I created some DTS Packages through EM on SQL Server
> 2000. Is there a way I
> >can generate scripts on those packages and execute the
> same scripts on some
> >other DB Server with the similar Database?
> >Any help in this regard is greatly appreciated.
> >Thanks & Regards,
> >Ratna
> >
> >
> >.
> >|||1. Open Enterprise Manager
2. Right Click on "Data Transformation Services"
3. Click Open Package
4. Open the package file (e.g, mypackage.dts)
"Ratna Garapati" <ratna.garapati@.sbcglobal.net> wrote in message
news:u5JHHoFjDHA.1132@.tk2msftngp13.phx.gbl...
> Thank you guys.
> Saving to a structured storage file is my option since I have to move
these
> packages to the servers at various locations. I tried it and I suceeded in
> saving the file.
> But how do I open that file as a package in the other server? I looked for
> the options but I didn't find one.
> Any help is appreciated.
> Thanks,
> Ratna.
> --
> "Edgardo Valdez" <edgardovaldez@.hotmail.com> wrote in message
> news:079c01c38c57$ac6a31f0$a301280a@.phx.gbl...
> > On the Package Designer, you can do a Save As to another
> > server
> >
> > or
> >
> > you can save the package as Structured Storage File and
> > then open the .dts file and save it to the same server
> >
> >
> > Edgardo Valdez
> > MCSD, MCDBA, MCSE, MCP+I
> > http://www.edgardovaldez.us/
> >
> > >--Original Message--
> > >I created some DTS Packages through EM on SQL Server
> > 2000. Is there a way I
> > >can generate scripts on those packages and execute the
> > same scripts on some
> > >other DB Server with the similar Database?
> > >Any help in this regard is greatly appreciated.
> > >Thanks & Regards,
> > >Ratna
> > >
> > >
> > >.
> > >
>

Saving DTS Packages

I am building packages to migrating data from Sybase to SQL Server; however, I have problem saving my packages when it is selecting from a large table. It takes a long time for it to save, but if I limit the script to a small subset, it save faster.

Is there an option to disable the SQL Server Enterprise Manager to run the query against the db on a save?You can uncheck the Run Immediately option and just check the Save. This will only save the package without actually running it.|||Sorry, I am new to this. Where is that option?|||What's the source (Access?) And it's across the Network?

Yeah I had that problem...I created a shell empty access db, and it's quick...I thought is was because of the large number of objects, and had to spin through the collection to "find" that table.

But yours sounds different...are you sure it's the volume of data?|||On the Source Tab of the DTS Properties, I added the where clause to look for only one record and it saves immediately. The script is doing a select from a Sybase environment and pump into SQL Server.|||How are you creating the dts package - Import/Export wizard, dts editor, vb ?|||I am building the packages in the dts editor.

Saving a package into a folder in MSDB

Hi

I am trying to load a ssis package into a folder (lets say SSIS packages) that I created in MSDB but its geeting saved in the root (MSDB) not into the SSIS packages folder...I am doing this thru C#...

Have you tried using the Application.SaveToSqlServerAs method? The 3rd parameter is the package path and there you can specify the "\folder\folder1....\packageName" format for your package.

More info about Application.SaveToSqlServerAs can be found at http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.runtime.application.savetosqlserveras.aspx

HTH,
Ovidiu

|||Thanx Ovidiu... I also checked that before and now its working fine...

Save DTS packages to MSDE

Hi,
I'm new to SQL Server. And I have problem moving the DTS packages from SQL server standard edition to MSDE. Is there other way to save the DTS package without access to the Enterprise Manager? And what about ways to edit the DTS packages loaded into MSDE so that it can run properly on the new server?
Thanks.Why not save the packages as files then you don't have any problems.

Does msde include the dts application?

Note that dts is a client application not part of the server.
It can load and run packages which may be stored in a database or in files and create connections to servers to manipulate data.|||The MSDE does come with the dtsrun utility.
So, you mean, if I save the DTS packages from SQL Server 2000 as .dts files, I can run the DTS packages from the .dts files? I think we have to save the .dts into the DTS service in MSDE first?

And another question that may arise is, how to edit the DTS package to make it run smoothly on another server without Enterprise Manager? Any suggestion?
Thanks...|||OK if the dts client is installed then you just need to load the package and run it.
The /F parameter on dtsrun will load the file and run it.

You can also use sp_oacreate which will allow you to change properties of the package before running it.
This will enable you to do anything you did with enterprise manager but not as easily. Save the package afterwards or you can use it to run the package and make any changes on every run.
Probably better to use it to set global variables and use them within the package.

see
www.nigelrivett.com
sp_oacreate Load DTS