issues about foreach loop

I defined Foreach File Enumerator,there are more than 2 files needed to deal with,it donesn't work if i don't specify filename in flat file connection manager editor,however if i specify filename then doesn't foreach files

what should i do? thanks

and i tried to use varialbes ,but it seems not supported in Flat File connection manager editor?|||

andrew knight wrote:

I defined Foreach File Enumerator,there are more than 2 files needed to deal with,it donesn't work if i don't specify filename in flat file connection manager editor,however if i specify filename then doesn't foreach files

what should i do? thanks

Did you set an expression on the ConnectionString property of the connection manager?

-Jamie

|||

get it

thanks in advance

Issue with fields without value

Hi all of you,

I'm loading a Sql destination table via flat file. Everything is file but when one concrete field is empty in my file

it generates an error. Why?

The first 10000 lines have data from 75 position till 85 in my file. But the 10001 no but at the same time I want to load

the rest of the fields

In my Sql table, of course that fields keep nulls.

When I ran DTS this casuistry worked fine.

[Destino 193_TMP [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Thanks in advance for your help and thoughts,

Hi ya,

It seems that when you're casting it, it doesn't appear to be of right value for the casting function. If you don't have a casting function do put in the casting function so that null value is ignored or recognised.

Hope that helps

Cheers

Rizwan

|||

I belive the problem is that you're trying to insert a value with the wrong type in your destination. That's what it is with me when i get this error.

Issue with Ascii 7 files

Hi All,

I need to generate Ascii 7 bit flat file, based on data in db, using integration services, FTP task. Currently i am generating file with ansi-latin and then using the script task converting it to the ascii 7. File looks to be generated properly. But when the target system reads this, they complain that the file has junk charecters some thing like this. when i open it after generating the file it looks fine to me in DOS also. I dont know what is the target system and what OS is used by them. what cud be the issue for these junk charecters and is it possible that a Ascii 7 file generated by windows doesnt work in other OS? If the method i am doing to generate the ascii 7 is not currect then what is the best method for this?

????H^@.D^@.R^@.|^@.1^@.E^@.N^@.I^@.N^@.A^@.|^@.O^@.|^@.2^@.0^@.0^@.6^@.-^@.0^@.9^@.-^@.1^@.3^@.

PS: earlier i had generated a flat file using data export from Excel & that worked in the target system well. is there any difference in the file encoding generated by excel and integration services?

Please help me!!!!

-vinu

ANSI Latin is extention of ASCII character set. Meaning any proper ASCII text is also valid ANSI Latin text. And ANSI Latin text is either valid ASCII text as well, or contains characters that can't be represented in ASCII at all. So you either don't need any conversion from ANSI Latin -> ASCII, or the lossles conversion is not possible.

I'm not sure what you are doing in the script task, but if you specified the requirements correctly, it is either not needed or not possible :). Anyway, it would be helpful if you include the code.

Issue Loading Data from Flat File

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

Philip Coupar wrote:

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

I'm working with Philip on this. Basically it looks as though there's problem in the way that Excel saves stuff to csv. Anyone know why?

-Jamie

|||

We are experiencing the same exact problem. The CSV file imports fine using our old DTS Packages just not with the new SSIS packages we have created. I can provide sample csv files if needed.

If you have an update on this, please let me know.

|||Did you see the workaround that Philip posted (above)?

-Jamie

Issue Exporting Date format to a delimited file.

In exporting from a OLEDB connection to a flat file.

In the originating table the field for DOB is in a varchar(10) format ex. 01/17/2007. The flat file connection destination is setup as a DT_STR. When you look at the OLEDB connection table preview you see it as 01/17/2007. When it is export to the delimited <CR><LF> <|> pipe delimited the format looks like this 01/17/2007 00:00:00. The issue would be resolved with a right ragged fixed width file. But this is not the requirement for the project format fot the file. I have tried delete and recreating the connections, and even tried doing a data conversation from the OLEDB connection to a char(10). Also, thourgh the transformation services with out any luck. On the flat file data connection I am using expressions to map to a declared variable path and variable name and I listed the expression language below also:

@.[User::varPATH]+ @.[User::varFileName]+ RIGHT("0" + (DT_WSTR, 2) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_WSTR, 2) DAY( GETDATE() ), 2) +RIGHT("0" + (DT_WSTR, 4) YEAR( GETDATE() ), 4) + ".txt"

If you can give some help in getting the file to export to a delimited "|" file in the format of "01/17/2007" this would be greatly aprreciated. I also forgot to mention that I have also tried putting a text qualifier in like" on the flat file destination column layout and get the other format still.

Thanks in advance.

Scott

Try a derived column transformation with the following:

(DT_STR,10,1252)(DT_DBDATE)[columnname]|||I could not reproduce your issue; if the column in the source table is varchar(10); I don't see how you can get the time part in the flat file. Unless you have something else between the source component and the flat destination that cast the DOB column to a datetime.|||

Rafael Salas wrote:

I could not reproduce your issue; if the column in the source table is varchar(10); I don't see how you can get the time part in the flat file. Unless you have something else between the source component and the flat destination that cast the DOB column to a datetime.

I agree. My guess is that the OLE source table is really a datetime field.|||Try again... It looks like it's auto correcting to a DT_DBDATE by default.|||

Wolfsvein wrote:

Try again... It looks like it's auto correcting to a DT_DBDATE by default.

Nope, not going to happen.

I just created a table:
CREATE TABLE [dbo].[forums_test3](
[column1] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_forums_test3_column1] DEFAULT (''),
[datecolumn] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Then I inserted data:
insert into forums_test3 values (1,'01/17/2007')
insert into forums_test3 values (2,'01/18/2007')
insert into forums_test3 values (3,'01/19/2007')

Then I created a package with one data flow in it. Inside that data flow, I added an OLE DB source connector which points to the aforementioned table. I chose to add both columns to the data flow.

Then I added a flat file destination. I created a new flat file connection for a delimited file, where the vertical bar ("|") was the column separator.

I executed the package and got the following results:
01/17/2007|1
01/18/2007|2
01/19/2007|3

Somewhere, you have incorrect metadata.|||I ran the same test and got same results. What else do you have in the data flow? go to the advanced properties of the source component and check the data type of the column...|||The advance says DT_STR|||Are you using expression in the export of your table too?|||If you read the above state carefully it said the source was a varchar(10) and I even through sql try to create it as a varchar(10) on a convert statement.|||I even deleted the connection recreated like I said in the oringal description of the issue.|||

My test was a atable with only one column varchar(10); 2 rows with sothing like '01/12/2007'. The dataflow was an OLE Db souce and a Flat file destination. The connection manager for the flat file was created as delimited. Header row delimiter: Vertical bar (|); text qualifier: <None>

This is the code of the package:

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">NAM\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">KLPKA91</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">1/17/2007 10:59:52 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">5</DTS:Property><DTS:Property DTS:Name="VersionGUID">{8AC2348D-2416-4AE5-8A16-7BA2B820B7AA}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">Flat File Connection Manager 2</DTS:Property><DTS:Property DTS:Name="DTSID">{259C2EAB-15E8-40EB-8B91-EA273C23249E}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="FileUsageType">0</DTS:Property><DTS:Property DTS:Name="Format">Delimited</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="Unicode">0</DTS:Property><DTS:Property DTS:Name="HeaderRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="HeaderRowDelimiter" xml:space="preserve">_x007C_</DTS:Property><DTS:Property DTS:Name="ColumnNamesInFirstDataRow">0</DTS:Property><DTS:Property DTS:Name="RowDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="DataRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="TextQualifier">&lt;none&gt;</DTS:Property><DTS:Property DTS:Name="CodePage">1252</DTS:Property>

<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">10</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">DOB</DTS:Property><DTS:Property DTS:Name="DTSID">{264F0CBC-933F-4506-BD4D-1CBB50E3507D}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn><DTS:Property DTS:Name="ConnectionString">C:\Temp\test3.txt</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">klpka91.RafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{D3313824-77A8-4270-A6EA-65C620E688C0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=klpka91;Initial Catalog=RafLab;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;1000&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="5" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="21987" y="15346" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="0" top="3598" logicalid="2" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{0FA525CC-1C4A-465D-8460-DB43EE25F44A}</DTS:Property><DTS:Property DTS:Name="DTSID">{EBC145DF-B9E7-4928-B523-27AA69956DA8}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsDataFlowDiagram&gt;&lt;dwd:BoundingTop&gt;-500&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="12" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="-1500" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="21987" y="14420" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="27" top="1084" logicalid="4" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}/components/1" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Flat File Destination" left="0" top="3369" logicalid="5" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}/components/87" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="MSDDS.Polyline" left="1400" top="1849" logicalid="6" controlid="3" masterid="0" hint1="0" hint2="0" width="826" height="2020" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobj&gt;

&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}/paths/242" vartype="8" /&gt;

