.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Instruct DataFlow task to Redirect bad input rows to Log file?

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server
Hi all,

I am new to SSIS 2005 packages but have written many in DTS 2000.

I am importing a CSV flat file into a SQL server table with SSIS 2005. How do I tell the Dataflow task to redirect rows that don't have the proper number of columns into a Text log file so I can inspect the bad rows later?

I have pointed the Red Error arrow to a text file and defined all the Columns to redirect the error to the text file. I have set the Max error count to 200. However, the entire package fails at the first bad row in the PrimeOutput() method, indicating that Column 13(the last one in the Row) is missing in a particular row. I have tried setting Ignore the error, but that doesn't work either.

I have created a DTS 2000 package that works on the same CSV input and the same SQL table destination. It seems to have no problem with my instructions.

I must be missing something. Can someone tell me what it might be? I really would like to use SQL 2005 or even SQL 2008 because of the new features available, but I am stuck on what I thought would be a very easy first step (because it is very easy in DTS 2000).

Any help would be appreciated. Thanks...

View Complete Post

More Related Resource Links

Web Services Task Editor: The input Web Services Description Language (WSDL) file is not valid

I am trying to prove I can use SSIS to connect to a web service.  The WS I am trying to connect to was developed by a vendor and covered by a NDA, but I was able to reproduce the issue with a public WS. Here are the steps to reproduce the issue: In the Web Services Connection Manager, I entered http://office.microsoft.com/Research/Providers/MoneyCentral.asmx?wsdl in the URL window.  I am able to successfully "test" the connection I pasted the above link into IE and saved the resulting XML as a .wsdl file on my local machine.  In the Web Services Task Editor, General Tab, I specify the path to the .wsdl file and click on "Download WSDL" button.  No Issues When I click on "Input" and select "MoneyCentralRemote" from the drop-down for Service, I receive an error message saying "This version of the Web Services Description Language (WSDL) is not supported" So the questions are: Did I perform the above steps correctly? What WSDL versions are supported in SSIS? How can I tell what WSDL version was used to create the .wsdl I am trying to access? If the WSDL is an unsupported version, is there a work-around to fix the issue?

Join 2 flat file data flows - retain unmatched rows

I have two data flows from two separate flat files. They may contain matching IDs (account number), in this case specific data from each flow should be used to create one row. When there is no match, the rows would stand on their own. At the end of the flow, I need both flows combined into one flow, with one record for each key record (account number). If I were able to use a look-up, I could easily union the no-match data flow back into the match data flow and have the desired result. I cannot use a look-up, since the source is flat files, but this is exactly the functionality I am trying to achieve. Solutions I want to avoid: staging tables, and cache transformations. Any ideas are appreciated.

Method/Way to know whether a input path of FilesSystem contains any Child file/folder or not

Hi,I am looking for a Method/Way to know whether a input path of FilesSystem contains any Child file/folder or not. To know it currently I am using "Directory.GetFileSystemEntries(Path).Count" and checking if count > 0 then returning True else False. For this first method calculates all the files and folder and then return status to me but I want to exit out as soon as my method gets first element(file/folder) in specified path. Because above method is timing out when my input path contains more than 1 million files. I just need to check whether input folder path contains any child file/folder or not. I have tried to use Win32 API also but on ther server it is crashing so I donot want to use that solution. In .NET any method or way is present to know it. Please suggest me.

Respoce.Redirect from ashx file

Hi everyoneHow To  Redirect from httphandler(.ashx)  to an (.aspx) page in the same application ?Thanks.

The DataFlow task has no components. Add components or remove the task.

Hello, we have the following problem: 1. Simple DTS-Packet with very simple data flow "Source -  Destination" from Access to SQL Server is prepared as file. It is executed under our own WinForm C# Application. 2. On one machine (SQL Server 2005 Developer Edition) everything works fine. 3. When we try to execute this package on another machine (SQL Server 2005 Standard Edition) we get message "The DataFlow task has no components. Add components or remove the task" and the task does not do anything. However, it runs fine under "dtexecui". We have already search the problem in the internet and could not find anything that could solve the problem (we don't use Source Safe). Thank you very much in advance, Vladimir

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

