.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

Can a Synchronous and Asynchronous Transformation in one data flow

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
Hi Guys, Can a Synchronous and Asynchronous exist in one data flow? I mean, in my data flow, i have 5 Synchronous and 1 Asynchronous Transformation in the end all in one flow. The output is not the way it was use to be because im introducing a Asynchronous Transformation at the end for 2 columns. Any suggestions? Thanks,

View Complete Post

More Related Resource Links

Excel File as input paramter for Lookup Data Flow Transformation problems


Before I start, I'm using SQL 2008.

I have a Excel file with email addresses that need to acts at input parameters to a Lookup transformation. I have set the Excel Source to my file and specified the email field to be the output. I have dropped the Lookup Transformation Data Flow and connected the both. I'm going to execute a very simple stored procedure, and under the Connections section my SQL query looks like follows: EXEC Test_GetUserName ? 
When I run that I get an error saying that no parameter was provided. But when I run EXEC Test_GetUserName 'someemail@companyname.com' everything executes great, for the obvious part that the email is hard coded. How do I pass the excel input as the parameter?

Thanks for all the help.

There is 10 types of people in the world, those that understand binary, and those that don't.

Dynamic WPF: Create Flexible UIs With Flow Documents And Data Binding


Flow documents offer enormous flexibility in arranging text layout and pagination, but they don't support data binding, so you can't dynamically change content. Here we build a component to solve that problem.

Vincent Van Den Berghe

MSDN Magazine April 2009

How do I add two XML tasks to a Data FLow task?

I am new to SSIS and am developing a package which takes input from an XML file and then populates an existing SQL table with these records.  However, this is a very large XML file.  I keep getting an Out of Memory exception.  (This file is 210 MB)!  So what I tried doing was separating this XML file into 8 smaller files.  Now, I can successfully load data from these smaller files one at a time.  But how can I do all of them in the same package?  I connected two XML tasks to the Data Flow task and get the error below.  I also increased my timeout and number of errors to no avail.  Advice? Error: 0xC002F304 at XML Task 1, XML Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.". Task failed: XML Task 1 Warning: 0x80019002 at zSkywardEnrollment: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.". Task failed: XML Task SSIS package "

How to detect that a Data Flow Task has failed (from inside a custom componet)

Hi, in the PostExecute Method of a custom component i would like to check if the Data Flow Task has been failed. Is that possible? Also it would be great to be able to check if this custom component already got all data or has been marked as "green".  Thanks, Dennis

Custom SSIS Data Flow Component Not Showing in Toolbox or GAC

Hello - I have created a very simple data flow component for SSIS (Actually, I am following this example:  http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en).  However, when I register the DLL to the GAC, I am unable to find the assembly in C:\Windows\Assembly - even though the GACUTIL says "Assembly Registered Successfully".  Furthermore, after copying the DLL to the PipelineComponents folder for SSIS (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents), it does not show in the "Choose Items . . ." dialog box of SSIS.   I am running SQL Server 2008 Dev edition, Visual Studio 2010 with .NET 4.0, and Windows 7 Enterprise 64-bit edition.  Any assistance/thoughts would be appreciated. Thanks!

Data flow task with multiple destinations randomly hangs

I have a package whose control flow consisting of a single data flow task with multiple destinations continues to hang at random locations.  The data flow task is fairly simple: - One OLE DB source (one query of selecting about 10 fields from one table) - 9 lookups that serve basically as left joins to capture which data "drops off" (does a row redirect on on lookup errors to an "error flow") - Two destinations: one the captures all the data that doesn't drop off from the lookups, and another to capture the data that does drop off; these write to two tables unrelated to each other, and unrelated to any of the tables I'm reading from I have tried the following: - Changed one or both destinations from OLE DB to flat file -- still randomly hangs - Removed both destinations altogether -- this always suceeds.   - Removed destination A and leave destination B -- this always succeeds - Removed destination B and leave destination A -- this always succeeds I took great care in making sure I get no warnings when the package runs, even resolving the unneeded column warnings, and the duplicate keys on all my lookups, but it still hangs. However, once I give up and stop the package, the following error is thrown (among others that appear to be a byproduct of the root issue): Error: 0xC02020C4 at Data Flow Task, OLE_SRC AP12 [1]: The attempt to

