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 SSIS package

Hi,
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!
SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>

saving SSIS package

Hi,
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>

saving SSIS package

Hi,
Let me know if this is possible...
We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
network.
We have a SQL 2005 server, that is also our web server, out in the DMZ.
We are trying to copy some tables from a database on our SQL 2000 cluster,
out to the SQL 2005 server. We want this to happen every night.
So from his workstation, our db admin, installed the SQL 2005 tools, started
creating an SSIS package, didn't save it, and it copied the tables out to
the server, no problem. However, he created the SSIS package again, this
time tried to save it to the SQL2000 server, so that he could schedule it to
run every night.
This makes sense that you cannot save an SSIS package to an SQL2000 server.
How do we get around this?
Note: the web server cannot be allowed to initiate the table copy. In
other words, the data can only be Pushed from the inside, not Pulled from
the DMZ. So what is the best way for us to copy tables from SQL2000 to the
SQL2005 server?
Thanks in advance!SSIS packages differ a lot from DTS ones, so you would need a completely
different tables in SQL 2000 msdb. You can save the SSIS package into file
system of the SQL 2000 server and use SQL 2005 DteExec command-prompt
utility (replaces DTSRun) to execute the package from the SQL 2000 server.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:uHFefvEHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Let me know if this is possible...
> We have a SQL 2000 SP3a cluster on Windows 2003 Server that is inside our
> network.
> We have a SQL 2005 server, that is also our web server, out in the DMZ.
> We are trying to copy some tables from a database on our SQL 2000 cluster,
> out to the SQL 2005 server. We want this to happen every night.
> So from his workstation, our db admin, installed the SQL 2005 tools,
> started creating an SSIS package, didn't save it, and it copied the tables
> out to the server, no problem. However, he created the SSIS package
> again, this time tried to save it to the SQL2000 server, so that he could
> schedule it to run every night.
> This makes sense that you cannot save an SSIS package to an SQL2000
> server. How do we get around this?
> Note: the web server cannot be allowed to initiate the table copy. In
> other words, the data can only be Pushed from the inside, not Pulled from
> the DMZ. So what is the best way for us to copy tables from SQL2000 to
> the SQL2005 server?
> Thanks in advance!
>

saving package with Protectionlevel set to serverstorage fails

I am getting this when I try to save my package. Any Ideas?

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.

Well, as the message says - rely on server storage can't be used when saving to file. It works only when saving to SQL Server (otherwise you would have a file with plain text passwords in it).|||I am getting the same error when I am working on the SQl Server and trying to save it. Is there a security key I need to switch on in the SQl Server to use server storage?

saving package with Protectionlevel set to serverstorage fails

I am getting this when I try to save my package. Any Ideas?

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.

Well, as the message says - rely on server storage can't be used when saving to file. It works only when saving to SQL Server (otherwise you would have a file with plain text passwords in it).|||I am getting the same error when I am working on the SQl Server and trying to save it. Is there a security key I need to switch on in the SQl Server to use server storage?

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822
|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

sql

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

Saving DTS Package in Meta Data Services Error

I am running SQL Server 2000 SP3 and I am trying to save a DTS package into Meta Data Services and I am receiving the following "Package Error"

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: General Error -2147217355 (80041035).

I have searched for this error and I cannot find anything related to it. Also, I saw some of the comments about right clicking "Data Transformation Services" and checking the box for allow save to Meta Data Services, however, I do not see that checkbox to allow for this. Has anyone else had this problem and resolved it? I'm beginning to get very frustrated with it.May try in http://www.sqldts.com for any refernece on this behaviour.

HTH

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...

saved package gets corrupted

Hi,

I am facing a problem. I have custom data flow transformation.We have saved a package using component's earlier assembly version. Now when we install later version of the component the saved package fails to open. If I try creating new package it succeeds.

Error message :

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (153)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package.dtsx 0 0

We tried overriding "perform upgrade" method but still I am facing the same issue.

Dharmbir

There is a good sample on how to overridfe PerformUpgrade here:

http://msdn2.microsoft.com/ko-kr/library/ms345168.aspx

The minimal thing that you need to do in the PerformUpgrade method is to upgrade the version (see the last line of code in the link above)

Thanks,
Ovidiu

|||

Hi,

I implemented this, still I am getting the error.

If I have earlier version of the Component in the GAC it does not give me the error and component runs. But if I have only the latest version of the assembly in the GAC it gives me error, though I have gaced its policy file also.

thanks

Dharmbir

|||

You can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen

sql

saved package gets corrupted

Hi,

I am facing a problem. I have custom data flow transformation.We have saved a package using component's earlier assembly version. Now when we install later version of the component the saved package fails to open. If I try creating new package it succeeds.

Error message :

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (153)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package.dtsx 0 0

We tried overriding "perform upgrade" method but still I am facing the same issue.

Dharmbir

There is a good sample on how to overridfe PerformUpgrade here:

http://msdn2.microsoft.com/ko-kr/library/ms345168.aspx

The minimal thing that you need to do in the PerformUpgrade method is to upgrade the version (see the last line of code in the link above)

Thanks,
Ovidiu

|||

Hi,

I implemented this, still I am getting the error.

If I have earlier version of the Component in the GAC it does not give me the error and component runs. But if I have only the latest version of the assembly in the GAC it gives me error, though I have gaced its policy file also.

thanks

Dharmbir

|||

You can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen

SaveCheckpoints=True affects Recordset

I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:

Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable

adp.Fill(dt, Dts.Variables("CountryTable").Value)

It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.

Note: I have SP1 installed.

That's weird for sure. I'll need to investigate.

When we are writing checkpoints, we also write out the value of variables to the checkpoint file - so that we can start the package again in the correct state. I can imagine an error when serializing an object variable could cause some checkpoint funkiness, but as I say, it needs more investigation.

Donald

|||

Donald,

I thought that Object variables don't get written out (unless the behaviour has changed in SP1).

-Jamie

|||

They're not and its documented in BOL now.

I hade the same issue, that my package worked up until I added checkpoints at which point it wouldn't run properly. Thought I had raised a bug but haven't

Save to sql server failed no description found

I want transfer data from excel file to sql server.

I created a package in sql server managament studio using export import wizard and i gave the option "sql server" as the package location.

At the final stage of the wizard "Save to sql server failed no description found" error is coming. pls give me a solution.

thank u

I could not reproduce this issue. Can you try saving this to the file system and let us know if you have any issues?|||

try to run

Regsvr32 msxml3.dll
Regsvr32 msxml6.dll

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=748066&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74140&SiteID=1

save package to SQL server failed, no description found

Hi I have an instance of sql2005 on my machine and connected to a sql2000
server. I tried using the export wizard to create a package with the source
being tables from a database on the 2000 server and the destination being an
excel file on my machine. I also selected save package on the 2005 server.
When executing it makes it to the save portion but I then get the error, Save
to SQL Server failed, no description found!
Thanks.
--
Paul G
Software engineer.Hi Paul,
Please ensure that the user account in SQL who runs the job has adequate
privileges.
"Paul" wrote:
> Hi I have an instance of sql2005 on my machine and connected to a sql2000
> server. I tried using the export wizard to create a package with the source
> being tables from a database on the 2000 server and the destination being an
> excel file on my machine. I also selected save package on the 2005 server.
> When executing it makes it to the save portion but I then get the error, Save
> to SQL Server failed, no description found!
> Thanks.
> --
> Paul G
> Software engineer.|||ok I think that may have been the problem. Also I changed the authentication
from windows to mixed mode so will give it a try.
--
Paul G
Software engineer.
"Amol Lembhe" wrote:
> Hi Paul,
> Please ensure that the user account in SQL who runs the job has adequate
> privileges.
> "Paul" wrote:
> > Hi I have an instance of sql2005 on my machine and connected to a sql2000
> > server. I tried using the export wizard to create a package with the source
> > being tables from a database on the 2000 server and the destination being an
> > excel file on my machine. I also selected save package on the 2005 server.
> > When executing it makes it to the save portion but I then get the error, Save
> > to SQL Server failed, no description found!
> > Thanks.
> > --
> > Paul G
> > Software engineer.

Save As dialog

Hi,
Is there any way to prompt as Save As dialog in a DTS package that allows the user to pick the path and filename of the document to be exported?
Thanks.Yes, on the top right hand corner, click Package and select Save As.|||hehe.. maybe i wasn't too clear on what i'm trying to do.

I'm calling this package from a web page. What I want this to do is to pop-up a save dialog so that the user can choose the path and filename of the exported file to be saved on their computer when the package runs.

But this brings up another question... technically, the web server would be calling this package so it would save it on the server's drive rather than the client. Is there any way to tell it to save to the client or would I have to save it temporarily first on the server than send that to the client?|||We do this on our internet site. We use ADO, save the data to the ADO stream as XML, transform it using an XSL (to format the data into comma-delimited) and then write the results into the Response object.

Since the DTS package is being run in the context of the web server, I would think that you are going to have to save the file first, then http it to the user's client.

hmscott|||well we've got it figured out that we're going to create a temp file on the server, have the page link directly to that file which will prompt the user to save since it's an excel file... then after the client save/cancels, delete the file.

now my problem is.. how do i call a package using java? i've seen NO code to do this...|||Please don't take this the wrong way, but usually I find that when a problem like this starts leading to more and greater complexity, then there is often times a simpler, more elegant, better performing (and easier to support) solution.

I dunno the specifics of your situation and I am not trying to be judgmental, but perhaps by retracing your steps back up the ladder, you may find an easier way down...

hmscott|||Originally posted by hmscott
Please don't take this the wrong way, but usually I find that when a problem like this starts leading to more and greater complexity, then there is often times a simpler, more elegant, better performing (and easier to support) solution.

I dunno the specifics of your situation and I am not trying to be judgmental, but perhaps by retracing your steps back up the ladder, you may find an easier way down...

hmscott

Yea, what he said. Sometimes DTS can be more work that it is worth. It looks like you are trying to figure file delivery issue. Your idea of creating a temp file may work.

To execute the DTS package from jsp, I would suggest looking up how to execute the package using DTSRUN and the command shell in java.|||sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...
sproc...bcp...unc...format file...t-sql...|||I'm rather 1 dimensional....