Query Results SSIS Data Flow Source Adapter

Jun 1, 2006

Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.

The query plan is already cached in SQL.

Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.


View 3 Replies


Bulk Insert Vs. Data Flow Task (different Row Results Using Flat File Source)

Nov 2, 2006

I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).

With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.

I used two different flat file sources and got the following:

Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows

Anyone have any guess as to why this is happening?

View 9 Replies View Related

Data Flow Source For MS Access In SSIS Package

Jul 26, 2006

Hi all...

I'm creating a SSIS in the designer view of SQL Server BI Dev. Studio (SQL Server 2005)

I need to import a whole table from MS Access into my local SQL Server.(this task will be performed weekly, so once working I'll schedule a job for it)

I've created a 'FILE' connection to MS Access in the 'Connection Managers'.

When I'm on the 'Data Flow' tab I can't find a Data Flow Item to use as a MS Access connection.
(available on the 'Data Flow Sources' are only: DataReader, Excel, Flat File, OLE DB, Raw File and XML Sources)

What am I doing wrong/missing?

Thanks for your help.

View 4 Replies View Related

SQL 2012 :: SSIS Data Flow Items Tab Missing For Adding Data Source / Destination

Apr 3, 2014

I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.

View 4 Replies View Related

SSIS Data Flow Source Component To 'read' A PDF File

Feb 13, 2008

At our business we are getting a lot of PDF documents that are being hand keyed into a database. Has anyone heard ior know of a SSIS Data Flow Source component that I coud use to read thos documents into a data stream (?) and process?

View 5 Replies View Related

Use Query Results To Feed Data Flow

Sep 24, 2007


I need some help determining the best way to accomplish my task. The workflow starts by generating a list of unique ID's from a local table. Then take that list of unique ID's and query an Oracle table for all matching records.

My thought was to first use an Execute SQL task with the following SQL:

select projectid from projectlist group by projectid

with Result Set configured as follows:

Result Name = projectid
Variable Name = varProjectIDList

Then in the Data Flow Task add a DataReader Source to pull the matching data. Here's where I'm getting hung up. I'd like to pass the result set from the Execute SQL task. I tried the following SQL but it doesn't work.

select * from masterlist where projectid = @[User::varProjectIDList]

I'm open to any suggestion on the best way to take my unique list and use it as input for a query against my Oracle DB.

Thank you for your ideas.


View 4 Replies View Related

SSIS XML Source Adapter Can't Make XSD Location An Expression

Nov 21, 2007

I am using the "XML Source Adapter" in an SSIS package.
I notice that although you can specify the XML filename as an expression, the XSD appears to have to be a fixed file path.
This is a problem for me since the path for the XSD is different in my development than it will be in production (in production it's on drive E:, which I don't have).

I'd like to have the file location specified in the config file, but since I can't make it an expression how can I do that?

Also, since they don't have Connection Managers I can't switch DelayValidation on.


View 24 Replies View Related

Dynamic Queries With Data Reader Source Adapter

Apr 16, 2008

I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.

Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?

View 3 Replies View Related

SSIS Data Flow Parameterize Query

Jun 1, 2006


I am using SSIS in SQL Server 2005 and want to have a query like this in my data flow task

Select a.*
from abc as a
inner join (Select max(b.id) as ID from xyz as b inner join pqr as c on b.id = c.id and b.id > ?) as t1
on t1.id = a.id

SSIS fails to detect the parameter (?) for the inner query and gives message.

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.", so assuming this is your problem, then you can workaround.


The idea is to parameterize the inner query ,,,
(so if the above query doesnt make sense ignore it )

View 1 Replies View Related

How Do I Add An ODBC Connection Data Source As A Data Flow Source

Mar 2, 2007

I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.

Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?

Thanks in advance


View 2 Replies View Related

SSIS Variables Between Data Flow And Control Flow... How To????

May 17, 2007

Hi everyone,

Primary platform is 64 bit cluster.

How to move information allocated in SSIS variables from Data Flow to Control Flow layers??

We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????

Thanks in advance and regards,

View 4 Replies View Related

SSIS OLEDB Data Source Query Parameters Connect To Oracle

Oct 26, 2007


I'm trying to use query parameters with an Oracle OLEDB Source in a data

flow task and I'm having problems.

I've tried formatting the query each of the following ways...







from frequency_bcs

where update_frq > ?

and update_frq <= ?


Parameters cannot be extracted from the SQL command. The provider might not

help to parse parameter information from the command. In that case, use the

"SQL command from variable" access mode, in which the entire SQL command is

stored in a variable.

Additional information

---> Provider cannot derive parameter information and SetParameterInfo has

not been called. (Microsoft OLE DB Provider for Oracle).

View 7 Replies View Related


Mar 7, 2006




View 3 Replies View Related

Using A Recordset Source In A Data Flow

May 17, 2007

I have been using a recordset destination in a data flow where I need to perform some complex manipulation on a dataset, including combining some information from a web service and updating records that already exist, vs. inserting them.

I have a script task that modifies the dataset as needed, and then saves it back to the variable it came from.

However, when it comes time to write the data to the database, I couldn't find an appropriate tool - there's no "recordset source" object in the data flow task, and use of a "for each" loop with a sql call to a stored proc takes 20 minutes for a few thousand rows.

The best way I could find around this was as follows:

Call the .NET ".GetXML" method on the dataset and put the resulting XML data into a string variable
Generate an XSD for that XML (it comes out like <NewDataSet><Table1>...)
Use an XML source in the data flow task.This works, and the same data insert that took 20 minutes via the loop / stored procs now takes under 10 seconds.

It seems horribly inefficient to have to do this - there should be a way to just dump my dataset back into a table natively without all that extra stuff.

Anyone done anything simillar?

View 1 Replies View Related


Mar 7, 2006




View 1 Replies View Related

HOW? Access Results Of Computed SQL Statement In A Data Flow

Apr 24, 2007

The SQL computed is complex enough that I can't see a way to make it a parameterized query. The obvious approach seems to be to compute the SQL in a CONTROL FLOW SCRIPT TASK and then use it to load a variable to set the VARIABLE SOURCE of a CONTROL FLOW EXECUTE SQL TASK.

I see that I can return a resultset to a variable.

But getting the rows of the results into a dataflow is not obvious. I have heard mentione that a Derived Column can do this. I can see using a dummy SCRIPT COMPONENT as DATA SOURCE with nothing in it to then drop into DERIVED COLUMN. But when setting up DERIVED COLUMN I don't see how to pull the columns out of the RESULTSET variable.

If it makes a difference I think the columns of the resultset will always be the same in this scenario.

Maybe this is totally the wrong approach? Any clues would be appreciated.

View 1 Replies View Related

Set The Data Source Of Data Flow From External Application (C#)

Jan 11, 2006

I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!

View 1 Replies View Related

Data Reader Source In Data Flow Problem

Jul 18, 2006

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,


View 11 Replies View Related

Slow OLEDB Source In Data Flow

Feb 2, 2008

Slow OLEDB Source in Data Flow

Hi All,

I have a simple data flow task, composed of only an OLEDB Source, a Conditional Split, and two Execute SQL statements (both insert statements, one after the other). When I run my package in Visual Studio for debugging, I noticed that after executing around ~9800 in the first and another ~9800 records in the second insert statements, the OLEDB Source will take around 3 or 4 minutes to fetch another set of ~9800 records. I have set the DefaultBufferMaxRows property of the Data Flow to 10000. My query to retrieve those 700,000 records runs for about 2-3 mins to finish (which I think should be decent enough). Is this an expected behavior of SSIS? The expected number of records to be retrieved is 700,000, and it takes forever to finish the transfer of these records. Please help

View 4 Replies View Related

Data Flow Source For Dbf (DBASE IV) Files

Aug 22, 2005

We're trying to read DBASE IV files as a source, but can't find any providers for that format. Will these be included in the final release? Is there another way? DBASE has always been supported, so it's kinda stranged.

View 19 Replies View Related

Data Flow Source Script Component

Dec 3, 2007

I'm wondering if it is possible to create a flat file source on the fly while bypassing the following step:

On the Connection Managers page, add or create the Flat File connection manager, using a descriptive name such as MyFlatFileSrcConnectionManager. Then close the Script Transformation Editor.

I want to create the connection totally in script, yet i'm having a hard time proving this out...does anybody have any
experience with this?


View 4 Replies View Related

Using A Stored Procedure As A Data Flow Source

Dec 11, 2007

Simple question - can I use an external stored procedure for my data flow source. I assume that I can use the OLEDB connection and go from there?

Can I use SQL command and do exec sp_thisprocedure?

Thanks for the information.

View 5 Replies View Related

Data Flow Task - OLEDB Source / Destination

Nov 9, 2006


Inside a data flow task, i have a oledb source and destination. In my situation, I need to pull data from a table in the source, but also hard code some columns myself, which means my source is a blend of data from table, hard coded data, which will then have to be mapped to columns in oledb destination. Does anyone which option to choose in the oledb source dropdown for the data access mode. Keep in mind, i do need to run a a select query, as well as get data from a table. Is it possible to use multiple oledb sources and connect to one destination, because that is really what intend to do here. I am not sure how it will work, or even if its possible. Basically my source access mode needs to be a blend of sql command and table columns, how would that be implemented? Any help or advice is appreciated.


View 4 Replies View Related

Error Handling In OLEDB Source In Data Flow

Sep 11, 2007

I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?

exec usp_Inert_Registration_Episodes_Assessments






@Registration_Dt=? ,

View 16 Replies View Related

Validation Error. Data Flow Task: OLE DB Source [94]:

Nov 8, 2007

Dear all,

I am trying to executed a packege so that it loads data from from the excel file to the SQL Server Server database.
When I execute it, it prompts the following error message and 1 warning
The excel file has three colums, Week, Item and Value

Error 4 Validation error. Data Flow Task: OLE DB Source [94]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E37 Description: "ORA-00942: table or view does not exist ". Test - GET NW PERF 1.dtsx 0 0


Warning 1 Validation warning. Data Flow Task: OLE DB Destination [36]: The external metadata column collection is out of synchronization with the data source columns. The column "DAY" needs to be added to the external metadata column collection. The column "TCH_AVAIL" needs to be added to the external metadata column collection. The column "PDROP" needs to be added to the external metadata column collection. The column "P_HR" needs to be added to the external metadata column collection. The column "SFAIL" needs to be added to the external metadata column collection. The "external metadata column "VALUE" (90)" needs to be removed from the external metadata column collection. The "external metadata column "ITEM" (89)" needs to be removed from the external metadata column collection. Not in use - GET NW STATS.dtsx 0 0

Could someone give me a hand here.



View 1 Replies View Related

Data Flow Source That Will Take A String Variable With Csv Structure?

Dec 21, 2006

I am able to use a custom script task to receive a MSMQ package and save the package contents to a flat file.

I can also use the bulk load task to push the flat file contents into a SQL table.

However, I would like to save the package contents to a variable (done, it works), and then pass that string variable to a data flow task for SQL upload. In other words, I don't see any reason to persist the msmq package contents to disk.

My question is: Which data flow source can I use that will accept a string variable? The string variable will then need to be processed with bulk load or an execute sql task.

Btw, the content of the string variable is a csv style string:




View 3 Replies View Related

Basic Text File Data Flow Source

Jun 7, 2007

In DTS 2000 I had a situation where I had a text file as input source and text file as output source. On migrating the package to 2005 it puts a wrapper around it which executes it as a 2000 package, the rest of the tasks are neatly converted to 2005 style tasks. I presume this to mean that this will not be supported through to the next version, and there is no direct equivalent in 2005.

My question is how do I import a non-flat file source which has different numbers of columns per line. I did ,somehow, manage to do this with 2000 but cannot seem to get anywhere with 2005.

The flat file source seems to be expecting a common number of columns and just can't seem to cope with no column delimiters on some lines. If anybody knows different I would be glad to hear about it.

Raw data is not helpful to me as only works with a specific raw type (apparently)

Went onto Bulk Insert Task but got this message

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file.".

Have already browsed with this on web but only find comments about changing timeout setting.

Can find timeout settings in DataFlow source and DataFlow destinations but not in Bulk Insert Task.

As you can see this is a long and protracted question.

If the answer is simple I apologise if not blame Microsoft. Other than that have found SSIS has some nice improvements, apart from the odd vague error message I keep coming across.

View 13 Replies View Related

Custom Source Adapter UI

Jun 22, 2006

I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).

My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?

Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.


View 3 Replies View Related

OLE DB Source Adapter &&amp; Variables.

Jan 4, 2007

Hi All,

Using a data access mode of SQL Command, I have a stmt that returns one date ( e.g select max(LastChangedDate) from TblA ).

I want to store the result in a variable. How do I do this?



View 13 Replies View Related

Using An ODBC Data Source In A Data Flow

Oct 2, 2006

Okay, this should be really simple but I don't get it. How do I use an ODBC data source in an SSIS data flow task? When I look at Data Flow Sources I see the following options:


DataReader Source

Excel Source

Flat File Source

OLE DB Source

Raw File Source

XML Source

Which one do I use if I need to get the data from a connection manager that is ODBC based? The IBM OLEDB driver for the AS400 doesn't work correctly so I HAVE to use an ODBC driver to connect to an AS400 data source.

Thanks in advance for any info.

View 1 Replies View Related

Fail To Execute Store Procedure In OLD DB Source In Data Flow

Jun 20, 2006

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.

View 13 Replies View Related

Parameters In Data Flow Task With Oracle Database Source

Jul 9, 2006

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

View 7 Replies View Related

Reading Dates From An XML Source Adapter

Oct 30, 2007

I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field.
The XSD defines the field as:-

<xs:element minOccurs="1" name="DateTime" type="xs: string" />

The XML looks like this:-

<DateTime>12/31/2007 10:12:14.123</DateTime>

And the database table column is defined:-

[DateTime] [datetime] NOT NULL,

So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP].
However, when I run the package I get:-

Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

So what how do I fix it?
I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.

I'd be happy to change the XSD to:

<xs:element minOccurs="1" name="DateTime" type="xs:dateTime" />

But the "XML Data Flow Source" doesn't seem to support the dateTime data type.
Any suggestions appreciated. Thanks.

View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved