Microsoft JET Databse Engine Error Message

Hi,
When I try to import DBF files into the SQL Server,
I got the following error messages for some files.

Error Source: Microsoft JET Databse Engine
Error Destination: External table is not in the expected
format
Context: Error calling OpenRowset on the provider.

Does anyone know about this error?

I found that only files that have *.FPT (memo) files didn't work
and other files were imported successfully/

I installed sql server service pack and I checked that
the machine has the latest JET Database engine (4.0).

I'm using Microsoft SQL Server 2000 Standard Edition
w/ Service Pack 2 & Microsoft FoxPro 2.6.

Thank you in advance.

- Hyung -Did anyone ever answer this?sql

Microsoft .NET Framework: Unhandled Exception

Hi,
I built a very simple report that uses a shared data source to access
an SQL server. I wanted to select all columns from a table on a linked
server. My query is simply "select * from SERVER.DATABASE.USER.table"
and it works fine in Query Analyzer. However, when I enter this query
into the Report Designer (running SP1) and try to run it, I get the
error below.
Thanks in advance for any help you wonderful MSFT guys can give,
especially if there's a workaround!
--Adam Bliss
--TEXT OF ERROR MESSAGE--
Microsoft .NET Framework
An unhandled exception has occured in a component in your appliaction.
Click continue and application [sic] will ignore this error and attempt
to
continue.
Object reference not set to an instance of an object.
Details:
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance
of an object.
at Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&
pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, Object[] pvaIn,
IntPtr pvaOut)
at
Microsoft.ReportDesigner.Design.VDTToolBar.SQLToolBar_ButtonClick(Object
sender, ToolBarButtonClickEventArgs e)
at
System.Windows.Forms.ToolBar.OnButtonClick(ToolBarButtonClickEventArgs
e)
at System.Windows.Forms.ToolBar.WmReflectCommand(Message& m)
at System.Windows.Forms.ToolBar.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/microsoft.net/framework/v1.1.4322/mscorlib.dll
---
Microsoft.ReportingServices.Designer
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.dll
---
Microsoft.VisualStudio.Shell.Interop
Assembly Version: 7.0.3500.0
Win32 Version: 8.0.30310 (lab21.030309-2105)
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.Shell.Interop.DLL
---
Microsoft.VisualStudio.OLE.Interop
Assembly Version: 7.0.3500.0
Win32 Version: 8.0.30310 (lab21.030309-2105)
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.OLE.Interop.DLL
---
Microsoft.VisualStudio
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.DLL
---
Microsoft.DataWarehouse.Interfaces
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.Interfaces.DLL
---
Microsoft.DataWarehouse.VsIntegration
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.VsIntegration.DLL
---
System
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
System.Xml
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/system.xml.dll
---
System.Web
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.web/1.0.5000.0__b03f5f7f11d50a3a/system.web.dll
---
System.Drawing
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/system.drawing.dll
---
Microsoft.DataWarehouse
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.DLL
---
EnvDTE
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase:
file:///c:/windows/assembly/gac/envdte/7.0.3300.0__b03f5f7f11d50a3a/envdte.dll
---
System.Windows.Forms
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934e089/system.windows.forms.dll
---
Microsoft.VSDesigner
Assembly Version: 7.0.5000.0
Win32 Version: 7.10.3077
CodeBase:
file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VSDesigner.DLL
---
Microsoft.VisualStudio.Designer.Interfaces
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.Designer.Interfaces.DLL
---
RegexAssembly1_0
Assembly Version: 0.0.0.0
Win32 Version: n/a
CodeBase:
---
Microsoft.VisualStudio.TextManager.Interop
Assembly Version: 7.0.3500.0
Win32 Version: 8.0.30310 (lab21.030309-2105)
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.TextManager.Interop.DLL
---
Microsoft.ReportingServices.ReportPreview
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ReportPreview.DLL
---
Microsoft.ReportingServices.Processing
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Processing.DLL
---
Microsoft.ReportingServices.Interfaces
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Interfaces.DLL
---
Microsoft.ReportingServices.Diagnostics
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Diagnostics.DLL
---
qcafn_1e
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
bz_nfjwh
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
yl9hu7fj
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ilerz4wa
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
5jocxjac
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
jrfgwn5w
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
fxnv6itt
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
zg4sz4ni
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ny4juciw
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
xlnic1xx
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
atmntgbj
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
imdmrqdn
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
-t4ubrdd
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ogl20pjz
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
uzelzqkt
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
7gxa3lt5
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
zafsetv2
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ttnoe-eu
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
zzdw1h6p
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
r9dhpvbw
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
qexy1od1
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
x5cdllnv
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
System.Design
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.design/1.0.5000.0__b03f5f7f11d50a3a/system.design.dll
---
Accessibility
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/accessibility/1.0.5000.0__b03f5f7f11d50a3a/accessibility.dll
---
Microsoft.SqlServer.WizardFramework
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.WizardFramework.dll
---
Microsoft.ReportingServices.Designer.Wizards
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.Wizards.DLL
---
Microsoft.SqlServer.CustomControls
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.CustomControls.DLL
---
Microsoft.NetEnterpriseServers.ExceptionMessageBox
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.NetEnterpriseServers.ExceptionMessageBox.DLL
---
Microsoft.ReportingServices.DataExtensions
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.DataExtensions.DLL
---
System.Data
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.data/1.0.5000.0__b77a5c561934e089/system.data.dll
---
System.Data.OracleClient
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.data.oracleclient/1.0.5000.0__b77a5c561934e089/system.data.oracleclient.dll
---
System.EnterpriseServices
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.enterpriseservices/1.0.5000.0__b03f5f7f11d50a3a/system.enterpriseservices.dll
---
Interop.Vdt70
Assembly Version: 8.0.242.0
Win32 Version: 8.0.242.0
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Interop.Vdt70.DLL
---
ReportingServicesLibrary
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesLibrary.DLL
---
Microsoft.ReportingServices.XmlRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.XmlRendering.DLL
---
Microsoft.ReportingServices.CsvRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.CsvRendering.DLL
---
Microsoft.ReportingServices.ImageRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ImageRendering.DLL
---
Microsoft.ReportingServices.HtmlRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.HtmlRendering.DLL
---
Microsoft.ReportingServices.ExcelRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ExcelRendering.DLL
---
ReportingServicesNativeClient
Assembly Version: 0.0.0.0
Win32 Version: 2000.080.0878.000
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesNativeClient.DLL
---That looks like bug.
As workaround I suggest to use Generic Query Designer
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Bliss" <abliss@.gmail.com> wrote in message
news:cdjf9k$vk4@.odak26.prod.google.com...
> Hi,
> I built a very simple report that uses a shared data source to access
> an SQL server. I wanted to select all columns from a table on a linked
> server. My query is simply "select * from SERVER.DATABASE.USER.table"
> and it works fine in Query Analyzer. However, when I enter this query
> into the Report Designer (running SP1) and try to run it, I get the
> error below.
> Thanks in advance for any help you wonderful MSFT guys can give,
> especially if there's a workaround!
> --Adam Bliss
> --TEXT OF ERROR MESSAGE--
> Microsoft .NET Framework
> An unhandled exception has occured in a component in your appliaction.
> Click continue and application [sic] will ignore this error and attempt
> to
> continue.
> Object reference not set to an instance of an object.
> Details:
> See the end of this message for details on invoking
> just-in-time (JIT) debugging instead of this dialog box.
> ************** Exception Text **************
> System.NullReferenceException: Object reference not set to an instance
> of an object.
> at Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&
> pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, Object[] pvaIn,
> IntPtr pvaOut)
> at
> Microsoft.ReportDesigner.Design.VDTToolBar.SQLToolBar_ButtonClick(Object
> sender, ToolBarButtonClickEventArgs e)
> at
> System.Windows.Forms.ToolBar.OnButtonClick(ToolBarButtonClickEventArgs
> e)
> at System.Windows.Forms.ToolBar.WmReflectCommand(Message& m)
> at System.Windows.Forms.ToolBar.WndProc(Message& m)
> at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
> at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
> at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
> msg, IntPtr wparam, IntPtr lparam)
>
> ************** Loaded Assemblies **************
> mscorlib
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/microsoft.net/framework/v1.1.4322/mscorlib.dll
> ---
> Microsoft.ReportingServices.Designer
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.dll
> ---
> Microsoft.VisualStudio.Shell.Interop
> Assembly Version: 7.0.3500.0
> Win32 Version: 8.0.30310 (lab21.030309-2105)
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.Shell.Interop.DLL
> ---
> Microsoft.VisualStudio.OLE.Interop
> Assembly Version: 7.0.3500.0
> Win32 Version: 8.0.30310 (lab21.030309-2105)
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.OLE.Interop.DLL
> ---
> Microsoft.VisualStudio
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.DLL
> ---
> Microsoft.DataWarehouse.Interfaces
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.Interfaces.DLL
> ---
> Microsoft.DataWarehouse.VsIntegration
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.VsIntegration.DLL
> ---
> System
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> System.Xml
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/system.xml.dll
> ---
> System.Web
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.web/1.0.5000.0__b03f5f7f11d50a3a/system.web.dll
> ---
> System.Drawing
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/system.drawing.dll
> ---
> Microsoft.DataWarehouse
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.DLL
> ---
> EnvDTE
> Assembly Version: 7.0.3300.0
> Win32 Version: 7.00.9466
> CodeBase:
> file:///c:/windows/assembly/gac/envdte/7.0.3300.0__b03f5f7f11d50a3a/envdte.dll
> ---
> System.Windows.Forms
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934e089/system.windows.forms.dll
> ---
> Microsoft.VSDesigner
> Assembly Version: 7.0.5000.0
> Win32 Version: 7.10.3077
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VSDesigner.DLL
> ---
> Microsoft.VisualStudio.Designer.Interfaces
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.Designer.Interfaces.DLL
> ---
> RegexAssembly1_0
> Assembly Version: 0.0.0.0
> Win32 Version: n/a
> CodeBase:
> ---
> Microsoft.VisualStudio.TextManager.Interop
> Assembly Version: 7.0.3500.0
> Win32 Version: 8.0.30310 (lab21.030309-2105)
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.TextManager.Interop.DLL
> ---
> Microsoft.ReportingServices.ReportPreview
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ReportPreview.DLL
> ---
> Microsoft.ReportingServices.Processing
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Processing.DLL
> ---
> Microsoft.ReportingServices.Interfaces
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Interfaces.DLL
> ---
> Microsoft.ReportingServices.Diagnostics
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Diagnostics.DLL
> ---
> qcafn_1e
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> bz_nfjwh
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> yl9hu7fj
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ilerz4wa
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> 5jocxjac
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> jrfgwn5w
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> fxnv6itt
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> zg4sz4ni
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ny4juciw
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> xlnic1xx
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> atmntgbj
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> imdmrqdn
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> -t4ubrdd
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ogl20pjz
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> uzelzqkt
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> 7gxa3lt5
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> zafsetv2
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ttnoe-eu
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> zzdw1h6p
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> r9dhpvbw
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> qexy1od1
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> x5cdllnv
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> System.Design
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.design/1.0.5000.0__b03f5f7f11d50a3a/system.design.dll
> ---
> Accessibility
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/accessibility/1.0.5000.0__b03f5f7f11d50a3a/accessibility.dll
> ---
> Microsoft.SqlServer.WizardFramework
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.WizardFramework.dll
> ---
> Microsoft.ReportingServices.Designer.Wizards
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.Wizards.DLL
> ---
> Microsoft.SqlServer.CustomControls
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.CustomControls.DLL
> ---
> Microsoft.NetEnterpriseServers.ExceptionMessageBox
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.NetEnterpriseServers.ExceptionMessageBox.DLL
> ---
> Microsoft.ReportingServices.DataExtensions
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.DataExtensions.DLL
> ---
> System.Data
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.data/1.0.5000.0__b77a5c561934e089/system.data.dll
> ---
> System.Data.OracleClient
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.data.oracleclient/1.0.5000.0__b77a5c561934e089/system.data.oracleclient.dll
> ---
> System.EnterpriseServices
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.enterpriseservices/1.0.5000.0__b03f5f7f11d50a3a/system.enterpriseservices.dll
> ---
> Interop.Vdt70
> Assembly Version: 8.0.242.0
> Win32 Version: 8.0.242.0
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Interop.Vdt70.DLL
> ---
> ReportingServicesLibrary
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesLibrary.DLL
> ---
> Microsoft.ReportingServices.XmlRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.XmlRendering.DLL
> ---
> Microsoft.ReportingServices.CsvRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.CsvRendering.DLL
> ---
> Microsoft.ReportingServices.ImageRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ImageRendering.DLL
> ---
> Microsoft.ReportingServices.HtmlRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.HtmlRendering.DLL
> ---
> Microsoft.ReportingServices.ExcelRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ExcelRendering.DLL
> ---
> ReportingServicesNativeClient
> Assembly Version: 0.0.0.0
> Win32 Version: 2000.080.0878.000
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesNativeClient.DLL
> ---
>

