DTS Dynamic Properties task to SSIS

Hi ,

What is the equivalent of DTS Dynamic Properties task into SSIS ?

How do I convert this task to SSIS ?

Thanks,

Vikas

But it used to work in DTS (2) - Changing connections using the Dynamic Properties Task
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4157.aspx)

There is loads of information out there about this if you bother to look: http://www.google.com/search?hl=en&q=ssis+dynamic+properties+task

-Jamie

sql

DTS drawbacks

I am trying to compile a case of why DTS packages need to be migrated to SSIS packages. Is there any document, whitepaper which can provide me drawbacks in DTS?

Thanks,

Vijay.

This will be completely situation dependent, and my reasons are different from yours... For us, it was simply the upgrade to SQL Server 2005. Some things continued to work, others didn't, so we converted packages.

But have you looked at the feature list of SSIS and compared it to DTS to build your own conclusions?

Phil|||I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful.|||

Vijay Thirugnanam wrote:

I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful.

SSIS performs and scales better than DTS by an order of magnitude.

As opposed to SSIS, DTS very much relies on the staging of data in order to transform it into something suitable for populating a data warehouse. SSIS very much relies on in-memory buffers to transform data, obviating the much of need to stage such data.

Also, DTS typically requires a significantly greater amount of custom coding to accomplish that which can be accomplished with SSIS.

|||

Try performing a loop in DTS (where you loop among various tasks not just inside and ActiveX script task) then try it in SSIS.

Also take a look at all of the work required to make a DTS package portable (able to move from test to prod without making changes to it) and compare to SSIS.

On this last see http://www.mutuallybeneficial.com/index_files/dts_ssis_packages_portable.htm for more info.

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

sql

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

Dts 2000 packages with SSIS

I need help,

I've got 51 pretty complex Dts packages that are running on Sql 2000. I'm
trying to make them run on Sql 2005 without at first migrating them with the
migration wizard, since i know that many of them cannot be migrated.

I'm using the "Execute DTS 2000 Package Task Editor" to make them run in an
SSIS package, some of them are running fine, but many of them, especially
those with a "Data Driven Query Task" are returning this error:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution
was canceled by user. at DTS.PackageClass.Execute() at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

After many research on the web, i've reinstalled the Sql 2000 tools on the
Sql 2005 server, but i'm still receiving the same error. Also, i've tried to
repair the "Microsoft Sql Server 2005 backward compatibility" in the
Add/remove program of the Sql 2005 server without much success.

For your info, i've installed the hotfix Build 2153 on both the server and
the client and i'm still having the same problem......

Any idea?

Thanks.What happens when you just run them via dtsrun and/or the DTS designer?|||

They run fine without any errors!

DTS -> SSIS -> ??

Anyone know where SSIS is? I installed the SQL Server 2005 evaluation
and found several app folders, and SSBID, but no SSID.
It took a bit of research to find and install DTS and I finally
imported a flat file of 500+ fields to begin normalizing, but I don't
see an application called SSIS to automate the process.SSIS is a service, which you have to select when you install (can be added later by running SETUP
again). You create SSIS packages using "SQL Server Business Intelligence Development Studio).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<thomasjbs@.gmail.com> wrote in message news:1191101123.446974.123480@.22g2000hsm.googlegroups.com...
> Anyone know where SSIS is? I installed the SQL Server 2005 evaluation
> and found several app folders, and SSBID, but no SSID.
> It took a bit of research to find and install DTS and I finally
> imported a flat file of 500+ fields to begin normalizing, but I don't
> see an application called SSIS to automate the process.
>

DTS & SSIS - much cop?

Hi

I am in the job market at the moment and keep seeing DTS listed as a requirement in the specs, typically right after "excellent T-SQL Skills". I kind of thought the latter made the former redundant. I don't really use DTS anymore - debugging a complex package is about as excruciating as pulling teeth - and just do it all in SQL. Load the data into staging tables using BULK INSERT and bugger about with it there before loading into the data tables.

Am I missing something? Is there some great feature and functionality in DTS (or latterly in SSIS) that I am missing out on? Perhaps I just haven't come across the complex problems these organisations have... Or is it just another drag and drop GUI that keeps you at arms length from the application and therefore less effective? Are DTS advocates the sort that would swear by EM over QA too?

I have DTS on my CV since I can (and have) used it but I am nervous that there are perhaps some advanced features I am not familiar with that could catch me out at interview.

I know there are some vehement critics of DTS on this forum and some agnostics but I don't know of anyone that is a real fan. Anyone got any opinions?

TaI am not sure if there are any advanced features of DTS that can add whole dimensions of functionality. I doubt it, but I am not a DTS advocate. I think you can make a DTS package just as complex as any C program, PERL script, or T-SQL procedure. I think you will get farther knowing which tool to use for what purpose.|||My .02:

I use DTS as a crutch and as what the military would call a "force multiplier".

1. Crutch

It's a really easy and fast way to move data from one place to another (with or without transformations). I'll use it over BCP any day.
a. I'll use it in production to move a remote database's data to a local database when there are issues with connection reliability and remote db performance (can't be a huge db and the move is done off hours when there are fewer issues).
b. I'll use DTS to archive off aged data to another database (perhaps on another server).
c. I'll use it to pull in data from a non-SQL system, transform it and make it (the data) queryable to anyone in the organization for reporting purposes (without having to maintain multiple sets of db drivers on client machines).

2. Force multiplier

It's a force multiplier in a couple of ways:

a. DTS makes the database a (more) important component of my employer's business model. It places the database server squarely into the middle of my employer's revenue stream and makes it an asset s/he pays attention to. It's Machiavellian perhaps, but the idea is that while my employer may not understand databases or database administration, s/he does understand information that is accessible. DTS makes data more accessible and is thus valuable to my employer.

b. The more scheming part of (a) is that DTS (by extension) makes ME more valuable. My employer no longer needs to hire an additional ETL specialist to gain access to the data; I can provide that access in the same or less amount of time.

c. Manageability. Since DTS packages can be scheduled through SQL Agent, I have one place to go to track the success/failure of a job. I don't have to scurry from one Scheduled Tasks window to another or check on MQ Queue on a third server or do a whole bunch of other tasks. I come in, settle down, check my scheduled tasks and we're done. It's on to other things.

DTS does have some evil aspects:

a. You can make packages too complex; complex packages can be a total nightmare to manage. I write my packages to be as austere and simple as can be. I try to stick with the very basic tasks/components and try to avoid too many in situ transformations.

b. They can be hard to port from one environment (dev) to another (prod). Appropriate use of the Dynamic Properties task can help, but it's always a challenge.

c. It runs in a separate memory space from the database server. You have to allocate memory to it (or rather make an allowance for it if you are using it heavily).

d. You have to be mindful of the context in which you are executing a DTS package. I find it best to T/S in to the server (even in production) to validate that things are running how I expect them to run. If you consistently try to run them exclusively from your desktop through EM, you WILL get burned one day.

DTS is a tool. It is nothing more and nothing less. Learning to use it well (and appropriately) can enhance your value to a prospective employer or a current one. From everything that I hear, SSIS offers even greater opportunities to enhance the role/function of the database server within an organization.

I am sure that database purists shudder to hear such heresy. Ultimately, I try not to look at myself a simply a DBA: my role is to help my employer make money. The more I help my employer, the more (I hope) that I can share in the rewards that follow.

My .02. It's worth what you paid for it.

Regards,

hmscott

PS. Pootle: best of luck to you on your job search. I really value the insights you have to share on this board. I certainly empathize with your position and nothing I have said here is intended to detract from you or anyone else.|||DTS is a handy way to logically group tasks together. It also makes process flow much easier to manage. I personally seperate doing the heavy lifting, usually in a stored procedure, from the business rules and logic around how, when and why said heavy lifting occurs. The former tends to be accomplished with raw "code", the latter happens through DTS.

It's the same manner in which I would seperate BI objects from the database through a data access layer, certain logic belongs in a certain place and becomes kludgy if you try to do otherwise.|||Thanks guys. I confess I still have a couple of packages and they are reasonably similar to those describeed - they started out as full ETL routines but were eventually stripped back to just the E, the T & L being completed by the calling stored procedure.

I also use DTS (via the wizard) over bcp etc for one off imports so I guess I am not totally pooh-poohing it. I just probably wouldn't consider it something I would even consider putting on my cv unless potential employers specifically looked for it.

PS. Pootle: best of luck to you on your job search. I really value the insights you have to share on this board. I certainly empathize with your position and nothing I have said here is intended to detract from you or anyone else.How terribly kind :D I would be very interested to know one single insight I have shared that has been valuable to you - my posts are really for my own amusement or for people less experienced than me - not the uber DBAs. And I didn't read anything that detracted from what I said (or if it did I don't take it personally - I am just interested in others' experience).|||Bah! You have more to say than you suspect, Pootle. And the "uber DBA's" were not born uber*. You just give back to the community as a whole, and don't worry over who is happy to get an answer from you, just know they are happy to get an answer. Like this guy: http://www.dbforums.com/showthread.php?t=1610893.

Besides, if we had a job opening here, I would offer it to you. I just think you would tire of the commute relatively quickly with the concorde being out of service, and all.

*They were in fact cloned from other uber DBA's|||Gosh - how terribly kind of you all. Sniff.

If you offered me the job and provided me with my own Lear jet I would certainly give it serious consideration :D

... Lear jets can make it across the Atlantic can't they? :shocked:|||I took it up with upper management here, and their counteroffer is a pair of water wings. Actually, their initial offer was a single water wing, but I managed to talk you up enough that they doubled their offer.|||I accept. When do I start?

DTS - Send records to different text file - DTS

Hi all,

I am designing a DTS package instead of SSIS.

I have a text file and already uploaded to tableA, there is a field named NameID in tableA. The field NameID should match the NameID in tableB and update other fields of tableA, the non-match records will generate another exception text file.

How can i implatement this in DTS? Which task or tech?

thanks

Micror

You'll need to ask this in the DTS forum. http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

DTExec is the only redist for executing dtsx file?

Question:

trying to execute SSIS package "Package.dtsx" from another machine. What are the requirements to do this without installing full SSIS?
I presume that we need .Net framework 2.0, but what are the minimum component or files I need to run a package?

thanks
HorseshoeMy understaning is that SSIS is not redistributable as DTS was. Either way there are more files than just dtexec. All the tasks and components are spread accross several assemblies for example. If it is redistributable, then it will be covered in redist.txt or the equivalent if that has changed from SQL 2000. I believe it is now licencsed as server component, so you need a server licence for any machine you install it on.|||Darren's absolutely right. For a headless install (without UI) on a server, you can select to not install the Tools, but you still need a license and the SQL Server setup to install SSIS on another machine.

regards
ash

DTExec is taking all my memory

I have created a SSIS package that reads 500 text files splits them into 4 raw files then reads them again and writes then to 4 database tables different Tables.

The reason form this is that my raw files have multiple types of records in them and it is only 1 Coolum. I split this out into the different types of records and load whole rows into the database.

ie input 1 txt file

<T6>
1:1000178
3:18148821-00
5:40204043
6:1
17:EX201036259NZ
25:0000304862
</T6>
<T1>
1:18148821-00
</T1>
<T5>
1:1511313
4:18126485-00
8:2006032510230300
17:EX201033399NZ
</T5>
<T6>
1:1511158
3:18084863-00
5:40617044
6:1
17:EX201033969NZ
25:0000302981
</T6>

End up begin rows in the T6 Table

1000178 18148821-00 40204043 1 EX201036259NZ 0000304862

1511158 18084863-00 40617044 1 EX201033969NZ 0000302981

T5 Table gets a new record
1511313 18126485-00 2006032510230300 EX201033399NZ

and T1 Table get a record

18148821-00

Anyway all this works find but I find that the DTExec process work fine until it has used up all the memory in the laptop in general it take 400megs to run this SSIS. I'm wondering am I missing something like don't run in a transaction. I know in the old DTS you could commit on each package and how do I turn all logging off eg what you see in the DOS box (can I do this?) would love some help on this and if anyone want a copy of this ssis package ie your trying to do the same then I'm more than happy to email it.

Check the /Rep option of DTExec to reduce the output.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

-Jamie

|||

Thanks Jamie,

This has help the logging (if you know can I set this in the enviroment IDE).

Now bigger problems is the memory, at the moment it fails after 700 files due to luck in memory.

Any ideas on how to find where the memory leak is in the SSIS Package?

|||

Just to let other people kno, after some more searching I found that there is a memory leak in the Forloop object, that should be fixed in SP1.

I have download SP1 and tested it and it does appear that there is still a memory leak but only about a 10th of what it was, which now allows me in import 5000 files in one go.

|||

Thanks for the info John. That's an important one to know. I certainly wasn't aware of it.

-Jamie

DTExec is not working in xp_cmdShell

Hi All,

When I was trying to execute an SSIS package from DTExec using xp_cmdShell, it is giving an error message saying "unrecognized command,...". This error I am getting only in my Staging Environment. But at the same time, it is working fine with Development and Production servers.

I suspect the issue should be with config or access issues. So if anyone of you faced the same problem or if anyone have any solution, please share with me.

Thanks in advance for your help.

Thanks & Regards,

Prakash Srinivasan

Two things:
- Make sure you are using the full path to dtexec.exe.
- Is SSIS actually installed on the staging server?

dtexec

I understand to schedule an SSIS package to run, I need to use the dtexec utility. I want to schedule the job right within SQL. I walked through how to set up the job, but I really could not decipher the proper syntax to use as a Step.

I have the following:

dtexec /File "C:\...file.dtsx"

Is the the proper way to schedule and execute an SSIS package or is there some other way I should be doing this. The pacakge will run unattended nightly. I am using SQL Server 2005.

Thanks for the information

Hi, yes the command line syntax can be a bit daunting. But you should not have to type it out yourself...if your using SQL agent and choose the Step type of "SQl Server Integration Services Package", it will show a UI that helps you build the command line for you though you do not need to build the full command line there.

If your running outside of Agent or want to run with Agent and the step type of Command exec then use dtexecui.exe as it will also help you build the command line...and its the basis of the agent UI I mention above.

Hope that helps

DSV in SSIS

Hi,
How can we make use of DSV in SSIS?

In the connection manager of a package i tried to add a link to it but couldn't.

I believe it has an importance since it is there. Could anyone tell me what is its benefits and how it could be implemented?

Thanks

Christina

A DSV allows you to create a metadata layer over an existing Data Source. Within a DSV, you can select a subset (or all) of the tables and views available from a Data Source, rename any of the tables/views and/or their columns, add new Named Calculations (which act like computed columns within a table/view), and add new Named Queries (which are complete select statements, which basically act like views).

To use a DSV within SSIS, you still create a Connection Manager that points to the Data Source that the DSV is based on. Then, when you select the Connection Manager, you should have the option to select the DSV that is based on it. For example, if you have a Data Source based on an OLE DB provider connecting to SQL Server, you could create a DSV based on the database that the Data Source is connecting to. Then, within a Data Flow task, if you add an OLE DB Source adapter, when you select the Connection Manager based on your OLE DB data source, you should be able to also select the DSV.

HTH,

Dave Fackler

|||

Thanks Dave.

Actually this is what I read about it in the books online. However when I come to add a data source in the connection manager i don't see the DSV among the possible connections.

Have you tried it? I could have missed something.

Thanks again

Christina