Credentials Error

I have been trying to use stored credentials with a data source, but I always
get a "login failed" with "Not associated with a trusted SQL Server
connection" as the reason. I am running Report Server on Windows 2000. The
login I am trying to use exists in SQL Server. I have tried different logins
and different servers, always with the same results. Any suggestions?SQL may be set to only allow windows authentication. In enterprise manager,
go to the properties of your server --> security tab and check the
authentication section.
Mike G.
"Falcon" <Falcon@.discussions.microsoft.com> wrote in message
news:10F85238-51F3-466F-9A07-1831B8C0688F@.microsoft.com...
>I have been trying to use stored credentials with a data source, but I
>always
> get a "login failed" with "Not associated with a trusted SQL Server
> connection" as the reason. I am running Report Server on Windows 2000. The
> login I am trying to use exists in SQL Server. I have tried different
> logins
> and different servers, always with the same results. Any suggestions?

creating SSIS packages in c#

How can I create a SSIS package using C#? Can anybody have source code for the same.

thanx in advance,

Vinod

You can use the object model of SSIS to do the same. Please refer to the books online for the SSIS Object Model.

Thanks,

S Suresh

Creating SSIS package failed

Hi,

I'm trying to create a SSIS package. I have used OLEDBSource adapter to get the source table's data and transferring the data to an OLEDBDestination adapter. I tried but I'm facing the problem in mapping the metadata contents.

My code:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

static void Main(string[] args)

{

// Create a new package

Package package = new Package();

package.Name = "OLE DB Transfer";

// Add a Data Flow task

TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost;

taskHost.Name = "Transfer Table";

IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;

// Get the pipeline's component metadata collection

IDTSComponentMetaDataCollection90 componentMetadataCollection = pipeline.ComponentMetaDataCollection;

// Add a new component metadata object to the data flow

IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New();

// Associate the component metadata object with the OLE DB Source Adapter

oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource";

// Instantiate the OLE DB Source adapter

IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate();

// Ask the component to set up its component metadata object

oledbSourceComponent.ProvideComponentProperties();

// Add an OLE DB connection manager

ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB");

connectionManagerSource.Name = "OLEDBSource";

// Set the connection string

connectionManagerSource.ConnectionString = "provider=sqlncli;server=HSCHBSCGN25008;integrated security=sspi;database=Muthu_SSIS_Testing";

// Set the connection manager as the OLE DB Source adapter's runtime connection

IDTSRuntimeConnection90 runtimeConnectionSource = oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"];

runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID;

// Tell the OLE DB Source adapter to use the SQL Command access mode.

oledbSourceComponent.SetComponentProperty("AccessMode", 2);

// Set up the SQL command

oledbSourceComponent.SetComponentProperty("SqlCommand", "select * from EmployeeTable");

// Set up the connection manager object

runtimeConnectionSource.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerSource);

// Establish the database connection

oledbSourceComponent.AcquireConnections(null);

// Set up the column metadata

oledbSourceComponent.ReinitializeMetaData();

// Release the database connection

oledbSourceComponent.ReleaseConnections();

// Release the connection manager

runtimeConnectionSource.ReleaseConnectionManager();

// Add a new component metadata object to the data flow

IDTSComponentMetaData90 oledbDestinationMetadata = componentMetadataCollection.New();

//Associate the component metadata object with the OLE DB Destination Adapter

oledbDestinationMetadata.ComponentClassID = "DTSAdapter.OLEDBDestination";

// Instantiate the OLE DB Destination adapter

IDTSDesigntimeComponent90 oledbDestinationComponent = oledbDestinationMetadata.Instantiate();

// Ask the component to set up its component metadata object

oledbDestinationComponent.ProvideComponentProperties();

// Add an OLE DB connection manager

ConnectionManager connectionManagerDestination = package.Connections.Add("OLEDB");

connectionManagerDestination.Name = "OLEDBDestination";

// Set the connection string

connectionManagerDestination.ConnectionString = "provider=sqlncli;server=HSCHBSCGN25008;integrated security=sspi;database=Muthu_SSIS_Testing";

// Set the connection manager as the OLE DB Destination adapter's runtime connection