Mgmt Studio: Script Table as...

Hi,

I'm using SQL Server 2005 Express SP1 & Mgmt Studio express.

I'm trying to use the script output to track my db changes in source code mgmt, but when I output a table to a script the first two bytes of the "text" file are 0xFF and 0xFE which sends my SCM into storingthe file in binary defeating the purpose of storing text files I can "diff" to see the changes.

Does anyone know why the mgmt studio is doing this and is there a way to stop it?

thanks!

Management Studio creates UNICODE files so characters from all alphabets can be represented in the same file. Your SCM system is probably interpretting the UNICODE signature at the top of the file as being "binary."

If you are scripting to a Query Editor Window, you can save the script as an ASCII file by clicking File | Save As... and clicking the little pull-down arrow on the right side of the save button to select the file type. US-ASCII is toward the bottom of the list.

Hope this helps,
Steve

|||

Thanks! This was helpful and it explains the situation.

I'm working around it by the copy & paste method you mention while I deal with the SCM issue.

It's interesting that SQL Server Mgmt Studio uses unicode BOM and Visual Studio uses utf-8 for it's configuration files, so the SCM has to deal with the many "standards" of unicode...

life was not mean't to be easy...

metadata refresh?

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

mruniqueid wrote:

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

I can answer this one - the versioning story for tasks allows ISVs (or Microsoft) to release completely new version of the task (new assembly version for managed, or new CLSID for native tasks). Designer doesn't automatically upgrade package to use new task version, as it will probably break package compatibility with systems that have old task version installed. Reload with Upgrade command tells the designer to try to upgrade tasks to new version.