&lt;property name="Virtual" value="0" vartype="11" /&gt;

&lt;property name="VisibleAP" value="0" vartype="3" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="1" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;

&lt;point x="1826" y="2248" /&gt;

&lt;point x="1826" y="2808" /&gt;

&lt;point x="1799" y="2808" /&gt;

&lt;point x="1799" y="3369" /&gt;

&lt;/connector&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;dwd:PersistedViewPortTop&gt;-1500&lt;/dwd:PersistedViewPortTop&gt;&lt;/dwd:DtsDataFlowDiagram&gt;&lt;dwd:DtsComponentDesignerPropertiesList&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 DataSourceViewID&lt;/dwd:key&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 TableInfoObjectType&lt;/dwd:key&gt;&lt;dwd:value xsi:type="xsd:string"&gt;Table&lt;/dwd:value&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;/dwd:DtsComponentDesignerPropertiesList&gt;&lt;/TaskHost&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}</DTS:Property><DTS:Property DTS:Name="DTSID">{34FC20EC-A4C2-4FED-8961-52AD0EBF2AF4}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DestinationName&gt;Flat File Destination Input&lt;/dwd:DestinationName&gt;&lt;dwd:SourceName&gt;OLE DB Source Output&lt;/dwd:SourceName&gt;&lt;/PipelinePath&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}-242</DTS:Property><DTS:Property DTS:Name="DTSID">{3C016A24-AED7-4B6F-88B9-BF48E8B6B5D6}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<components>

<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">

<properties>

<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[test1]</property>

<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property>

<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>

<connections>

<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{D3313824-77A8-4270-A6EA-65C620E688C0}"/></connections>

<outputs>

<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="240" name="DOB" description="" lineageId="240" precision="0" scale="0" length="10" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="239"/></outputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="239" name="DOB" description="" precision="0" scale="0" length="10" dataType="str" codePage="1252"/></externalMetadataColumns></output>

<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="241" name="DOB" description="" lineageId="241" precision="0" scale="0" length="10" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

<component id="87" name="Flat File Destination" componentClassID="{A1DF9F6D-8EE4-4EF0-BB2E-D526130D7A7B}" description="Flat File Destination" localeId="1033" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Flat File Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

<properties>

<property id="90" name="Overwrite" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether the data will overwrite or append to the destination file." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</property>

<property id="91" name="Header" dataType="System.Null" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify"/></properties>

<connections>

<connection id="89" name="FlatFileConnection" description="" connectionManagerID="{259C2EAB-15E8-40EB-8B91-EA273C23249E}"/></connections>

<inputs>

<input id="88" name="Flat File Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><inputColumns>

<inputColumn id="264" name="" description="" lineageId="240" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="263"/>

</inputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="263" name="DOB" description="" precision="0" scale="0" length="10" dataType="str" codePage="1252"/></externalMetadataColumns></input>

</inputs>

</component>

</components>

<paths>

<path id="242" name="OLE DB Source Output" description="" startId="11" endId="88"/>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">TableDateToAFile</DTS:Property><DTS:Property DTS:Name="DTSID">{0FA525CC-1C4A-465D-8460-DB43EE25F44A}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||If this is a meta data issue how do you clear out bad meta data. Is there a place where you can look at the meta data directly.|||

Wolfsvein wrote:

If this is a meta data issue how do you clear out bad meta data. Is there a place where you can look at the meta data directly.

You'll need to look at the advanced properties of all of your components and look at the input/output columns to ensure they match.|||We have a winner.