SSIS Data Flow Batch Question

May 2, 2007

I am loading a lot of Excel and CSV files to SQL Server. Some loading may fail for various reasons. I want a file either be load as a whole or nothing. Currently I keep a list of failed filename and remove it at the end (I add a column for source file name).



Any better way to make sure a file is loaded as a whole or nothing?



Thanks,

View 3 Replies


ADVERTISEMENT

SQL Batch In Data Flow

May 9, 2008

I am learning SSIS, I am try to figure out how to run a SQL batch that returns a result set and export it to Excel using a Data Flow. I am using a OLE Db Source with a SQL batch shown below and the Destination Query is an Excel file.

How is this done?

So far I have had no luck getting the tasks to run. I need more than just simple queries.

The SQL is below:



SET NOCOUNT ON

DECLARE Tables CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE != 'VIEW'

DECLARE @RowCount INT

DECLARE @TableSchema NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @SqlCmd NVARCHAR(1024)

CREATE TABLE #temp ([Name] NVARCHAR(128), [RowCount] INT)

OPEN Tables
FETCH NEXT FROM Tables INTO @TableSchema, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlCmd = 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'', COUNT(*) FROM [' + @TableSchema + '].[' + @TableName + ']'
PRINT @SqlCmd
INSERT #temp EXEC sp_executesql @SqlCmd
FETCH NEXT FROM Tables INTO @TableSchema, @TableName
END

SELECT * FROM #temp

CLOSE Tables
DEALLOCATE Tables

DROP TABLE #temp

View 4 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

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

How Do I Call A Stored Procedure To Insert Data In SQL Server In SSIS Data Flow Task

Jan 29, 2008



I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks

View 6 Replies View Related

Xml Ssis Data Flow Component?

Jul 2, 2007

There is a table with a column that contains Xml documents. For each record from my Data Flow Source, I want to pass in the Xml document and the node to interrogate, and return the value contained in the node. Like the Crm component, this is probably one I will have to write from scratch in C#, but I would like to avoid having to create the custom component if it already exists in the public arena.



Does anyone know of any Xml Ssis Data Flow Components that are downloadable for free?

View 3 Replies View Related

SSIS Ate My Data Flow Task!

Sep 7, 2007

I was working all day making changes to my 3MB package. I was adding a large number of transforms that were copied-and-pasted from elsewhere in the same data flow task.

All was going well. I even took the time to have SSIS lay out the task again (1/2 hour). Suddenly I started receiving some strange errors:



After the layout, I noticed two stray components 'way off in the upper right corner. I found that one of them had a duplicate name to a component which had been added hours ago. Even after deleting it, I got "duplicate name" errors.

I copied three components in one selection, and when I tried to paste them, got the error "can't initialize component on paste". I tried them one at a time, but got the same error.

I got errors about COM failures due to marshalling to another thread
I then exited Visual Studio and started it again. To my great surprise, the data flow task I was working on was still there, but was completely empty.

Comparing what I'm left with to my last version in source control, I find that the entire pipeline element is missing from the DTS: ObjectData element!

I'm developing a real love/hate relationship with SSIS. It varies from one day to the next. Guess what kind of day this is!

View 3 Replies View Related

SSIS Data Flow Parameterize Query

Jun 1, 2006

Hi,

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

SSIS Data Flow Task Problem

Sep 28, 2006

Hi, there,

I am having some problems with the loading of tab delimited text file (source) to a SQL Server table (destination) using the SSIS data flow task. Package has been executed successfully with no error msg. The number of rows in the text file also matches the number of rows in the SQL table. But, when I check the content of the table, I noticed some of the columns contain NULL which supposed to have value. This happens not to all the rows but only to some rows. I did some testing by removing some rows from the beginning, middle and end of the text file and re-run the package but the result is quite inconsistent. Sometimes, the field got filled, but sometimes, it just contains NULL where it supposed to have value.

What will be the possible causes to this?

Thanks in advance.

Regards,

Yong Hwee

View 2 Replies View Related

SSIS DATA Flow Task Freezing

May 26, 2008

Hi,

I am experiencing an error where the ssis data flow task would freeze and stop data export from a oledb source to a text file. It doesn't generate any errors the ssis package would just hang. This only happens when I run it in 64 bit mode. When I change the mode to 32 bit the ssis never freezes and runs fine. Has anyone experience this? Is there a fix so I can run my jobs in 64 bit mode?

Thanks,

View 5 Replies View Related

SSIS Data Flow Script Compoment

Sep 19, 2007



Hi ,
Is there any option to use OLE DB Source in Script Component?

Thanks
Aravind.

View 5 Replies View Related

SSIS Error With Data Flow Task

Feb 29, 2008

Hi!

When I add a Data Flow Task to a package, save it and then close. When I open it again, I get 3 errors and the detail of the Data Flow Task is empty.

I had try to uninstall and install my SQL Server 2005, but the problem continues.

Can some one help me on this?

Sérgio Cardoso

View 4 Replies View Related

How To Disconnect Data Flow's Components Using SSIS API?

Sep 29, 2007

Hi,

I have a SSIS Package which I would like to modify using SSIS API. I need to put new component between some two existing data flow's components. During this process I need to disconnect two data flow's components using SSIS API. How can I do that?

Thanks,
Rafal

View 1 Replies View Related

SQL 2012 :: SSIS Data Flow With Case Statements

Oct 29, 2014

I would like to know how I can add the following sample code to my Source data on Data Flow on SSIS, or what other options there are. The main issue is time as we have talking about 100's of millions of rows

select Sample,
CASE
WHEN Sample IS NULL
THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL
THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable

what I have done at this stage is just to Create a SQL task with a Insert into

INSERT INTO [dbo].[TestTable1]
([Sample]
,[Sample_1_6])
select Sample,
CASE WHEN Sample IS NULL =THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable

If there is a way adding this to a dataflow so I van use fast load that would really be the best solution. I know there are derived columns, but would this really be faster than the straight insert into in a SQL Task? If this is the way to go what is the code I would use in the derived column or any other option.

View 7 Replies View Related

SSIS Failure - Can Not Add A Row To The Data Flow Task Buffer

Apr 18, 2006

I have a relatively simple SSIS package that I'm building for a data mining process. The package starts with an OLE DB data source, passes the results of a SQL Command (query) along to a conversion step, which then gets sent to a Term Lookup task. The Term Lookup then writes the result to an OLE DB Data Destination. Pretty simple. The OLE DB data source query returns about 80,000 rows if you run it through SQL WB. The SSIS editor shows 9,557 rows make it out of the source, and into the conversion step, 9,557 make it out of the conversion and into the lookup, and about 60,000 rows make it out of the lookup and are written to the results table. Then the package fails with the following errors listed on the progress screen. I was assuming that the 9,557 was some type of batching that was occurring in the process, but now I'm not so sure.

Thoughts?

Frank

[DTS.Pipeline] Error: The ProcessInput method on component "My Component" (117) failed with error code 0xC02090E5. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC02090E5.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[My Data Source Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "My Component" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

View 2 Replies View Related

SSIS Partition Processing Data Flow Item

Feb 12, 2007

Does anyone have a helpful link for using the partition processing data
flow task in SSIS? I am trying to process a monthly partition
from within my package and am getting the following error:



Error: 0xC113000A Errors in the high-level relational engine. Pipeline
processing can only reference a single table in the data source view.



If anyone has used this before and could point me in the right direction, I would appreciate it.



Thanks,

Nick

View 3 Replies View Related

SSIS Data Flow Task Execution Priority

Apr 13, 2007

I have a package that loads staging tables from an Oracle source DB. In the data flow tab I have 30+ read table/write table task combinations. When I run the package 3-4 of the read/write combos execute at a time. What I'm trying to control is the priority order of the combo execution. My goal is to minimize to total load time by having the larger table transfers run first and the smaller table transfers fill in until they are all complete. Currently, the largest table (16 million) transfers last (because it was the last combo that I created?).



Thanks,

Dave

View 1 Replies View Related

SSIS Sequence Container,data Flow Task

Nov 28, 2005

I am creating a staging database in which I am loading required tables from 2 different sources.
I have 30 different tables to load from source 1 and 10 different tables from source 2.
This is the way I am doing, in Control flow task I am using Sequence container and in that I included the data flow task, the data flow task
has source OLD DB connection from where I select the table and then destination OLE DB connection where I load the data.
So for 30 tables I have one Sequence container with 30 different data flow task and each data flow task has OLE DB source and OLD DB destination.
I wanted to find out if this is the efficient way to do, or if there is any other way to do this.
And for source 2 shall I put in another package or shall I use the same package with different sequence container and follow the same steps
as for Source 1 tables.
Please advice.
Thanks,

View 19 Replies View Related

SSIS Data Flow Execution Monitoring And Logging

Aug 1, 2006

Has anyone come up/determined a generic way to capture and log indicative information within a data flow in SSIS - e.g., a number of rows selected from the source, transformed, rejected, loaded, various timestamps around these events, etc.? I am trying to avoid having to build a custom solution for each of the packages that I will have (of which there will be dozens). Ideally, I'd like to have some sort of a generic component (such as a custom transformation) that will hide the implementation details and provide a generic interface to the package.

It is not too difficult to achieve something similar on the control flow level, but once you get into data flows things get complicated.

Any ideas will be greatly appreciated.

View 5 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 2005 SP2 - Cannot Open Data Flow Task In SSIS

May 17, 2007

I have just installed Service Pack 2 on my SQL 2005 Standard Edition.

However, now all my SSIS packages will not allow me to open my Data FLow Tasks. I get the following error:



TITLE: Microsoft Visual Studio

------------------------------

Cannot show the editor for this task.

------------------------------

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)



If I try to create a new Data Flow task I get:



TITLE: Microsoft Visual Studio

------------------------------

Failed to create the task.

------------------------------

ADDITIONAL INFORMATION:

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)



I have tried to install the latest hotfixes after this but they had no effect.

Can anybody help me???? Please?



View 10 Replies View Related

Logging Data Flow Steps In SSIS Package

Jul 14, 2006

Hello.

I am using the "SSIS Log Provider for SQL Server" to log events to a table for "OnError" and "OnPostExecute" events of a package. This works as expected and provides a nice clean output on the execution steps of the package.

I am curious as to why I do not see any detail for any/all tasks that fall under the "Data Flow" section of the package though. For instance, on my "Control Flow" tab, I added a "Data Flow" task that simply loads a few tables from a target to destination server. However, there is nothing shown in the logging output. Just that a Data Flow task was initiated. And when I'm configuring this logging under "SSIS-->Logging" in the checkbox area on the left, you cannot "drill into" data flow steps.

Is there a reason why there is no detailed logging for Data Flow tasks? Would getting to that require me to create a custom log provider?

Thanks for the help.

Greg

View 1 Replies View Related

Multiple Data Flow Tasks In One SSIS Package

Jul 25, 2007

What are the advantages and disadvantages of having multiple data flow tasks in one SSIS package?



Is this a good idea at all considering the workflow may be similar now but may change in the future? Should it be left as one data flow per package?

View 1 Replies View Related

Logging Data Flow Component Events In SSIS

Nov 14, 2007

Does anyone know how to hook up to data flow pipeline events via custom solution (C#)? I am trying to write code to log start and end times of components(lookup,merge joins etc) in a data flow task. I tried with a class inheriting from the EventsProvider class but it didn't work as this is only for container tasks. Any ideas will be greatly appreciated.

View 3 Replies View Related

SSIS Programming Setting Name To Data Flow Component

Aug 28, 2007

Hi,

I am creating a SSIS Programmatically. I facing problem in setting name to data flow compenent.

here is the sample code

IDTSComponentMetaData90 oledbItemMasterSource;

oledbItemMasterSource = dataFlow.ComponentMetaDataCollection.New();



oledbItemMasterSource.ComponentClassID = "DTSAdapter.OLEDBSource";

oledbItemMasterSource.Name = "InventTable";

oledbItemMasterSource.Description = "Get Item Master";



After creating this package i found that 'InventTable" is not set to OLE Db Source component. where as i can set name to OLE DB Flat File destination component.

Kindly suggest me a way fix this issue.

Thanks-
aravind

View 1 Replies View Related

Reuse Existing Data Flow Components In A Custom Data Flow Component

Aug 29, 2007

Hello,

Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?

Thanks,

Yoann

View 15 Replies View Related

Create A Data Flow In SSIS Wich Updates Som Rows.

Aug 14, 2006

Hi,

I have a table customer wich has the columns phone_number(char type) and ok_to_call(bit type). There are already data in the table and the column ok_to_call only contains the value false for every row.

Now i want to update the latter column. I have a text file with a list of phone numbers and i want that all the rows in the Customer table(phone_number column)that matches the number in the text file to update ok_to_call to true.

This is to be done in SSIS(Integration Services). I'm new at this and i've looked around that tool but is a lot of items, packages and stuff so i dont know where to begin.

Would appreciate help on how to solve this issue in SSIS. What controlflow/Data flows to use,wich items and packages to use, how to configure and how to link together?

Regards
/Tomas

View 3 Replies View Related

How Can I Append Children To Parents In A SSIS Data Flow Task?

Apr 11, 2007

I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.



In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk



The external agency can only use a delimited file looking like

p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b



where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.



How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.

View 4 Replies View Related

Transact SQL :: Get Back (Identity) ID To The Other Table In SSIS Data Flow?

Jul 17, 2015

Table 1:

-------     -----                ----          ----
Name       Add                  No         RowID
-------     -----                 ----         -------

aa     #a-1,India                              10
bb     #a-1,India                              11                
aa     #a-1,India                              12

----------------------------------------------------

 table 1 inserting to Table 2 (Using 1st Data flow)

Table 2:

-------     -----                ----
Name       Add                 ID(Note:Here Identity1,1)
-------     -----                 ----
aa     #a-1,India                 1
bb     #a-1,India                 2
aa     #a-1,India                 3

----------------------------------------------------

My Requirement is Update  Table 1 set Column::No=Table 2.ID
                                                                       
based on Exact Match of
                                                                        
Table1.Name=Table2.Name  and
                                                                        
Table1.Add=Table2.Add

It means Get back the Id for Source Table 1

 2nd Data flow
             Source(Table1:Name, Add,No)
                          |

   --LOOKUP(Table2:Name, Add::Matched Look Columns Name, Add and
Tick Mark on ID)
                         |(Match)

   -->OLEDB Command: update Table1 set N0=? where  RowID=?(Here Param_0= NO ,Param_1=RowID)

Here My Issue is if  Table 1 had Duplicates(same Name, Add, but Row Id is different it is Updating Same ID for Table 1.No It means Get Back ID correctly not updating Result::

Table 1:

-------     -----                ----          ----
Name       Add                  No         RowID
-------     -----                 ----         -------
aa     #a-1,India                1              10
bb     #a-1,India                2              11                
aa     #a-1,India              1 12

----------------------------------------------------

My correct Output is     3      instead of Result:Table1 1.NO  1   where RowID =12

It caused by LOOKUP , It picking Top1 ID while Matching Look on fields.

How Should I update the (Identity) Get Back Table 2.ID to Source Table1. NO  in Above logic in SSIS?

View 11 Replies View Related

SSIS Package (Data Flow) Validation Time Going Exponential

Jul 25, 2007

I am using SSIS to populate a star schema.

The issue is in the data flow for loading and setting the Fact table dimension keys (the dimensions are all loaded fine). After 16 rather pedestrian Lookup Transformations, I have an escalating problem adding additional Lookup transforms to the Data Flow. The problem is not in execution; the problem is adding more transforms in design mode.

Lookup # Fields in Data Flow Time to validate that lookup
<17 47 Sub-second
17 48 2 sec
18 49 4 sec
19 50 8 sec
20 51 16 sec
21 52 32 sec
22 53 64 sec

While I€™m intrigued by the mathematical progression that is forming here, the issue is that I have at least 6 more Lookups to perform. I hope you can see my dilemma.

I have gone to where it takes a little over 4 minutes each to validate the lookup transform and its associated Derived Column transform and Union transform (Total 12 Minutes). Not only does this add up to many idle minutes to each design step, BUT it breaks the debugger as it pre-validates the ENTIRE data flow before it ever switches into debugging mode.

Some notes:
1. It doesn€™t matter what order the Lookup transforms occur in, the timings are exactly the same.
2. I tried many Data Flow execution optimizations, but they don€™t improve the validation times (or even get a chance to improve the execution times!)


I realize this may be somewhat of a unique problem.



Thanks for any help you are able to lend.



-Dave

View 3 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

Multi-parameters Issue In Data Flow Task SSIS

Dec 4, 2006

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

View 1 Replies View Related

Query Results SSIS Data Flow Source Adapter

Jun 1, 2006

Hi,
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



View 3 Replies View Related







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