isql query output issue: 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"><none></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"><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"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>1000</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></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"><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"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>-500</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</dds></dwd:Layout><dwd:PersistedViewPortTop>-1500</dwd:PersistedViewPortTop></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></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"><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"><dwd:DestinationName>Flat File Destination Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></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: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">

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

</outputs>

</component>

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

</inputs>

</component>

</components>

<paths>

</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.

Monday, February 20, 2012

Issue Exporting Date format to a delimited file.

No comments:

Post a Comment

isql query output issue

Blog Archive

About Me