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 RepliesI 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
ADG
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 RelatedHI EVERYONE...!!
I WANT TO CREATE A DATA FLOW SOURCE ON ODBC TO SQL BUT A CAN'T FIND THE ODBC DATA FLOW SOURCE. SOMEBODY CAN SAY ME HOW DO THIS????
THANKS...!!!
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?
HI EVERYONE...!!
I WANT TO CREATE A DATA FLOW SOURCE ON ODBC TO SQL BUT A CAN'T FIND THE ODBC DATA FLOW SOURCE. SOMEBODY CAN SAY ME HOW DO THIS????
THANKS...!!!
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 Relatedhi 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,
tomer
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
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?
Ryan
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.
Hi all,
I'm trying to find some way to implement a move file task in my Data Flow. I parse through a flat file, retrieve a value, then I'm using a lookup table to check if that value exists in a table... if the value doesnt exist, I need to move this file to a new directory and insert the value into a different table. Does anyone have any ideas about how to move the file while using the Data Flow pane?
Or is there some way to pass information from the look up table to the Control flow pane and use the Move File Task there? I'm trying to stay away from a Script task or a Script component unless it's unavoidable. I appreciate any ideas!
Hi
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.
MA
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
@Unique_ID=?,
@Gender_Cd=?,
@Birth_Date=?,
@Race_Ind=?,
@Ethnicity_Cd=?,
@Registration_Dt=? ,
--
--@Object_Key
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.
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
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.
Regards,
Ronald
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:
"01001","11/21/2006",15
"01001","11/21/2006",1
"01001","11/21/2006",25
"01001","11/21/2006",3
Thanks,
Trey
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.
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:
Pointer
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.
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 RelatedHi,
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.
Thanks,
Gaurav
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.
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?
All,
I'm having an issue with the Flat File Data Flow Source returning only a limited set of the rows that are in the flat file. Basically, I connect to the flat file fine, it goes to retrieve the data (tab delimited file) and only returns 190 of 392 rows. Is there a limitation on the # of rows this data flow source can retrieve or something? I've look all through the settings and properties of the task as well as the connection manager and nothing is obvious as to what is causing this. Hopefully someone ou tthere has run into this before and can help me retrieve all rows. Thanks in advance!
bakerz
Hi all,
I have a package that does simple exporting from an excel sheet to a table.
I used a Dataflow task with Excel Source and OLEDB Destination Components.
And i created Package configurations for Source and Destination Components.
After than when i execute the package i get the following error.
Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:TEST_ETLLPL_Config2.dtsConfig".
Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:TEST_ETLDBCon2.dtsConfig".
SSIS package "ProductDetails_Import.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at ProductDetails_Import, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "ProductDetails_Import.dtsx" finished: Failure.
The program '[2416] ProductDetails_Import.dtsx: DTS' has exited with code 0 (0x0).
I have been trying to troubleshoot the error message given below from last evening.
I have been trying to troubleshoot the error from last morning.
Counld not figure out what is causing this error to occur.
Please help!!!!
Any pointersSuggestions would be highly appreciated.
Thanks & Regards
How do i use the foreach loop container and pass each file found according to a specified pattern to a Flat File Source in a Data Flow Task Object so i can operate on each file found in the foreach loop object instead of having to specify a static file name
Thanks
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?
Hi,
How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.
I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.
Any help appreciated.
Thanks
Naveen
We have a single generic SSIS package that is used to import several hundred iSeries tables into SQL. I am not looking to rewrite the process. But I am looking for ways to improve performance.
I have tried retain same connection, maximum insert commit size, lock table (tablock), removed some large columns, played with the log file location and size, and now I am working to tweak the defaultbuffermaxrows.
To describe the data flow task - there are six data flows tasks (dft) working at the same time. Each dtf has their own list of iSeries tables and columns and the corresponding generic SQL table names. Each dtf determines their list of tables based on the number of columns to import. So there is dft30 (iSeries table has 1-30 columns to import), dtf60 (iSeries table has 31-60 columns to import), etc. The destination SQL tables are generically called Staging30, Staging60, etc. Each column in the generic Staging tables are varchar(100). The dtfs are comprised of an OLE DB Source and an OLE DB Destination.
The OLE DB Source uses a SQL Command from Variable to build a SELECT statement. The OLE DB Source uses a connection manager that uses an IBM iAccess IBMDA400 provider. The SQL Command ends up looking like this for the dtf30. This specific example is importing from the iSeries table TDACLR and it only has two columns so it will be copied to the Staging30 table.
select TCREAS AS C1,TCDESC AS C2,0 AS C3,0 AS C4,0 AS C5,0 AS C6,0 AS C7,0 AS C8,0 AS C9,0 AS C10,0 AS C11,0 AS C12,0 AS C13,0 AS C14,0 AS C15,0 AS C16,0 AS C17,0 AS C18,0 AS C19,0 AS C20,0 AS C21,0 AS C22,0 AS C23,0 AS C24,0 AS C25,0 AS C26,0 AS C27,0 AS
C28,0 AS C29,0 AS C30,''TDACLR'' AS T0 from Store01.TDACLR
The OLD DB Source variable value looks like the following, but I am not showing the full 30 columns
select cast(0 AS varchar(100)) AS C1,cast(0 AS varchar(100)) AS C2,cast(0 AS varchar(100)) AS C3,cast(0 AS varchar(100)) AS C4,cast(0 AS varchar(100)) AS C5, ... cast(0 AS varchar(100)) AS C30.
The OLE DB Destination uses OpenRowSet Using FastLoad From Variable. The insert into Staging30 ends up looking like this.
insert bulk STAGE30([C1] varchar(100) ,[C2] varchar(100) ,[C3] varchar(100) ,[C4] varchar(100) ,[C5] varchar(100) , ... ,[C30] varchar(100) ,[T0] varchar(20)
Of course we then copy and transform the Staging30 data to the SQL table that equals T0.
But back to defaultbuffermaxrows. Previously the dtfs had default values of 10000 for DefaultBufferMaxRows and 10485760 for DefaultBufferSize. I added a SQL task to SUM the iSeries column sizes, TCREAS and TCDESC in this example, and set the DefaultBufferMaxRows by dividing the SUM of the columns max_length into 10485760. But I did not see a performance improvement. Do you think that redefining the columns as varchar(100) for the insert is significant? Should I possibly SUM the actual number of columns (2) as 2x100 or SUM the 30x100?
Hi,
I am trying to use linked tables to connect SQL Server 2000 to a legacy system using dbase III files. (I need real time read only access to these files)
I have created a linked table from SQL server to a folder on the C drive which contains the dbase III files, using an ODBC DSN which uses "Microsoft dbase driver (*.dbf)". DSN tested successfully using Excel. Linked server connection is then created using "Microsoft OLE DB Provider for ODBC drivers".
The dbase tables appear OK in Enterprise manager, but I cannot get a query to work in SQL Anayzer, using the 4-part name syntax.
My query is just :
SELECT * FROM LinkedTable...customers
Error message is "Invalid schema or catalog specified for provider 'MSDASQL'". Now I am pretty sure dbase files do not support any sort of schema / catalog set up, so I suspect SQL Server is looking for something it is not going to get.
One clue might be that in Enterprise manager, under the catalog column, I get the pathname to the dbase file, ie c:customers.dbf, which I cannot enter in the 4-part syntax.
Any suggestion welcome !!!
Has anyone had any trouble moving a package using a OLE DB Connection Manager reading DBASE IV files? While developing I never had a problem, the confiugration string described int his thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76237&SiteID=1) worked just fine. Since I have enabled Configurations, my package will always fail when trying to read the dbf file. I've gone through just about every setting in the config file that I can think of.
Information: 0x4004300A at MyDataFlow, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at MyPackage, Connection manager "MyDBASEIVConnManager": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at MyDataFlow, OLEDBFileSource [15]: The AcquireConnection method call to the connection manager "MyDBASEIVConnManager" failed with error code 0xC0202009.
Error: 0xC0047017 at MyDataFlow, DTS.Pipeline: component "OLEDBFileSource" (15) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at MyDataFlow, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at MyDataFlow: There were errors during task validation.
I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation.
The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path.
Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.
Hi,
I'm just starting off in SSIS and have a question that I can't find an answer to...
I'm loading in a number of files (in separate Data Flows) and performing some transformations on them before merging them back together. What I'm not sure about is what I should be doing with the data at the end of each of my "Import Data From XXXX Flat File" Data Flows. Am I better off using OLE DB Destinations (or SQL Server Destinations) and saving this intermediate data to temporary tables, or am I better off using a Raw File Destinations and saving this intermediate data to files? Or is there, perhaps, a better option that I'm currently unaware of?
If the Raw File Destination is the way to go, then isn't there a maintenance issue with cleaning up all the files created? And will there not be a management issue to ensure that there is sufficient disc space available on the drive you are saving to?
I'm a bit confused and overwhelmed by SSIS at the moment, so any help would be much appreciated!
Thanks in advance,
Lawrie.