|||

mruniqueid wrote:

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

This means the metadata of your source column ("objectName1") had changed in the source table or maybe you added this column after configuring your OLE DB source component.

You should try to double-click on your OLE DB Source and the pop-up window, with a question if you want to automatically synchronize the metadata, will show up.

HTH.

|||

Thanks - works as advertised.

In the meantime I found something interesting that wasn't immediately obvious. I have a foreach container with an item enumeration. The enumeration collection is a list of views, all with the same "basic" definition (I'll explain "basic" in a minute). The container has a data flow task that opens a view (with the view name defined by a variable from the foreach container) as an OLE DB source and copies the view contents to one OLE DB destination.

I kept getting errors about metadata differences and tracked the error to the view definitions. All views have the same column data type definijtions but some of the view columns are literals with differing text lengths, such as 'person' (6 chars), 'location' (8 chars), and so on. To solve the problem the view definition had to be modified to cast these literals to the destination column length, such as

CAST('person' AS varchar(50)) AS objectName

All of the views have to have exactly the same data types and lengths in order for the package to work.

Just wanted to pass this along in case anybody else runs into the problem

|||

Correct. A data-flow is bound to the metadata at design-time. The expected metadata cannot change at runtime.

-Jamie

|||My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?

I understand your pain and I have similar complains....

