OLEDB command in SSIS

I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.The OLE DB command executes for every row in the data flow. Are you sure you're stating the correct component?

You should use an OLE DB Source component hooked to an OLE DB Destination component to move your data.|||I want to delete some data while transfering from one to another i.e i want to do cleaning.|||If you want this to be fast, try writing the keys for the rows to delete to a temporary table, then use an Execute SQL task to issue a batch DELETE statement. Since the OLEDB Command transform executes once for each row that passes through it, it will be slower than doing a batch.

OLE DB Destination not writing data

I am using OLE DB Destination to write data to a SQL server database. However, nothing is written to the database though there is no error reported. See the following output:

SSIS package "Tbl_Dim_Dates.dtsx" starting.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Tbl_Dim_Dates, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Tbl_Dim_Dates, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Tbl_Dim_Dates, DTS.Pipeline: Execute phase is beginning.

Information: 0x402090DF at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has started.

Information: 0x402090E0 at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has ended.

Information: 0x40043008 at Tbl_Dim_Dates, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Tbl_Dim_Dates, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Date extract to file" (924)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Raw File Destination" (2518)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "OLE DB Destination" (2396)" wrote 3652 rows.

SSIS package "Tbl_Dim_Dates.dtsx" finished: Success.

The program '[2708] Tbl_Dim_Dates.dtsx: DTS' has exited with code 0 (0x0).

this might be a bug. try recreating the data flow.|||

You should use SQL Server Profiler to see what is being sent to the database and what happens within SQL Server. We are pretty much just a client of SQL in such cases, and that's a good to place to start looking for the problem.

Donald

|||

Thanks for all your advices.

ghe

Old FSO snippet of code

Hi everyone,

Does anyone has ever written VbScript .Net stuff which do anything like that? I've got a dtsx package and I'd need covering along folder looking for subfolders and so on

objFSO = CreateObject("Scripting.FileSystemObject")

objFolder = objFSO.GetFolder("c:\test")

colSubfolders = objFolder.Subfolders

For Each objsubfolder In colSubfolders

...

...

Thanks in advance and best regards,

System.IO.FileSystemInfo is what you need

http://msdn2.microsoft.com/en-us/library/system.io.filesysteminfo.aspx

-Jamie

|||The .NET Version of this would be.

Dim Directories As String() = System.IO.Directory.GetDirectories("C:\test")
Dim I As Int32
For i = 0 To Directories.Length - 1
Dim TmpInfo As New System.IO.DirectoryInfo(Directories(i))
TmpInfo.Delete(...
Next

Links embedded in code go to documentation for related classes. Please see them and look at the "methods" list for a complete list of method's available to you.
Hope this helps,.|||Tons of thanks Jamie and MarcD.