After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?
Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:
I currently have logging enable on several of my packages. However, we are still in development of our packages and are reaching upwards of 100 and logging will eventually need to be active on all of them. In production, there will still be a development server and a production server, both with different server names and user id/pwd.
I am looking for a way to dynamically change the logon information for the logging so that we do not have to have someone go through and manually change the options. I have tried using Dynamic Properties Task, but this only works on the 2nd run of the package.
-----
As a second question: can anyone explain to me why the errordescription field in sysdtssteplog is cut short?
Hi - Im migrating packages in dts 2000 to SSIS 2005 using package migrator wizard. It loads a CSV to a table in DB. I need to change the server and file location paths after migration. While changing this, im encountering error - "the acquireconnection method call to connectionmanager failed
One issue that we are facing while migrating our dts packages from development to test is that in all our DTS package tasks, every object used is prefixed with the database name. For eg. in a Data Transformation task the Destination object has the prefix dw_dev.dbo.dw_user. This is causing problems as our database name in development and test is different and our packages are not running.
I understand that one can change this property in the Set Connection Task by changing the Source/Destination Object property to the new databse name and running the task. However that would involve changing the source and destination names in all the data pump tasks in all packages.
Is there anyway to copy all the DTS packages and jobs in one step from one server to another. I usvally copy one DTS package at a time. As our packages count grown bigger, it became a painful process. If anyone knows a way to copy these packages and jobs in one then it would be very helpful.
I need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network
i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it
i would like an automated way if possible
i tried tools from sqldts.com, wrote my own, etc... none of them work right
I have been tasked with migrating/recreating our DTS packages from our old 2000 server to our all ready existing and in production 2005 server.
I recently managed to create a simple SSIS package that connected to an Oracle database and dumped the data into the local MS SQL server. That is the extent of my knowledge of SSIS packages.
I have done a little bit of research and so far it sounds like it will be a pain in the butt to do the migration.
One thing I have read from a SAMS book is that migrating a DTS package that uses ActiveX will not work but then later on in the chapter it suggests that there is a possibility that migrating a DTS package that uses ActiveX could work. Which is it?
Can someone give some general advice on how I should go about doing this?
I have been tasked with migrating/recreating our DTS packages from our old 2000 server to our all ready existing and in production 2005 server.
I recently managed to create a simple SSIS package that connected to an Oracle database and dumped the data into the local MS SQL server. That is the extent of my knowledge of SSIS packages.
I have done a little bit of research and so far it sounds like it will be a pain in the butt to do the migration.
One thing I have read from a SAMS book is that migrating a DTS package that uses ActiveX will not work but then later on in the chapter it suggests that there is a possibility that migrating a DTS package that uses ActiveX could work. Which is it?
Can someone give some general advice on how I should go about doing this?
Hi, I upgraded several of my SQL 2000 database to SQL 2005 databases. I'm in the process of migrating over the DTS packages over to SSIS packages. I'm using the DTSMigrationWizard utilty to do this migration. When I started testing the SSIS packages I've discovered that not all the steps were being excuted. So I've noticed on several of the packages not everything migrated over. Such as connections to oracle and some of the task as well did not migrate over. Is there a way to migrate the entire DTS package to SSIS or do I have to re-create the entire package from scratch or is there a better way to get the SQL 2000 DTS packages over to SQL 2005 and have them run successfully?
I have created a SSIS package to load data into a destination table.
Now I cheked that package into main server. Then I tried to change path for both source and destination connection managers. I'm not sure whether I can do that. But now what I want is load data into a data base which is in another server (Perforce).
Can I do this? Or Am I doing smthing wrong? If so, Which is the correct way?
I am working on a migration project of my database(lets name it DB1), from SQL 2000 to SQL 2005. I have some DTS packages in my SQL 2000 database which also needs to be migrated to SSIS. These DTS packages currently interacts with other database(lets name it DB2) which is also in SQL 2000 and which does the data transmission. The real issue is that the other database(DB2) also has some DTS packages which also communicates with DB1 (the db to be upgraded) and so when i migrate my DB1 to SQL 2005, i will have to change my DB2 packages also (although the change will be minor). Now considering that in future I might also migrate my DB2 to SQL 2005 I wanted to know what will be the right approach to follow. One is to modify the DTS packages of DB2 to accmodate the change in connection and the other is to migrate the DTS packages of DB2 also to SSIS.
I have migrated several (7) DTS packages to SSIS in a development environment. In this environment these packages all work as designed. I am now in the process of placing the .dtsx files on the production server and scheduling jobs around them.
I am encountering problems in 3 of these 7 packages. I get the following error:
Message The job failed. The Job was invoked by User <domainuser>. The last step to run was step 1 (Create and Transfer Credentialling Data). This is not what I would call very informative. I have opened the dtsx files in question in BIDS on the production server and each has the following error reported:
Error loading <name>dtsx; Failed to decrypt protected XML node "PassWord" with error 0x8009000B" Key not valid for use in specified state." You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. I have looked at the package setting "Protection Level" and all seven packages have the same setting: EncryptSensisitveWithUserKey Each of the connections within the packages have been defined to utilize Windows Authentictation. And the jobs are set to run under the SQL Agent Account. What I find most puzzeling is that each of these packages are defined exactly the same and some work correctly and others do not. Thanks for taking the time help me with this issue.
Below is a migration plan that I've compiled to migrate SQL 2000 DTS packages to SSIS 2005. Once these DTS packages have been migrated i will need to create a job and schedule them in SQL 2005.
I would appreciate and feedback or questions on this migration plan.
Migration DTS 2000 packages to SSIS 2005:
1. Will need to save the current production DTS package as structure storage file. We do not have a UDL file. We set the data connections within each DTS package. 2. Go to Sql 2005 - ManagementLegacyData Transformation Services - right-click and open previous saved structure storage file. 3. Modify the DTS data creditentials to reflect the SQL 2005 connection data. Modify any SQL 2000 MAPI settings to utilize SQL 2005 new database mail. Save the package on SQL 2005. 4. After the modified DTS package has been updated and saved on SQL 2005, save this file as a structure storage file. 5. go to BIDS. Create a new SSIS project. Right-click on SSIS packages and select Migrate DTS 2000 package. This will migrate over the DTS 2000 package with the updated SQL 2005 data creditentails. 6. click on the package properties - protectionlevel and change it to dontsavesensitive. 7. right-click and select package configurations..., select to store data creditentials in xml format. 8. right-click on execute DTS 2000 package task, select Edit... and click on Load DTS2000 package internally. This will embed this task into the new SSIS package. Test the package. Continue if successful. 9. Use SSIS deployment functionality to move the package over to SQL 2005 Integration Services. Right-click on package and select Run Package, if successful, create a job and schedule it to run on SQL 2005 Agent. 10. When creating the Job under SQL Agent, change the Owner: of the job to reflect the owner of the new SSIS package. Schedule the job.
I've just coming up to speed on SSIS 2005. Therefore, this is what i've been able to piece together up to this point and I'm looking for some industry advice/feedback on whether or not this is a good migration plan. I need to provide a migration plan to management by 2/18. Thanks
I have a requirement of logging the failure and the error of a job executing a SSIS package to text file. However, when I go to job step -> Advanced, in the section "SQL Server Integration Services Package" where that info to setup logging is, it is blank and just says:
"There are no options for this command type"
Any ideas?
I thought that this might not be supported in Standard Edition as I am running standard edition. Then I checked on two other servers, where I have Enterprise edition with SP2. On one of the installations, I am able to give the log file, the other one still shows,
"There are no options for this command type"
What is the problem? Both of them I am using the sysadmin role account. I have proxy account created on both the SQL instances. I don't think this is the problem, but still I checked this.
I recently came across a DTS made by an experienced DBA and was impressed by the use of Dynamic Properties DTS Task used. As I understood the DTS was generic and if I'm not mistaken, can be easily transferred to another server/machine on an AS IS basis and without having to change any of the properties (server name, login, password etc.) for the source or destination server.
This seems to be a really neat feature.
I tried to put this to use but am having problems regarding how to proceed... Unfortunately I have not been able to find any article either which addresses this particular request and takes a novice step by step so that this feature can be used.
We€™ve got almost 250 old dts packages which simply loading data into Sql tables from plain files or at the reverse point. Most of them are defined with fixed fields and its fixed positions one after one. We don€™t want to migrate them using Import wizard, on the contrary we€™re producing them from the beggining taking advantatge of SSIS architecture to the full. And now, we€™re trying to imagine how to migrate automatically that valuable info from Sql Server 2000 to Sql Server 2005 without efforts€¦ You know, any program be able to move that detailed info to SSIS.
So we would avoid to select again all these positions per each file -very tedious and we're lazy
I don€™t see how except, of course, migrate them directly
Let me know if you need further explanations or more clarity on that.
Hi, DTS now has a dynamic properties task in sql 2000 which at a first glance is pretty cool. However i do not know if it would sort out a particular issue I have.
I need to have a generic DTS package that pulls data from different locations based on the project selected by a user. The source is a btrieve database The name of the tables change with the project, so for example the F24 project would have its source table named F24TACT and the IFO source would have its source table named IFOTACT.
I need to be able to dynamically retrieve the table name from a local SQL table and assign this name to the data pump for the data extraction..
I don' t know if this is achievable in DTS. I can retrieve the value into a look up variable but how can i set it at runtime ?
Hello,I am building a packge where an FTP task needs to pull down a singlefileevery day from a specific location. The location will only have theone file.The file name will be different every day. A sample of one of the filenameslooks like this:CDNSC.CDNSC.SC00015.04012007The file names will be different every day, as the last eight digitsrepresent the date of the data in the file. The source files will belocatedin a subdirectory called 'outgoing'.My first approach to this has been to use a Dyamic Properties Task tosetthe SourceFileName of the FTP Task. I tried using a sting GlobalVariableexpressed as *.*.*.* thinking a wildcard would work, but it didn't.What approaches can I take so that the Source Filename in the FTP taskwilldymanically update each day to get the one file?Thank you for your help!cdun2
Im using embedded SS2000 DTS packages in SSIS. These legacy packages use Dynamic Properties Tasks to pick up connection settings from a named ini file. The name of that ini file needs to change occasionally. To date the only way we have of doing this is resetting all the dynamic properties to point at the new ini file. Unfortuntely there are a lot of connections and properties and there is no budget to recreate these packages in SSIS. There must be an easier way?!
Does anyone know how to quickly change Dynamic Properties Tasks to look at a new ini?
I have a dts package that currently uses a dynamic properties task to set the values of global variables. Each variable is based on the value of a query to the database.
I am in the process of migrating this dts package to SSIS but cannot find an equivalent function. I have looked at property expressions but cannot get this working the same way.
I'm currently trying to convert over packages from SQL 2000 to SQL 2005. The biggest obstacle at the start has to do with converting my "Dynamic Properties" control. I use it to read an .ini file and load the user name and password to my connections. With 2000 it's nice and easy 1 file 3 lines. While trying to convert it I€™ve had nothing but problems. I've tried the Registry Entry but it forces everything into the Current User and I can't use that I need local machine. There is no documentation and I can't have a xml file with 1000 different username and password settings because 1 person has to update that file with the new passwords. Any help would be GREATLY appreciated.
Hi, After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?
I have recently prototyped a system, in which I use meta data to build a package of execute package tasks.
Essentially, in a Script task I do the following:
1) Create a new package in memory.
2) I add variables, logging to this package.
3) To allow for precedences I create a sequence container for any and all execute pacakge tasks that can run in parallel.
4) To help with parameters specific to the execute package task, I add another sequnce container for the individual excute package task.
5) Finally I add the actual execute package task.
6) Save the package to local disk.
7) Execute this pacakge.
I hadn't been setting explicitly setting the MaxConcurrentExecutables, but upon opening the saved copy of the last package I built and executed it has the default -1 setting.
My problem is that while this in-memory package is running it appears to be only running a single executalbe at a time. I'm going to try setting maxconcurrentexecutable to 4 or some other number to see if I get some parallel execution going on.
The real question is "Is there a limitation on using dynamic packages that limits them to only run a single executable at a time?". I haven't found anything in BOL that leads me to believe there is, but It was very obvious that only one executable would run at a time when I test this out.
I have 5 SSIS Packages out of which 1 is the parent package which calls the other 4 packages.
There are some database connections in my child packages.
Now, I want to make the packages dynamic.
I have two options.
1) Have all the connection information in the parent package and pass those values to the child packages. This way I'll be having only 1 configuration, the one for the Parent.
2) Have a separate configuration file for each package that will have all the connection related informations.
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection strings for all packages.
5) Can we do it with dtexec option /Conn[ection] IDOrName;ConnectionString We know what to give for ID or Name but what do we give for ConnectionString. Will it be the source/target one? Can we specify both source and target ones with which the data flow packages get executed.
I have a folder which contains all the flat files which are used by all the packages(ex--flat file connection managers) in my project. If we want to change the name of the folder,have to change in every package( in all connection managers) manually.It looks hardcoding and timetaking.
Is there any way to change in one place(xml,file,variable) so that it should be affected in all the packages.
one more doubt is..
If we configure the flat file connection manager in package configurations,configuration file (ex-xml)will be created (we can make changes in that file regarding that connecion mgr only.)
But i want one configuration file (ex--xml) so that i should configure the details of all the connection managers used in all packages.
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
