Hi all of you,
I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.
I mean, are there big differences between store data as XML format and nvarchar?
That sort of stuff.
Thanks in advance for any input,
http://msdn2.microsoft.com/en-us/library/ms187508.aspx
HTH
Hi all of you,
Formerly when we ran dts we had DtsBackup2000. So we could to arrange lots of backups around the network in a centralized way. But now how to run backups in groups no individually way?
We've got five sql25k along with its dtsx packages and we'd like have all of them in the same place.
Any tool or idea?
Thanks in advance,
What storage method do you use?
|||Hi Darren,
Thanks for your answer. We've LEGATO system for our db. in regard packages we save them as .dtb and .dts files.
TIA
|||
I meant how do you store your SSIS packages, as that would impact what I suggest for a backup of them! I know about DTS, you told me you used DTSBackup.|||
Hi again,
Individually, people save them in its own local machines but, of course, that's not very safe.. I mean, VSS is not used at all.
The SSIS packages are stored on the production server and on the development server.
Up to the date the only way for me is do backup one after one.
Thanks Darren,
|||
All of them in MSDB.
I don't know if this answer finally clarifies this post.
|||If the packages are deployed to MSDB as opposed to File System, backing up the SQL database on Production & Dev will effectively back up your packages.
Obvioulsy the "undeployed" latest or working versions on individual machines are at the mercy of the users! At least suggest they are saved on a network drive that is backed up if they cannot or will not use VSS. Local machine saves are madness
|||
Hi Will,
Thanks for your answer. But our main idea is to retrieve as soon as possible a concrete dtsx without have to do a restore when there's an issue or problem.
|||
If that is the case, go overboard... save all packages to a shared drive on the network (make sure that your sql agent user / proxy has access). Back that up nightly and run all imports into your MSDB from this drive. Also, make sure that you are doing full backups of the sql server in test and prod.
//mycomputername/SSISPackages/PackageName(s)
//mycomputername/SSISPackages/ConfigFiles
backup all files in SSISPackages...
|||enric, if you need to rollback to a working version of a SSIS package then I woudl look to re-deploy that working package from your release management store or source control system.
DTSBackup made sense in DTS because it was very common to change packages on the server, just because the tools worked that way, but with SSIS, you should always have an offline copy with changes made by the developer, and that "should" be under some control, source control, release management etc.
Hi all of you,
Formerly when we ran dts we had DtsBackup2000. So we could to arrange lots of backups around the network in a centralized way. But now how to run backups in groups no individually way?
We've got five sql25k along with its dtsx packages and we'd like have all of them in the same place.
Any tool or idea?
Thanks in advance,
What storage method do you use?
|||Hi Darren,
Thanks for your answer. We've LEGATO system for our db. in regard packages we save them as .dtb and .dts files.
TIA
|||I meant how do you store your SSIS packages, as that would impact what I suggest for a backup of them! I know about DTS, you told me you used DTSBackup.|||Hi again,
Individually, people save them in its own local machines but, of course, that's not very safe.. I mean, VSS is not used at all.
The SSIS packages are stored on the production server and on the development server.
Up to the date the only way for me is do backup one after one.
Thanks Darren,
|||All of them in MSDB.
I don't know if this answer finally clarifies this post.
|||If the packages are deployed to MSDB as opposed to File System, backing up the SQL database on Production & Dev will effectively back up your packages.
Obvioulsy the "undeployed" latest or working versions on individual machines are at the mercy of the users! At least suggest they are saved on a network drive that is backed up if they cannot or will not use VSS. Local machine saves are madness
Hi Will,
Thanks for your answer. But our main idea is to retrieve as soon as possible a concrete dtsx without have to do a restore when there's an issue or problem.
|||If that is the case, go overboard... save all packages to a shared drive on the network (make sure that your sql agent user / proxy has access). Back that up nightly and run all imports into your MSDB from this drive. Also, make sure that you are doing full backups of the sql server in test and prod.
//mycomputername/SSISPackages/PackageName(s)
//mycomputername/SSISPackages/ConfigFiles
backup all files in SSISPackages...
|||enric, if you need to rollback to a working version of a SSIS package then I woudl look to re-deploy that working package from your release management store or source control system.
DTSBackup made sense in DTS because it was very common to change packages on the server, just because the tools worked that way, but with SSIS, you should always have an offline copy with changes made by the developer, and that "should" be under some control, source control, release management etc.
Hi all of you,
Formerly when we ran dts we had DtsBackup2000. So we could to arrange lots of backups around the network in a centralized way. But now how to run backups in groups no individually way?
We've got five sql25k along with its dtsx packages and we'd like have all of them in the same place.
Any tool or idea?
Thanks in advance,
What storage method do you use?
|||Hi Darren,
Thanks for your answer. We've LEGATO system for our db. in regard packages we save them as .dtb and .dts files.
TIA
|||I meant how do you store your SSIS packages, as that would impact what I suggest for a backup of them! I know about DTS, you told me you used DTSBackup.|||Hi again,
Individually, people save them in its own local machines but, of course, that's not very safe.. I mean, VSS is not used at all.
The SSIS packages are stored on the production server and on the development server.
Up to the date the only way for me is do backup one after one.
Thanks Darren,
|||All of them in MSDB.
I don't know if this answer finally clarifies this post.
|||If the packages are deployed to MSDB as opposed to File System, backing up the SQL database on Production & Dev will effectively back up your packages.
Obvioulsy the "undeployed" latest or working versions on individual machines are at the mercy of the users! At least suggest they are saved on a network drive that is backed up if they cannot or will not use VSS. Local machine saves are madness
Hi Will,
Thanks for your answer. But our main idea is to retrieve as soon as possible a concrete dtsx without have to do a restore when there's an issue or problem.
|||If that is the case, go overboard... save all packages to a shared drive on the network (make sure that your sql agent user / proxy has access). Back that up nightly and run all imports into your MSDB from this drive. Also, make sure that you are doing full backups of the sql server in test and prod.
//mycomputername/SSISPackages/PackageName(s)
//mycomputername/SSISPackages/ConfigFiles
backup all files in SSISPackages...
|||enric, if you need to rollback to a working version of a SSIS package then I woudl look to re-deploy that working package from your release management store or source control system.
DTSBackup made sense in DTS because it was very common to change packages on the server, just because the tools worked that way, but with SSIS, you should always have an offline copy with changes made by the developer, and that "should" be under some control, source control, release management etc.
How can you tell what DTS package a Job step is running when it looks like this?
DTSRun /~Z0xBEA63421A37A7AA7175D23C17BA2CB8CEC94DCC69159320C62777706E248EFDD4BF43C93709C9FCEF2982201C4D26B7EAFF25816E9F0E7C51467E5C2446BE35FBA02E809F523C5D19F6AC91416E5ED3B79BE27B7991980910679EBF4C311A9A33AED799A9C53BB7BFDAEDE7EEBC6ECE9001D0E6218956A578C40FC
The GUID does not appear to be related to what DTS shows in the Package or Version GUIDS of the DTS packages. And yet the job runs from what I am told.
SQL Server 2000 msdb.dbo.sysjobsteps
The value "DTSRun /~Z0xBEA63421...." is in the command column. The table also has a step_name column which appears in the job. The default step_name is the name of the DTS package but it could have been renamed.
If so turn on profiler and find the package execution line with the package name
exec msdb..sp_get_dtspackage N'MyPackage'
|||If you can enable the dTS package log then you can see the status.|||The /~Z paramater is an encrypted command line version. Microsoft does this in case it needs to pass a password.
The job step and job name is by default the name of the package. If these have been changed that will make it harder.
If you have the command line and run it from a command prompt it will display in the command window which DTS package is running. I would recommend using this information to change the command line to use the /S /E /N commands so that it's easier to see which package is running.
Hi,
Yesterday I had posted a query of How to transfer the Data from AS400 files to Sql 2005 tables with DTS and the error I received for the same. I am not able to view the replies since it has been deleted. Pls repost the same. I posted the query expecting for a solution but instead my question was deleted..... the very purpose of forum is defeated.
Regds,
Anu
anu_ank wrote:
Hi,
Yesterday I had posted a query of How to transfer the Data from AS400 files to Sql 2005 tables with DTS and the error I received for the same. I am not able to view the replies since it has been deleted. Pls repost the same. I posted the query expecting for a solution but instead my question was deleted..... the very purpose of forum is defeated.
Regds,
Anu
Its possible that it was received because the question was concerning DTS. This is not a DTS forum.
Having said that, removing someone else's posts is completely improper. I would hope nobody would ever do that without a very good reason.
-Jamie
|||Hi,
I want to migrate data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result code
I am using Client Access tool to connect to ISereis and the provider is "IBM DB2 UDB for isereis IBMDA400 OLE DB Provider"
I posted this question since I received the SSI error. If this is not the right place pls direct me to which forum I should post the above query .
Regds,
Anu
|||I have not been successfull with the driver you mention. I am using AS400 iseries for V5R4
I would suggest you try with the oledb for ODBC driver or the microsoft driver, that I have been using recently in an SSIS package:
Microsoft DB2 driver
go to http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/featurepacks/default.mspx
and select the feature packs , in the list of feature packs you will see the Microsoft OLEDB Provider for DB2
|||
Hi,
Yesterday I had posted a query of How to transfer the Data from AS400 files to Sql 2005 tables with DTS and the error I received for the same. I am not able to view the replies since it has been deleted. Pls repost the same. I posted the query expecting for a solution but instead my question was deleted..... the very purpose of forum is defeated.
Regds,
Anu
When viewing your profile, you can click on the "Search" button. That will show you your threads you have started.
Your post is not deleted. It is in the SQL Server Tools General forum.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1487606&SiteID=1
OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
Right click on the package and select "Export..."
Chose File System as the destination and once done filling in the fields, you'll be able to open that package up in Business Intelligence Developer Studio.|||
Thanks, I tell ya, I should have walked away from this project when it was brought to me.
Coming from coding .NET apps for the pass 6 years, this SQL style work is a whole new world to me.
ok, thanks
Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement?
Also maybe this is a very bad way to do this- any suggestions?
You can use dtsrun commandline utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp
BTW, SQL provides a extended stored procedure xp_cmdshell to perform commandline tasks, so you can also call dtsrun utility in T-SQL:
exec('master..xp_cmdshell ''dtsrun -S iori\Athena -E -N test ''')
|||I saw that command line solution a while ago, but I was told that it isnt the best thing to do security wise.I ended up finding DTS.Packages (DTS.Package dtsp = new DTS.Package();) which seems to work pretty well. The only problem is that the web account does not have the correct permissions. By enabling a trusted connection, and tweaking IIS, it now prompts the user for a username/pswd when they get to the site. If they enter in an account with the correct permissions, then they are able to run the DTS job.
Now I'm trying to get rid of this annoyance. Is it possible to somehow force a login to a specific trusted account? If not then I was thinking of trying to run a job that will just run the DTS package, but I don't know how to do that either. Any suggestions?