The Data conversion transformation adds a 'Copy of..' to each column because it preserves the original column; if you decided to remove the Copy of part of the name; then SSIS is forced to use a multipart name; as the name of the column is duplicate within the dataflow; so you can see things like (OLE DB Source.MyColumn and Data Convertion.MyColumn). So, I don’t recommend you to edit the names as it gets harder when you are mapping them in the destination components.

Please, feel free to post a suggestion in Microsoft Connect site:

http://connect.microsoft.com/SQLServer/Feedback

|||Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.

Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?

IanO|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

You can't. Its a new column so it has to call it something. "Copy of XXX" is as arbitrary as anything else.

IanO wrote:

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Naturally it does. So your new column will have to be changed to the data type that you require. Should the development tools simply guess what length to put on there? Of course not, so it puts an arbitrary value of 50 in there. SSIS will never make changes for you - you are always responsible for making changes yourself (this is a good thing by the way)

IanO wrote:

Editing every line is tedious. I have to remove "Copy of" and change the size.

Hopefully you understand why this is unavoidable. YOU have to tell SSIS what the data type of each column should be - it will not try and guess.

IanO wrote:

So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

Sorry, I'm a bit confused on this one. What button are you referring to on the OLE DB Connection Manager?

IanO wrote:

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

No, there is no place. Putting an average in like you suggest is a no-no because the would constitute SSIS guessing and as I explained that is something it won't do hence an arbitrary value is better. Besides, if it COULD do the average of the first 100 rows (which it cant because at design-time no actual rows exist in the pipeline - so where are these 100 rows that you are referring to?) that would create an error for any of the rows that exceeded that average - so that isn't a good idea.

What *might* be a good idea would be to have the option of selecting what the arbitrary value should be. That could be an option you set within BIDS. I do however see problems with this if different developers have this option set differently. For safety's sake I think the behaviour is just right as it is currently.

IanO wrote:

Is there no other way to clear the external metadata error?

I don't think so. The developer is responsible for setting what the external metadata should be.

Hope that helps.

-Jamie

|||

IanO wrote:

Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I think what Rafael means by this is that its not a good idea to change the name of the new column to be the same as the one that it is derived from as this causes confusion later in the pipeline.

IanO wrote:

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

I agree with the assertion that you should change at source if necassary although I'm not sure that avoidance is a good strategy. This component has its uses in certain circumstances. I don't have the book to hand so can't read the context in which Kirk stated this.

IanO wrote:

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO

The reason changes don't get propogated will be because you have an asynchronous component such as a SORT or an AGGREGATE. I won't go into the details of asynchronous components (there's plenty of info out there though: http://search.live.com/results.aspx?q=ssis+asynchronous+synchronous&mkt=en-GB&form=QBNO) but you'll have to take my word for it that this is unavoidable because of the vary nature of the components that you are using. Any column that is input into an asynchronous component is NOT the same as any column that is output from it - even if they have the same name.

Its annoying, yes, but that's the way it is I'm afraid. If you want a fuller explanantion then let me know and I'll do my best to provide one.

-Jamie

metadata refresh?

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

mruniqueid wrote:

A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?

I can answer this one - the versioning story for tasks allows ISVs (or Microsoft) to release completely new version of the task (new assembly version for managed, or new CLSID for native tasks). Designer doesn't automatically upgrade package to use new task version, as it will probably break package compatibility with systems that have old task version installed. Reload with Upgrade command tells the designer to try to upgrade tasks to new version.

|||

mruniqueid wrote:

What do you do to address this:

[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.

This means the metadata of your source column ("objectName1") had changed in the source table or maybe you added this column after configuring your OLE DB source component.

You should try to double-click on your OLE DB Source and the pop-up window, with a question if you want to automatically synchronize the metadata, will show up.

HTH.

|||

Thanks - works as advertised.

In the meantime I found something interesting that wasn't immediately obvious. I have a foreach container with an item enumeration. The enumeration collection is a list of views, all with the same "basic" definition (I'll explain "basic" in a minute). The container has a data flow task that opens a view (with the view name defined by a variable from the foreach container) as an OLE DB source and copies the view contents to one OLE DB destination.

I kept getting errors about metadata differences and tracked the error to the view definitions. All views have the same column data type definijtions but some of the view columns are literals with differing text lengths, such as 'person' (6 chars), 'location' (8 chars), and so on. To solve the problem the view definition had to be modified to cast these literals to the destination column length, such as

CAST('person' AS varchar(50)) AS objectName

All of the views have to have exactly the same data types and lengths in order for the package to work.

Just wanted to pass this along in case anybody else runs into the problem

|||

Correct. A data-flow is bound to the metadata at design-time. The expected metadata cannot change at runtime.

-Jamie

|||My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?
|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.

There needs to be a comprehesive, contains all clicks menu for this tool.

There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

Is there no other way to clear the external metadata error?

I understand your pain and I have similar complains....

The Data conversion transformation adds a 'Copy of..' to each column because it preserves the original column; if you decided to remove the Copy of part of the name; then SSIS is forced to use a multipart name; as the name of the column is duplicate within the dataflow; so you can see things like (OLE DB Source.MyColumn and Data Convertion.MyColumn). So, I don’t recommend you to edit the names as it gets harder when you are mapping them in the destination components.

Please, feel free to post a suggestion in Microsoft Connect site:

http://connect.microsoft.com/SQLServer/Feedback

|||Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.

Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?

IanO
|||

IanO wrote:

My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?

You can't. Its a new column so it has to call it something. "Copy of XXX" is as arbitrary as anything else.

IanO wrote:

Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.

Naturally it does. So your new column will have to be changed to the data type that you require. Should the development tools simply guess what length to put on there? Of course not, so it puts an arbitrary value of 50 in there. SSIS will never make changes for you - you are always responsible for making changes yourself (this is a good thing by the way)

IanO wrote:

Editing every line is tedious. I have to remove "Copy of" and change the size.

Hopefully you understand why this is unavoidable. YOU have to tell SSIS what the data type of each column should be - it will not try and guess.

IanO wrote:

So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.

Sorry, I'm a bit confused on this one. What button are you referring to on the OLE DB Connection Manager?

IanO wrote:

If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.

No, there is no place. Putting an average in like you suggest is a no-no because the would constitute SSIS guessing and as I explained that is something it won't do hence an arbitrary value is better. Besides, if it COULD do the average of the first 100 rows (which it cant because at design-time no actual rows exist in the pipeline - so where are these 100 rows that you are referring to?) that would create an error for any of the rows that exceeded that average - so that isn't a good idea.

What *might* be a good idea would be to have the option of selecting what the arbitrary value should be. That could be an option you set within BIDS. I do however see problems with this if different developers have this option set differently. For safety's sake I think the behaviour is just right as it is currently.

IanO wrote:

Is there no other way to clear the external metadata error?

I don't think so. The developer is responsible for setting what the external metadata should be.

Hope that helps.

-Jamie

|||

IanO wrote:

Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,

I think what Rafael means by this is that its not a good idea to change the name of the new column to be the same as the one that it is derived from as this causes confusion later in the pipeline.

IanO wrote:

I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."

I agree with the assertion that you should change at source if necassary although I'm not sure that avoidance is a good strategy. This component has its uses in certain circumstances. I don't have the book to hand so can't read the context in which Kirk stated this.

IanO wrote:

If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO

The reason changes don't get propogated will be because you have an asynchronous component such as a SORT or an AGGREGATE. I won't go into the details of asynchronous components (there's plenty of info out there though: http://search.live.com/results.aspx?q=ssis+asynchronous+synchronous&mkt=en-GB&form=QBNO) but you'll have to take my word for it that this is unavoidable because of the vary nature of the components that you are using. Any column that is input into an asynchronous component is NOT the same as any column that is output from it - even if they have the same name.

Its annoying, yes, but that's the way it is I'm afraid. If you want a fuller explanantion then let me know and I'll do my best to provide one.

-Jamie

Metadata Refresh

-We are using SSIS packages for various kind of data load from excel source.

-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.

-During design time if you accept the metadata changes, all things work fine.

But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.

Do you have any suggestions for the above problem?

Thanks, Vijay.

You'll have to edit the package. Metadata cannot change once it has been designed into the package.

Metadata on data flow path not updating

I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.Metadata cannot and does not change dynamically nor at runtime.

This is by design and there's very little you can do to get around that, short of coding your own application using the SSIS API.

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

Message: SQL Server does not exist or access denied.

Receiving the following error:
Message: SQL Server does not exist or access denied.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.ConnectionPool.GetConnection
(Boolean& isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledCon
nection(SqlConnectionString options, Boolean&
isInTransaction)
any clues please?
Check the Datasource property of the connection string to make sure the
server name /instance name is correct - you can check by creating a new text
document, renaming it to test.udl, opeing and setting the connection
properties, then closing it and open in notepad.
Make sure you can ping the server.
Make sure the DBA's or network dudes haven't closed port 1433
"Belinda" <anonymous@.discussions.microsoft.com> wrote in message
news:37a001c4a06d$7b5405c0$a501280a@.phx.gbl...
> Receiving the following error:
> Message: SQL Server does not exist or access denied.
> Source: .Net SqlClient Data Provider
> at System.Data.SqlClient.ConnectionPool.GetConnection
> (Boolean& isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.Get PooledCon
> nection(SqlConnectionString options, Boolean&
> isInTransaction)
> any clues please?