Task scheduler and System.IO.File.WriteAllText() not working properly

I have a simple console application that has this in mainline:  Console.WriteLine(System.String.Format("the current directory is: {0}", System.IO.Directory.GetCurrentDirectory())); System.IO. File.WriteAllText(@"test.txt", "new contents");   Console.ReadLine(); I compile this in VS 2010 and copy the EXE to a directory named "c:\test".  When I run this code in two different OS's via "Task Scheduler" and "Scheduled Task" I get different results.   In Windows Server 2003 R2 "Scheduled Task" this code will create a file as I would expect, c:\test\test.txt with the proper contents written to it.  When I run this same code on Windows 2008 R2 (64 bit version) "Task Scheduler" it writes the expected contents to an unexpected location, c:\C:\Windows\SysWOW64\test.txt. In both environments if I run the exe's from a command line, it writes to c:\test\test.txt as I would expect. As I understand it, if you don't fully qualify the file name in the first parameter of the WriteAllText method it will write the contents to the file in the directory of the executable however this seems to break down when running in the Windows 2008 Task Scheduler.  I know I can fully qualify the file name or put the expected path in a

web service task input

Hi, I just searched forum and cannot find what I need. From web site for web service calling from SSIS step by step, I setup http connection, and download WSDL file with no problem. I then go to input part to select service and method. After method chosen, I can see 3 parameter variables as all string type. They told me that 1 variable is input string, the other variables are output strings. The main task is to use web service to call the method to pass XML file. I want to know should I create 3 project level variables to hold as corresponding 3 parameter variables? Does it mean that I should copy all XML file content to input parameter variable? How can I see the ouptut value of parameter variables? For output part, I created new file as output.xml file. After package run, it shows me "<Result>The result is null. Either the result is null or the Web method returns void.</Result> ". What does this mean? Thanks for any info or help!

URL location of the WSDL File for Web Services File Task

I am finding that in order to have the Web Services Task work successfully the location of the WSDL file has to be on a local drive that SSIS is executing upon.  Is the current intended behavior? In my SSIS task I use a URL path to store information extracted from the Web Service.  The information is stored on a different server than the one that SSIS is running upon.  This works properly without error. I have confirmed that SSIS has appropriate permissions to read/write to that directory on that server.  When I attempt to reference the WSDL file (located in the same URL directory that I am saving the information) I get a web services error, 'The Web Services Name is empty, Verify that a valid web service name is available." When I update the Web Service Task attribute to point to the WSDL file located on a local drive it works correctly.  I have confirmed that both WSDL documents are exactly the same. The behavior seems a little strange...so I must be missing something subtle. ...cordell...

WSDL file for Web Services task

I'm trying to use a Web Service for the first time in SSIS.  I have the httpConnection specified, and the test succeeded.  I have put in a text file the code below, and renamed the file with a .WSDL extension.  I'm getting the error below.  What is wrong with my WSDL file?  Thanks!   Could not read the Web Services Description Language (WSDL) file.  The input WSDL file is not valid.  The follwing error occurred while reading the file.  There is an error in XML document (3,3)...   Code Snippet<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><helloworld xmlns="http://lesl.com/" /></soap:Body></soap:Envelope>

Populate a Data Flow Task's variables with values from a .dtsConfig file?