What does strategy exist to deploy SSIS package and my own data flow components into a enterparise s

I created a SSIS package and several data flow componenets for this package.    What does strategy exist to deploy SSIS package and data flow components into a enterparise server?   Thanks in advance.

OLE DB Destination issue in ForEach Data Flow

Has anyone encountered the following problem:I have a ForEach container with a single task in it - a Data Flow. The Data Flow uses a Source Script transformation to read each file and writes out to several output streams, each of which is connected to a OLE DB Destination.The ForEach executes for each file (in my example 3 times) but only the data from the last execution is in the DB tables.This from is my output window:SSIS package "PIF.dtsx" starting.Information: 0x4004300A at ETL Audit, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Load V6 Policy, DTS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Load V6 Policy, DTS.Pipeline: Prepare for Execute phase is beginning.Information: 0x40043007 at Load V6 Policy, DTS.Pipeline: Pre-Execute phase is beginning.Information: 0x4004300C at Load V6 Policy, DTS.Pipeline: Execute phase is beginning.Information: 0x402090DF at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_PDMRFeed [5277]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_Policy [5208]: The final commit for the data insertion has started.Information: 0x402090E0 at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has ended.Information: 0x402090DF at Load V6 Pol

execute sql task vs data flow task

My manager has asked me to use linked server to connect to ORACLE database rather then oledb   my source db is ORACLE and my Distination is SQL server 2005 ( in 64 bit  windows server) I am planning to write this query in an execute SQL task, INSERT  dbo.MV_MAX (PERIOD                                 ,PRODUCTGROUPID                                 ,PRODUCTID                                 ,SUBPRODUCTID                                 ,HLBER_ID                                 ,BE_ID                                 ,ENTITYID                             &n

Error in Script component in the Data Flow Task

Hi, I need a small help. This is what i require. I need to generate a auto increment number using script component and assign it to target column. use db1 go create table src (name varchar(10) ) insert into src select 'A' union all select 'B' union all select 'C'   create table trg (id int, name varchar(10) ) while loading the trg table, i need to generate a auto increment number and then load it to target. Finally, my trg table data should look as follows id name 1  A 2  B 3  C This is what i have done inside the package. step1 : declared a global variable "tmp" of int32 step2 : created a connection manager pointing to "db1" database step3 : drag n drop data flow task step : inside the data flow task , i have the following        OLEDB Source  (select name from src)           |        Script Component (i have added a new Output column "v_id" of int datatype in INPUTS AND OUTPUTS page)           |        OLEDB Destination In the Script page in Script component , in ReadWriteVariables | User::tmp, I have chose Visual Basic 2008 and clicked the EDit script and inside the Editor this and inisde the below routine i have added only two

Data Binding in Flow Document

1.       I have some Flow Document Templates, where Binding Information will have to be specified in the Design Time. In the run time a Business Object will be assigned to the Data Context property of Flow Document or any of the Panels (Stack, Dock etc) inside the Flow Document.   2.       Properties of Paragraph, Run or other elements does not seem to have dependency properties, hence I will not be able to specify Binding Info Declaratively. Am  currently wrapping my Content in BlockUIContainers with Text Blocks inside them. The Text Block's Text property can be used as a placeholder to specify binding information declaratively.   3.       One alternate way to specify Binding for Flow Document Elements that are not enclosed in Block Or Inline UI Containers is to specify a reverse binding (from the controls in the application UI to the Flow Document Elements, where Flow Document Elements will be a Source now and the Binding will be One Way To Source. This increases the number of lines of code for each binding and also the requirement in my assignment is to specify the binding declaratively.   4.        Is Wrapping the Content in BlockUIContainers / InlineUIContainers to specify the Binding Info, the proper approach or is there any better solutions ?.   5.       Also how do we preserve white spaces inside a Flow Document Element ?.   (Note: After all the manipulations, the F

synchronous and asynchronous event handlers sharepoint 2007

Any one help me out to understand synchronous and asynchronous event handlers sharepoint 2007...why we r going to use..Event Handlers...

Assembly items don't show in SSIS Data Flow

I successfully add a new assembly (MapPoint Batch Geocoder.dll) into GAC using Mscorcfg.msc and I can see it from the assembly list and I've added the dll into SSIS's PipelineComponents folder but for some reason I just cannot see it from SSIS Data Flow Item tab, I saw some others have the similar issue, and I actually tried to fix it based on their solutions such as change the framework version, but nothing changed. I am using VS2008, .NET 3.5 framework. I don't understand how come I can see it from GAC and also can see it through .NET tab in "Add References" but just not from Data Flow Item tab? Any ideas? Thanks a lot!

Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registe

Hi, I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error: The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer. Then when I try to delete it it gives this other error: Cannot remove the specified item because it was not found in the specified Collection.  I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition. Any suggestions why this is happening and how to fix it?

Data Flow Tasks does not complete even though data transfer has ended

I have an SSIS 2008 package which contains a number of SQL Tasks and Data Flow tasks with precedence.  When I run each of the tasks separately, the transfer is completed, and sometimes the data flow task turns green, but sometimes it does not.  When I run the package as a whole from my workstation, a few of the tasks compete but then all processing stops.  There are no errors, the package just sits there.  The objects within the Data Flow tasks are green and the data transfer is completed, but the task is yellow and the package does not move on to the next task. Each of the Data Flow tasks consists of one ADO.Net Source to an ODBC driver for Cache and an OLDB Destination to a SQL Server database.  I can monitor the source connections and I can see when the successful transfer activity stops.  However, the package does not close the connection and move on to the next task. I have tried different levels of precedence and even allow all of the tasks to run in parallel and it does not seem to matter.

Populate a Data Flow Task's variables with values from a .dtsConfig file?

I have an SSIS Project to import exchange rates from an XML file into SQL. The project works when I have certain values hard-coded into variables, such as the URL of the XML File, proxy username and passwords etc. I decided to put in a .dtsConfig file, and have it update the values of the variables in the data flow task. So, one of my SSIS Packages in this solution is called ECBDailyRates.dtsx. It contains one control flow, which is a data flow task. The data flow task is a script task which does some XPATH on an XML file, and then provides outputs to an OLE DB Destination, which in turn puts the data into a table in SQL. That much worked before I put in the dtsConfig file. There are a few variables declared inside the data flow task, such as; PricesXMLUriDaily, String ProxyAddress, String ProxyAddressPort, Int32 etc. etc. These are now being updated from the dtsConfig, which is below. It was my understanding that SSIS would run through the config, and update any of the variables as required, and then run my data tasks. However when SSIS runs my script task, none of the variables have been populated, and so it falls over... as it were :) <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="...." GeneratedFromPackageName="dim_Institution" GeneratedFromPackageID="

SSIS For Each loop with data flow hangs on DTC (SID -2) when inside transaction

The short story: I have a data flow inside a For Each container that is inside a sequence container.  A transaction is established at the sequence container, with Isolation Level Serializable.  The For Each container is limited to execute only 4 iterations.  The data flow iterates once fine, but the second iteration hangs.  The Activity Monitor shows the process is waiting on a another process with a SID of -2.  From other research in the past, I have found that this is an internal process dedicated to DTC (Distributed Transaction Coordinator). Environment: Microsoft SQL Server 2005 (SP3) on Windows 2008 R2 Standard.  I'm using SQL Server BIDS 2005 for building these packages.  I also have SQL Server 2008 installed and the databases are installed there. The details: The data flow merges data from the same table in two databases, source and target, using a Merge Join component.  It then performs a few transformations and lookups from other tables in the target DB to get IDs.  Then it inserts new records into the target DB or updates existing records in the target DB.  The two source tables are using OLE DB Source components with customized queries.  The insert target component is an OLE DB Destination and the update target component is an OLE DB Command.  In this particular scenario,
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