IDTSRuntimeConnection90 runtimeConnectionDestination = oledbDestinationMetadata.RuntimeConnectionCollection["OleDbConnection"];

runtimeConnectionDestination.ConnectionManagerID = connectionManagerDestination.ID;

// Tell the OLE DB Destination adapter to use the SQL Command access mode.

oledbDestinationComponent.SetComponentProperty("AccessMode", 2);

// Set up the SQL command

oledbDestinationComponent.SetComponentProperty("SqlCommand", "select from EmplTable");

// Set up the connection manager object

runtimeConnectionDestination.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerDestination);

// Get the standard output of the OLE DB Source adapter

IDTSOutput90 oledbSourceOutput = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];

// Get the input of the OLE DB Destination adapter

IDTSInput90 oledbDestinationInput = oledbDestinationMetadata.InputCollection["OLE DB Destination Input"];

// Create a new path object

IDTSPath90 path = pipeline.PathCollection.New();

// Connect the source and destination adapters

path.AttachPathAndPropagateNotifications(oledbSourceOutput, oledbDestinationInput);

IDTSInput90 input = oledbDestinationInput;

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

// Call the SetUsageType method of the destination

// to add each available virtual input column as an input column.

oledbDestinationComponent.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

foreach (IDTSInputColumn90 col in oledbDestinationInput.InputColumnCollection)

{

IDTSExternalMetadataColumn90 exCol = oledbDestinationInput.ExternalMetadataColumnCollection[col.Name];

oledbDestinationComponent.MapInputColumn(oledbDestinationInput.ID, col.ID, exCol.ID);

}

Console.WriteLine("done");

Console.ReadKey();

// Save the package

//Application application = new Application();

//application.SaveToXml(@."c:\OLEDBTransfer.dtsx", package, null);

}

While debugging, I'm getting exception (ELEMENTNOTFOUND) at this line

IDTSExternalMetadataColumn90 exCol = oledbDestinationInput.ExternalMetadataColumnCollection[col.Name];

because metadata have no appropriate column to map with the destination component.

Any one help me to resolve this issue.

Regards,

kris

Hi,

I have resolved my issues.

For destination component, I initialized the connection and reinitialized metadata and done the mapping then released the connection for destination component.

Now my code is working.

Thanks for your helps..

Regards,

kris

Creating one row view help

Hello, I need assistance
I would like to know if it is possible to do the following
Corp Source Category Description_1 Count_1 Cycle
7801 Internal 52 Day Precall Left Message (Answering Machine) 92 A
7801 Internal 52 Day Precall Left Message (Answering Machine) 60 B
7801 Internal 52 Day Precall Not Reported
34 A
7801 Internal 52 Day Precall Not Reported 116 C
7801 Internal 52 Day Precall Left Message (Answering Machine) 26 C
convert it to:
Corp Sourc Category Description_1
Count_1 Cycle
7801 Internal 52 Day Precall Left Message (Answering Machine) 178 A,B,C
7801 Internal 52 Day Precall Not Reported
150 A,C
Trying to say if Corp, Source, Category, Description are the same but have
different cycles I would like to sum the count and just add whatever cycles
in one rowI hope you are having an issue with the string concatenation.
You may want to check out this link
http://omnibuzz-sql.blogspot.com/20...l.blogspot.com/
"Justin" wrote:

> Hello, I need assistance
> I would like to know if it is possible to do the following
> Corp Source Category Description_1 Count_1 Cycle
> 7801 Internal 52 Day Precall Left Message (Answering Machine) 92 A
> 7801 Internal 52 Day Precall Left Message (Answering Machine) 60 B
> 7801 Internal 52 Day Precall Not Reported
> 34 A
> 7801 Internal 52 Day Precall Not Reported 116 C
> 7801 Internal 52 Day Precall Left Message (Answering Machine) 26 C
> convert it to:
> Corp Sourc Category Description_1
> Count_1 Cycle
> 7801 Internal 52 Day Precall Left Message (Answering Machine) 178 A,B,
C
> 7801 Internal 52 Day Precall Not Reported
> 150 A,C
>
> Trying to say if Corp, Source, Category, Description are the same but have
> different cycles I would like to sum the count and just add whatever cycle
s
> in one row
>
>|||How about :
SELECT Corp, Source, Category, Description_1,
SUM(Count_1) as Count_1,
MIN(Cycle) as Cycle
FROM Whatever
GROUP BY Corp, Source, Category, Description
Roy Harvey
Beacon Falls, CT
On Tue, 13 Jun 2006 06:34:02 -0700, Justin
<Justin@.discussions.microsoft.com> wrote:

>Hello, I need assistance
>I would like to know if it is possible to do the following
>Corp Source Category Description_1 Count_1 Cycle
>7801 Internal 52 Day Precall Left Message (Answering Machine) 92 A
>7801 Internal 52 Day Precall Left Message (Answering Machine) 60 B
>7801 Internal 52 Day Precall Not Reported
>34 A
>7801 Internal 52 Day Precall Not Reported 116 C
>7801 Internal 52 Day Precall Left Message (Answering Machine) 26 C
>convert it to:
>Corp Sourc Category Description_1
> Count_1 Cycle
>7801 Internal 52 Day Precall Left Message (Answering Machine) 178 A,B,C
>7801 Internal 52 Day Precall Not Reported
>150 A,C
>
>Trying to say if Corp, Source, Category, Description are the same but have
>different cycles I would like to sum the count and just add whatever cycles
>in one row

Creating Notepad file through SQL Server 2005

Hi friends,

Is there any way to create a text file or word file through sql server 2005.

If you have any source please suggest me..

P.Kumaran

The question is vague at this point. Can you explain more details?

|||

Hi Dinakar,

Actually I want to write the output result (which i get from a select statement) to a notepad file.

Is it possible to write the select query result in a notepad from sqlserver 2005.

P.Kumaran

|||

You can use BCP utility to create a text file with the results of a query. Check out Books Online (or even google) on how to use the BCP tool.

Creating Error Output for Custom Components

Hi,

I have a 2 custom components - source and destination.

I want to create an error output for each, to allow the users of my component to handle errors the way they choose.

I only found a property in IDTSOuptut90 named isErrorOut - a boolean property indicating whether this output is an error output or not.

Does anyone have additional documentation / articles / code samples regarding how to really populate the rows in the error output?

Thanks

You add the output in ProvideComponentProperties, as others, and that property is pretty much the key-

// Error Output
IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = ErrorOutput;
outError.Description = "Error output for rows that caused an unexpected error.";
outError.SynchronousInputID = inp.ID;
outError.ExclusionGroup = 1;
outError.IsErrorOut = true;

In ProcessInput you would then direct rows to thje normal output or the error output.

int iError = ComponentMetaData.OutputCollection[ErrorOutput].ID;
buffer.DirectErrorRow(iError, 0, buffer.CurrentRow);

I don’t believe there are no samples in the recent batch on Microsoft.com/downloads/ and from memory I’m sure the Source sample that ships with the product includes error output usage.

Creating Dimension Template...

Hello,

I need to create a dimension that has pre-defined multiple hierarchies, attributes, and the data source table the dimension will be based on would be different for each customer. (The structure of the data source table would be the same though, just the underlying data would be different)

Thus, I thought about using the dimension template. However, the steps in the dimension template wizard is quite confusing to me. Does any know where to find a good document explanning how to create a dimension template? I read through the MSDN library about creating the template, but it's not too helpful either.

Please let me know if I am heading to the wrong direction, or if there is another good way to do this..... Any help is appreciated.

Thanks,

Hsiao-I

A dimension template is just a regular dimension XML file saved in the following location:

C:\Program Files\Microsoft SQL Server\90\Tools\Templates\olap\1033\Dimension Templates

So all you need to do is create a dimension either using the dimension wizard or manually in the dimenison editor, then go to the file system look for the .DIM XML file correpsonding to this dimension in the project folder where you created your project.

Then just copy this .DIM file to the path above and this dimenison will appear as a template in the dimension wizard.

Now the dimenison template only contains metadata information (so things like attributes, hierarchies...), it doesn't contain dimension members since these come from the underlying data source.

Let me know if this helps.