I can design a report fine in the Report Designer. I can build it fine. I can preview it fine. But, when I try to deploy it I get the error: No mapping between account names and security IDs was done
Any help will be appreciated.
Glenn,
Did you get past this error? I'm having the same issue now with my SQL Server Reporting Services / Report
I can design a report fine in the Report Designer. I can build it fine. I can preview it fine. But, when I try to deploy it I get the error: No mapping between account names and security IDs was done
Any help will be appreciated.
Glenn,
Did you get past this error? I'm having the same issue now with my SQL Server Reporting Services / Report
We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):
SELECT fieldlist INTO #temp1 FROM table
SELECT fieldlist INTO #temp2 FROM table
SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2
DROP TABLE #temp1; DROP TABLE #temp2
Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?
Thanks, Gordy
You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.
Ken
We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):
SELECT fieldlist INTO #temp1 FROM table
SELECT fieldlist INTO #temp2 FROM table
SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2
DROP TABLE #temp1; DROP TABLE #temp2
Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?
Thanks, Gordy
You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.
Ken
Hi ,
Im trying to build a package that will copy data from excel to SQL
in a program
unfortunately , when I open the package xml file and I drill into the
oledb destination I see that I have no available input columns
what could be the problem ?
thanks ahead
Eran
p.s
the script:
Dim p As Package = New Package()
Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As TaskHost = CType(e, TaskHost)
thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Create excel connection MANAGER
Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")
excelCon.Name = "ExcelSourceConn"
excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""
' Create sqldev connection Manager
Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")
sqlCon.Name = "sqldevConn"
sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"
''create source component
Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
excelSource.Name = "ExcelSource"
excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"
Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()
excelInstance.ProvideComponentProperties()
excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))
excelInstance.SetComponentProperty("AccessMode", 0)
excelInstance.SetComponentProperty("OpenRowset", "business_codes$")
excelCon.AcquireConnection(Nothing)
'excelInstance.ReinitializeMetaData()
excelInstance.ReleaseConnections()
Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
sqldev.Name = "sqldev"
sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()
sqldevInstance.ProvideComponentProperties()
sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))
sqldevInstance.SetComponentProperty("AccessMode", 0)
sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")
sqldevInstance.AcquireConnections(Nothing)
sqldevInstance.ReinitializeMetaData()
sqldevInstance.ReleaseConnections()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))
MsgBox(excelSource.OutputCollection.Count)
'For Each input As IDTSInput90 In sqldev.InputCollection
' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' ' Call the SetUsageType method of the design time instance of the component.
' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
' Next
'Next
Dim app As Application = New Application()
app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)
Hi,
I found your post and was wondering if you ever got a response to this? I'm having the same problem
Thanks
Cat
|||When you open the package in the designer do you see any columns published on the source adapter?
Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?
The commented piece of code is supposed to add columns to the input column collection.
Thanks.
Hi ,
Im trying to build a package that will copy data from excel to SQL
in a program
unfortunately , when I open the package xml file and I drill into the
oledb destination I see that I have no available input columns
what could be the problem ?
thanks ahead
Eran
p.s
the script:
Dim p As Package = New Package()
Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As TaskHost = CType(e, TaskHost)
thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Create excel connection MANAGER
Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")
excelCon.Name = "ExcelSourceConn"
excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""
' Create sqldev connection Manager
Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")
sqlCon.Name = "sqldevConn"
sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"
''create source component
Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
excelSource.Name = "ExcelSource"
excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"
Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()
excelInstance.ProvideComponentProperties()
excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))
excelInstance.SetComponentProperty("AccessMode", 0)
excelInstance.SetComponentProperty("OpenRowset", "business_codes$")
excelCon.AcquireConnection(Nothing)
'excelInstance.ReinitializeMetaData()
excelInstance.ReleaseConnections()
Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
sqldev.Name = "sqldev"
sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()
sqldevInstance.ProvideComponentProperties()
sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))
sqldevInstance.SetComponentProperty("AccessMode", 0)
sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")
sqldevInstance.AcquireConnections(Nothing)
sqldevInstance.ReinitializeMetaData()
sqldevInstance.ReleaseConnections()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))
MsgBox(excelSource.OutputCollection.Count)
'For Each input As IDTSInput90 In sqldev.InputCollection
' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' ' Call the SetUsageType method of the design time instance of the component.
' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
' Next
'Next
Dim app As Application = New Application()
app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)
Hi,
I found your post and was wondering if you ever got a response to this? I'm having the same problem
Thanks
Cat
|||When you open the package in the designer do you see any columns published on the source adapter?
Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?
The commented piece of code is supposed to add columns to the input column collection.
Thanks.
NEWSEQUENTIALID had very predictable behavior in the Apr CTP build. It incremented in obvious, clear, consistent amounts. Doing inserts right after another or having widely range time intervals between inserts made no difference.
With the June CTP build, things are not as predictable and do not appear to be sequential. The following output shows the guids that were created. The test plan inserted 10 rows, waited some varying interval of time, then inserted another 10 rows, etc. until all rows were inserted.
Waiting a period of time between inserts of 10 rows appeared to introduce gaps into the sequence. Additionally, the last group of 10 inserts appears to have guid values that fill in some of the gap left by the previous inserts.
30608b57-5dee-d911-83a8-0003ff3ba433 insert 10 rows
31608b57-5dee-d911-83a8-0003ff3ba433
...
39608b57-5dee-d911-83a8-0003ff3ba433 so far so good, wait before more inserts
80ab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
89ab5672-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
8aab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
93ab5672-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
94ab5672-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
9dab5672-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
c0430490-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
c9430490-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
a0143ddb-5dee-d911-83a8-0003ff3ba433 insert 10 rows; appears to be filling in a gap
...
a8143ddb-5dee-d911-83a8-0003ff3ba433
Eric
e h a n s@.microsoft.com|||
Code was sent to Eric Hanson by email. It is also shown below:
USE AdventureWorks
GO
CREATE TABLE dbo.SEQTEST(
[ID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
[Name] [nchar](50) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_SEQTEST] PRIMARY KEY CLUSTERED
)
GO
Make a console application using the following code. Run the console application several times.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace NEWSEQUENTIALID
{
class Program
{
static void Main(string[] args)
{
Inserter lInsert = new Inserter();
for (int i = 0; i < 10; i++)
lInsert.InsertRow(String.Format("batch " + DateTime.Now.ToLongTimeString() + " insert number {0}", i));
}
}
public class Inserter
{
private static string _insertCommand =
"INSERT INTO SEQTEST (Name) OUTPUT INSERTED.ID Values(@.Name)";
public bool InsertRow(string Name)
{
SqlConnection lConnection = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=AdventureWorks");
lConnection.Open();
SqlCommand lInsertCmd = new SqlCommand(_insertCommand, lConnection);
lInsertCmd.Parameters.AddWithValue("Name", Name);
IDataReader lResult = lInsertCmd.ExecuteReader();
while (lResult.Read())
{
Console.WriteLine("ID: {0} Name: {1}", lResult.GetGuid(0).ToString(), Name);
}
lConnection.Close();
return true;
}
}
}
NEWSEQUENTIALID in the April CTP consistently produced results that were very obviously increasing by looking at the character representations of the guids. The character pattern was completely predictable.
NEWSEQUENTIALID in the June CTP generates results that actually do follow an ascending sort order in SQL as evidenced by ORDER BY. The difference in behavior is that the character representation of the guids does not obviously show the ascending sort when casually inspecting the characters.|||
NEWSEQUENTIALID is not meant to generate gap-less ranges. It's only guarantee is that it will generate strictly increasing values.
As for the part of your repro where you observed that the values did not appear to be increasing. We defined "increasing" in different terms than the character value you see. This is because SQL Server implements it's own comparison semantics on GUIDs.
If you read the first 4 bytes of your GUID value from right to left, you'll notice that they are in strictly increasing order.
If I copy paste your generated GUIDS, and reverse the first DWORD, we see:
578b6030-5dee-d911-83a8-0003ff3ba433 insert 10 rows
578b6031-5dee-d911-83a8-0003ff3ba433
...
578b6039-5dee-d911-83a8-0003ff3ba433 so far so good, wait before more inserts
7256ab80-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
7256ab89-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
7256ab8a-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
7256ab93-5dee-d911-83a8-0003ff3ba433 minimal wait before next 10 inserts
7256ab94-5dee-d911-83a8-0003ff3ba433 insert 10 rows; no gap
...
7256ab9d-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
900443c0-5dee-d911-83a8-0003ff3ba433 insert 10 rows; notice a gap is left
...
900443c9-5dee-d911-83a8-0003ff3ba433 wait before next 10 inserts
db3d14a0-5dee-d911-83a8-0003ff3ba433 insert 10 rows; appears to be filling in a gap
...
db3d14a8-5dee-d911-83a8-0003ff3ba433
Which better describes how we define increasing.
I will check with documentation folks to see if it's possible to be clearer on this.
Let me know if you need more details,
Thanks
-Mat
mathh-at-microsoft-dot-com