I have an SSIS Project to import exchange rates from an XML file into SQL. The project works when I have certain values hard-coded into variables, such as the URL of the XML File, proxy username and passwords etc. I decided to put in a .dtsConfig file, and have it update the values of the variables in the data flow task. So, one of my SSIS Packages in this solution is called ECBDailyRates.dtsx. It contains one control flow, which is a data flow task. The data flow task is a script task which does some XPATH on an XML file, and then provides outputs to an OLE DB Destination, which in turn puts the data into a table in SQL. That much worked before I put in the dtsConfig file. There are a few variables declared inside the data flow task, such as; PricesXMLUriDaily, String ProxyAddress, String ProxyAddressPort, Int32 etc. etc. These are now being updated from the dtsConfig, which is below. It was my understanding that SSIS would run through the config, and update any of the variables as required, and then run my data tasks. However when SSIS runs my script task, none of the variables have been populated, and so it falls over... as it were :) <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="...." GeneratedFromPackageName="dim_Institution" GeneratedFromPackageID="

SQL Server 2005 (X64) Maintenance Clean Up Task Using Hours Option for File Age

I'm running into an issue with SQL Server 2005 64-bit and the maintenance clean up tasks.  I have it set up to delete any transaction logs or backups older than 12 hours.  It seems that there could be some sort of bug because, according to the log files, SQL is running the clean up for 12 days instead.  I've created a work around for this by using execute transact-sql tasks instead, but was wondering if there's a problem with my version of SQL Server, like an SP or patch that I've overlooked. Here's my version information: Microsoft SQL Server 2005 - 9.00.1399.06 (X64)   Oct 14 2005 00:35:21   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Do not create a flat file if no rows are read

Hi, I have a number of flows in a package that are reading to check for changes in data and then outputing those changes to flat files.  If there are no changes, that is, I read no rows in my oledb source, an empty flat file is created.  Consequently, the file is sent and the destination is empty for the next run time. This is not really the worst thing in the world, but I would rather not FTP off empty flat files, if only for the sake of efficiency.  Is there a way to prevent the creation of the flat file if no rows are going to be written? Mark

Excel File as input paramter for Lookup Data Flow Transformation problems


Before I start, I'm using SQL 2008.

I have a Excel file with email addresses that need to acts at input parameters to a Lookup transformation. I have set the Excel Source to my file and specified the email field to be the output. I have dropped the Lookup Transformation Data Flow and connected the both. I'm going to execute a very simple stored procedure, and under the Connections section my SQL query looks like follows: EXEC Test_GetUserName ? 
When I run that I get an error saying that no parameter was provided. But when I run EXEC Test_GetUserName 'someemail@companyname.com' everything executes great, for the obvious part that the email is hard coded. How do I pass the excel input as the parameter?

Thanks for all the help.

There is 10 types of people in the world, those that understand binary, and those that don't.

Calling remote batch file using Execute Process Task



The requiement is to create excel file. I cannot run this code using SSIS vbscript task because this requires Microsoft.Office.Interop DLL which can not be installed on the dev / prod server. Hence using the SQL task the data exported to excel.

But now the excel is required to be updated to merge some cells. The approach we are thinking is to keep VBS file with required code to UNC path. Keep the BAT file running VBS file using CSCRIPT command in same location. Call the BAT file from SSIS package using Execute Process Task. This approach is tested in local system and also works on dev server. But somehow the sample code used to create excel is not creating excel to UNC path.

VBS code below

Const xlSaveChanges = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Cells(1, 1).Value ="Test value"

The BAT used to call above VBS file is given below

cscript \\server.com\folder$\Demo\Excelfile.vbs

The above UNC path is used in execute process task package which runs fine. But the file is not yet created.


Huge temdb and msdb data file after Cleanup Task


I've run the History Cleanup Task has a maintenance job.

After the job ran successfully, I noticed that the data files of msdb and temdb are over 2 GB of size. I've tried to shrink both databases and I didn't get much. It is normal to have the tempdb over 3 GB after run History Cleanup Task even when this is was not the size of the database tempdb before run the History Cleanup Task ??

Best regards

Input type file-: Not getting full path in mozilla


Hi All

I am using Input type = File to get a file from Disk.

<input type=file id="ipFile">

But when i get the value of this i get a file name in Mozilla where as a complete path with file name in I.E.


In I.E. -: D:\bkp\css.txt

In Mozilla -: css.txt

I want to get the Full path in Mozilla too. 

Please help and advice

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend