.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

What is the SSIS Transfer Task?

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server

I have just been using the Import Data wizard in SSMS and when going through the wizard I checked the "Optimize for many tables" checkbox.

The resultant package contains a task called "Transfer Task". I've never heard of this before. Its not listed in the toolbox and its not documented in BOL.

It does some rather strange things as well.

  • From what I can determine it uses an XML manifest file (stored in c:\documents and settings\<user>\Local settings\temp) to construct a SSIS package on the fly which is then executed by an Execute Package Task.
  • Its SourceDB property is always "smo_Pubs". Its DestinationDB property is always "smo_Pubs_xfred"
  • There's no UI for it

All very peculiar.


What is it?

Where has it come from?

Did it arrive with SP1 or have I just never noticed it before?

Why is it preferable to a package with lots of data-flows?

Why is it not documented?

When should I use it/not use it?


Questions questions questions...





View Complete Post

More Related Resource Links

SSIS Database Transfer Error - "Role Exists" even though DB is being overwritten in task.



Can't get over this error, and net searches reveal other postings similiar, but no answers.

SSIS database transfer task (with overwrite) from SQL 2k source to SQL 2k5 destination fails with:


Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [RFRSH_USER] " failed with the following error: "User, group, or role 'RFRSH_USER' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter.

The error seems the same regardless if the destination DB exists or not!

Anyone have a solution?




Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Post Upgade task, Upgrading SSIS Pacakges to SQL Server 2008.

Hi, I am trying to upgrade sql 2005 packages to sql 2008 after doing in-place upgrade of DE and SSIS. Can I know what packageformat column in msdb.dbo.sysssispackages refer to, as according to http://msdn.microsoft.com/en-us/library/cc879336.aspx the value should be 2 if the package is in sql 2005 and it should be 3 if it is upgraded. But I am seeing only 0 or 1.   Can I know any other method to figure out version of the ssis packages? I am having issues upgrading SSIS Packages from 2005 to 2008, using SSIS package upgrade wizard.   Thanks for your help. Regards, KRanp.

Configurable SSIS data transfer package

I have to create SSIS Pakage for importing data form a sql server to  another. And this SSIS data transfer package has to be configurable at run time for: - Database source connection - Database destination connection - Query to call from the source database - Table to insert in destination database And Also need to call this package from a stored procedure...

SSIS 2005 - Send Mail Task - signature appended to email is garbled - unicode problem?

Hi, I'm pretty new to SSIS so go easy on me. I have a Send Mail Task to notify if a file cannot be imported - the mailbody is created on the previous step by a VB.NET script task to include the name of the file and the path it's been archived to. The problem I'm having is that while the body of the email I've created is displaying fine, our company's Exchange server appends a signature to all emails, and this is coming up as undisplayable characters, presumably due to some kind of unicode encoding problem. I've tried casting the email body in an expression to DT_STR (doesn't work as DT_STR "cannot be converted to a supported type" which seems a bit odd but never mind), DT_WSTR (garbled signature), DT_TEXT/DT_NTEXT (strange error on this one - "Attempted to read or write protected memory") none of those ideas worked, and I'm a bit stumped now. Can anyone help? I'm using SSIS 2005 with SP3

create ssis package with muli task

Hi Friends, Pls solve my following query with example. now i am doing project using ssis 2005. in that 1. import data from multiple resources like .xls, .xml,db 2.Read only file path from created meta data table like dil_table_met (Filetype=.xls,filepath=c:\..) 3.In that meta table, i want to read only file path and check file path which filetype like .xls or .xml and then import to excel source and to db destination , the same via for all file type 4. next read data from resources import correct data into correct table and wrong data into error table 5.for all these above condition are in loop.   pls give a example for this its very urgent pls pls help me out for thisR.Vinothraja

send records as weblinks through send mail task in SSIS?

Hi All, i have a table with two columns and two records as follows. TypeOfReport                       Links InvalidRecords                http://ReportInvalid MissingRecords               http://ReportMissing --------------------- I have a package with execute sql task selecting * from the table above, full result set selected and an object variable created, it works good. but now when i connect it with send mail task, i wanna send email like Subject : Reports On InvalidRecords and MissingRecords MessageText : Links for the Reports : http://ReportInvalid                                                     : http://ReportMissing Can somebone help me with it. NOTE : i tried creating user and object variable but its good if i have only one record in table. i tried creating 2 user and 2 object variables as well but didnt work. Thanks

SSIS Script Task

I am trying get user input from a form created via script but the form flashes on the screen and then disappear...is there a special implementation for this?? The following code is in my main; FormIU   from = new FormIU();   from.Show();    

Can not transfer data usisng SSIS

Database Server is SQL2008 with SP1. I created ODBC that connects to one table in the database in the server. It works fine. Then I created a SSIS package that tranfer this table's data into another table using the ODBC. When executing the SSIS , it works fine. No problem transfering data using ODBC via SSIS. Requirement is to use SSIS package in SQL Server job and run it from there. When the SQL job is run , it fails.  Error thrown is Message Executed as user: CORE\svc_prodsql1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:42:08 PM  Error: 2010-08-26 12:42:08.39     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS: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.  End Error  Error: 2010-08-26 12:42:08.75     Code: 0xC0047062     Source: Data Flow Task ADO NET Source [16]     Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft]

Microsoft.SqlServer.Dts and related assemblies to develop custom ssis task are missing

Hi All, I tried to develop a simple ssis task but the problem that I can't refer the necessary assemblies like Microsoft.SqlServer.Dts.Runtime And also Microsoft.SqlServer.DTSPipelineWrap Microsoft.Sqlserver.DTSRuntimeWrap Microsoft.Sqlserver.ManagedDTS Microsoft.SqlServer.PipelineHost What is certan that they aren't installed within the GAC in my case, so where could I find them? I have SQL server 2008 entrprise edition Other question, should I use Microsoft.SqlServer.Dts.Design and  Microsoft.SqlServer.ManagedDTS which are missed too, or they are part of the 2005 version only Thank you   The complexity resides in the simplicity

Insert error description in sysjobhistory from ssis script task

hi. in ssis i have script task that, in some situations, raise error (Dts.TaskResult = ScriptResults.Failure). But in sysjobhistory table there is only shor description what happend (Description: The script returned a failure result.). Is there any way that i can set some system variable to describe what is error so that description ens up in sysjobhistory table? in the end, i want to look in job activity monitor and see that job is "red" and see the description that couses this failure.

Error Trapping in SSIS Script Task

Hi, I am processing a cube partition using an SSIS script task. The script works out the name of the partition which requires processing based on the system date.The problem I have is whenever there is a failure in the processing task the only error I get is that the script task returned a failure result. This is reported by the SQL job that calls the script task and also by SSIS package logging. I am running SQL2005 SP3How can I get my script to return a meningful error? Thanks in advance.

SSIS Script Task

Can I use a script task to recieve two date values to start a SSIS package? if so, how do I implement this?

SSIS 2005 Control Flow Task Priority

The short version is I am looking for a way to prioritize certain tasks in SSIS 2005 control flows. That is I want to be able to set it up so that Task B does not start until Task A has started but Task B does not need to wait for Task A to complete. The goal is to reduce the amount of time where I have idle threads hanging around waiting for Task A to complete so that they can move onto Tasks C, D & E. The issue I am dealing with is converting a data warehouse load from a linear job that calls a bunch of SPs to an SSIS package calling the same SPs but running multiple threads in parallel. So basically I have a bunch of Execute SQL Task and Sequence Container objects with Precedent Constraints mapping out the dependencies. So far no problems, things are working and initial testing looks like it will cut our load time significantly. However I noticed that tasks with no downstream dependencies are commonly being sequenced before those that do have dependencies. This is causing a lot of idle time in certain spots that I would like to minimize. For example: I have about 60 procs involved with this load, ~10 of them have no dependencies at all and can run at any time. Then I have another one with no upstream dependencies but almost every other task in the job is dependent on it. I would like to make sure that the task with the dependencies is running before I pick up any of t

SSIS - Script task - Web refrerences do not add at first or second attempt

Problem happens on 3 different PC's with BIDS installed.   Steps to reproduce: - New SSIS project - Drop Script Task - Edit its code - Add Web Reference - Specify a web service and click add reference - Close script task code editing window - Open script task code editing window - Reference is gone - But you CANNOT add another reference with the SAME name, it will get "1" at its end! - Try to add same web refernece again, it will have "1" at its end - Close script task code editing window - Open script task code editing window - You have 50% chance for reference to stay, 50% chance that it will disappear AGAIN! - But you now cannot add another reference with name or name1! - Try to add same web reference AGAIN and it will have 2 at its end - Close script task code editing window - Open script task code editing window - I have never got the reference2 to disappear... yet Please can someone just tell me... WHAT THE ... IS HAPPENING?    

SSIS FTP Task works, but the files aren't there

I've created an FTP Task in my SSIS package, and when I run it in BIDS, it turns green and reports success, but when I look at the destintation folder, the file that should have been sent isn't there. I can send the same file to the same destination using the same credentials using WSFTP and it gets there. How can one begin to debug such a thing?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend