How does the process distinguish between the latest data and duplicate data?
Is there a date as part of the row or is it just the last row wins?
Kirk Haselden
Author "SQL Server Integration Services"
SSIS package "ImportAL3.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [872]: Truncation may occur due to inserting data from data flow column "SampleID" with a length of 4000 to database column "SampleID" with a length of 10.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [872]: Truncation may occur due to inserting data from data flow column "SampleID" with a length of 4000 to database column "SampleID" with a length of 10.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002785.AL3" has started.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x400490F4 at Data Flow Task, LookupGrade [2832]: component "LookupGrade" (2832) has cached 11 rows.
Information: 0x400490F4 at Data Flow Task, LookupTestID [5608]: component "LookupTestID" (5608) has cached 0 rows.
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [872]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002785.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002785.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002786.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002786.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002786.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002787.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002787.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002787.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002788.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002788.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002788.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002789.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002789.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002789.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002790.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002790.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002790.AL3" has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (872)" wrote 6 rows.
Warning: 0x80019002 at Data Flow Task: 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.
Task failed: Data Flow Task
Warning: 0x80019002 at ImportAL3: 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.
SSIS package "ImportAL3.dtsx" finished: Failure.
You have quite a few errors there.
You might want to replace the SQL Server destination with an OLE DB Destination.
It seems that you have the right idea using the lookup transformation. The red arrow will direct non-matches.|||Thanks for the help, that worked!
Actually, I get just one error - the rest are just truncation warnings. I don't see any need to address them - if the file has a >4,000 character key, it's been corrupted, and I want it to fail in that case! The one error that I did get, is solved by using an OLE DB destination. (I don't intend on scanning all files, every time, normally I should get no duplicates, but for the sake of error tolerance I had to put a check in.)
Now I'm doing a bit of reading to see just why the SQL Server Destination fails in such a case - it emphasises BULK transfers, I'm not sure of the significance of this.
Another dumb question, is there any way to post graphic files here? Since coding is being replaced by a graphical interface, you can't really post the code here.
|||You need to have your SQL Server destination database running on the same machine that the package is stored on. Also, a SQL Server destination won't perform any automatic type conversions -- you have to be sure that the metadata going into it is an exact match for the SQL Server table data types.|||Well, destination database is on the same machine. And when I went to the datatable, cleared out the records in question, and removed the SSIS module that checked for duplicate keys, and just went straight from the flat files into the database, it worked fine.
Hello,
I'm wondering how to use the dtUtil command to deploy an ssis package but also include any of the miscellaneous files, such as some dtsconfig files. I'd like to be able to use the dtUtil command to specify where to put any "dependencies" that I have. If I create a deployment utility from BI Studio, I have the option on the third screen to choose the folder for dependencies, how come I don't see any parameters for that on the dtUtil command? Is it possible, or do I HAVE to deploy using the deployment utility created for me by BI Studio?
Thanks in advance,
Andy
When deploying to a file system /copy option should work for dependencies as well.
For example:
dtutil /File c:\2.reg /copy File;c:\3.reg
|||[quote]Hello all,
I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.
Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?
Any suggestions GREATLY appreciated.
I won't go into the reason I have my servername in 80 places...
Andy
The .dtsconfig files are XML files - i.e. mostly text, but sometimes you need to be careful with the encoding (typically utf-8) to keep it valid XML. I have no experience with WinGrep, or why it thinks it is "binary". I would suggest using XML-aware tools to modify .dtsconfig.|||Thanks for the tip Michael. Do you know if notepad is XML-aware? Because that's the only tool I used to edit them.
Also, I tried just creating a clean .dtsconfig file from the package configurations wizard and then pointing WinGrep at that file and I got the same thing, saying it was binary. I didn't modify it at all outside of the wizard.
|||The .dtsConfig file is a text file, in UTF-8 encoding. If you don't configure any localized (non-English) properties, UTF-8 is identical to ASCII encoding - in that case any text processor can work with it. Notepad can typically detect the encoding correctly even if you have non-English properties, so it should be OK in any case.Again, I have no idea why WinGrep thinks it is binary, maybe because it is just one line.|||
Hi Michael,
You hit the nail on the head with that last sentence. I just put a carriage return in the file so it was more than 1 line and now WinGrep doesn't think it's binary anymore. Weird. Anyway to make it so that a configuration file is created on more than 1 line?
|||Ok, so in order to make it so it's not just 1 line, in BI studio, with your config file open in then center pane and your cursor inside the file, go to Edit/Advanced/Format Document. This will format the document better as well as put it on more than 1 line. Now WinGrep has no problem.
Could anybody help? I'm having a problem when I run my package.
[Flat File Source [524]] Error: The column data for column "IndicatorGroupID" overflowed the disk I/O buffer.
[Flat File Source [524]] Error: An error occurred while processing file "\\192.168.0.47\Temp\detail-2864EGGPUSH-20060315.txt" on data row 1.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (524) returned error code 0xC0202092. 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.
Please help...
:)
Try directing all erroring rows into into an error sink using the error output from your flat file source so you can examine them later. I imagine you have some erroneous data in there.
--Jamie
|||Thanks! I've already found the error. You were correct, I was not able to filter my loop. As it happened, it looped through all the files in the directory, even through the unparsed ones.I only needed it to loop through the parsed ones. Thanks again!This DTS is executed by a job on the SQL Server, and the files are created on a network share with access to the SQLServer user that executes the job.
I've been trying to find out information on how to zip the folder and I've come up with two options:
1. install winzip on the server and run winzip32.exe passing arguments to it.
2. use the standard zipfldr.dll that comes with Windows to do the job.
Number one is out of my reach since IT department don't want the software installed, I have to stick with number 2.
Now I've read that using rundll32.exe I can run the dll as an exe, but I need the entry point to the dll wich I cannot find besides the RouteTheCall %L, which actually opens an explorer window with the folder or zip file I pass to it as an argument.
Does anyone know if this is possible either using rundll32.exe or automating the task through an AttiveX task?
TIA.
I would really like to find the answer to this question as well but, I don't see any replies and I noticed that this posting is 8 months old. I also could not find any information on the web as of yet. If I find anything I will respond to this post.
Steve
|||I recently think of the 2nd method. But it's too difficult. If you find how to use the zipfldr.dll .Please tell me. Thanks a lot. And , I will tell me if I get a clear using.|||I programming to compress the data. I want to call am usable zipfldr.dll with C#.NET programming, eager to know that must use which order in the calling uses. please tell me at jenkarn_7@.hotmail.com, thanks.
|||I just searched for "zip file" on msdn... http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/567402.aspx
|||Easiest method of compressing files is to use rar.exe (WinRar CLI).rar -a C:\FilesToCompress\*.xxx C:\MyCompressedfile.rar
Can run that through a CLI in SSIS and DTS.
This DTS is executed by a job on the SQL Server, and the files are created on a network share with access to the SQLServer user that executes the job.
I've been trying to find out information on how to zip the folder and I've come up with two options:
1. install winzip on the server and run winzip32.exe passing arguments to it.
2. use the standard zipfldr.dll that comes with Windows to do the job.
Number one is out of my reach since IT department don't want the software installed, I have to stick with number 2.
Now I've read that using rundll32.exe I can run the dll as an exe, but I need the entry point to the dll wich I cannot find besides the RouteTheCall %L, which actually opens an explorer window with the folder or zip file I pass to it as an argument.
Does anyone know if this is possible either using rundll32.exe or automating the task through an AttiveX task?
TIA.
I would really like to find the answer to this question as well but, I don't see any replies and I noticed that this posting is 8 months old. I also could not find any information on the web as of yet. If I find anything I will respond to this post.
Steve
|||I recently think of the 2nd method. But it's too difficult. If you find how to use the zipfldr.dll .Please tell me. Thanks a lot. And , I will tell me if I get a clear using.|||I programming to compress the data. I want to call am usable zipfldr.dll with C#.NET programming, eager to know that must use which order in the calling uses. please tell me at jenkarn_7@.hotmail.com, thanks.
|||I just searched for "zip file" on msdn... http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/567402.aspx
|||Easiest method of compressing files is to use rar.exe (WinRar CLI).rar -a C:\FilesToCompress\*.xxx C:\MyCompressedfile.rar
Can run that through a CLI in SSIS and DTS.
This DTS is executed by a job on the SQL Server, and the files are created on a network share with access to the SQLServer user that executes the job.
I've been trying to find out information on how to zip the folder and I've come up with two options:
1. install winzip on the server and run winzip32.exe passing arguments to it.
2. use the standard zipfldr.dll that comes with Windows to do the job.
Number one is out of my reach since IT department don't want the software installed, I have to stick with number 2.
Now I've read that using rundll32.exe I can run the dll as an exe, but I need the entry point to the dll wich I cannot find besides the RouteTheCall %L, which actually opens an explorer window with the folder or zip file I pass to it as an argument.
Does anyone know if this is possible either using rundll32.exe or automating the task through an AttiveX task?
TIA.
I would really like to find the answer to this question as well but, I don't see any replies and I noticed that this posting is 8 months old. I also could not find any information on the web as of yet. If I find anything I will respond to this post.
Steve
|||I recently think of the 2nd method. But it's too difficult. If you find how to use the zipfldr.dll .Please tell me. Thanks a lot. And , I will tell me if I get a clear using.|||I programming to compress the data. I want to call am usable zipfldr.dll with C#.NET programming, eager to know that must use which order in the calling uses. please tell me at jenkarn_7@.hotmail.com, thanks.
|||I just searched for "zip file" on msdn... http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/567402.aspx
|||Easiest method of compressing files is to use rar.exe (WinRar CLI).
rar -a C:\FilesToCompress\*.xxx C:\MyCompressedfile.rar
Can run that through a CLI in SSIS and DTS.
I've got the following problem:
I'm using DTS to transfer fata from csv files into a table.
In the file there are often duplicated rows, therefore I made a
trigger
on the table.
The trigger works fine when i tried in query analyzer, but it not
launched
from the dts.
I've learned that i have to turn of the fast load option in the
properties of the transform data task object.
When I turn this option off, the task will fail with the following
message:
"The number of failing rows exceed the maximum specified.
Cursor operation conflict."
I wonder why this happens. There are no check constraints on the table
against duplication.
Thanks in advance for any helpBijesz wrote:
...
> "The number of failing rows exceed the maximum specified.
> Cursor operation conflict."
> I wonder why this happens. There are no check constraints on the table
> against duplication.
I may have an answer:
Let's assume you've saved your DTS job on the SQL Server, so that when
you are in Ent. Mgr. you can click on the DTS folder, click on the
'local packages' icon, and find your saved DTS job there.
Once there, you'll see the DTS job displayed as a set of 3 objects: 1)
the Excel icon for your source; 2) an arrow pointing to your MSSQL
destination, and; 3) your MSSQL destination.
If you right-click on the Excel or MSSQL objects and open the
Properties window, as I did, you'll be perplexed.
It finally occurred to me to right-click on that arrow and open-up yet
another Properties window there.
Once there, if you click on the 'Options' tab, you'll find a 'Max Error
Count' property. Nice place to hide it, eh?
hth,
victor dileo
