Reg: Creating An SSIS Package To Transfer The Data From Sql Datasources To Sql Datawarehouse

Apr 8, 2008



Hi All,
I have created fact tables and dimension tables in datawarehouse database, and i created a olap cube from those tables.
I want to run SSIS Package which populates these fact and dimension tables from datasources.


Thanks in advance,
Archana

View 5 Replies


ADVERTISEMENT

SSIS Package To Import Data From SQL Datasource To Datawarehouse OLAP Cube

Apr 8, 2008

Hi All,

I have created database and OLAP cube in Analysis services using SSAS.In SSAS I have used a datasource which is using SQL tables to populate OLAP cube.Now when I added some more data to my SQL tables and trying to deploy cube,the newly added is not getting populated in the cube.So i want run SSIS package which will import data from SQL tables to this OLAP cube.

Can you please help me how to write this SSIS package to import data from SQL tables to OLAP cube.(Very urgent issue)

Thanks in Advance,
Swapna

View 4 Replies View Related

Reg:SSIS Package To Transfer Data Between SQL Table And Olap Cube

Apr 2, 2008



Hi All,
can anybody help me in creating the SSIS package to transfer the data from SQL table in database engine to OLAP cube in Analysis services


Thanks in Advance.
Archana

View 1 Replies View Related

Creating SSIS Package To Extract Data From MySQL And Append To SQL 2005

Apr 26, 2007

Hi guys,

I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.

I hope you guys can help me with this.

Hoping to hear from you soon.

Thank you so much.

Kind regards,
Neil

View 7 Replies View Related

Several Different Errors When Creating And Running SSIS Package. Package Runs Successfuly Sometimes.

May 2, 2008

Hi All,



I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.



Sometimes when I am creating a package I get the following error in a message box:



SQL Server Import and Export Wizard

The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.

Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)





Other times when I run a package that has run successfully before I get the following error:



Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.





Other times I get this error message:



.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)





And still other times



The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.





I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.





Does anyone have any other suggestions to try?





Thanks.



View 4 Replies View Related

Transfer A SSIS Package To Another Server.

Mar 31, 2008

Hi All, I am new to SSIS and SQL Server. I have a development copy of SQL Server 2005 on my desktop, and have just completed my first SSIS package, which replaces an Access appplication that I have on our server. Now I want to move my package to one of our live SQL Server 2005 instalations on our Network. Is there an easy way to move the work I have done on my Development version? I guess that I will need to sort out security etc, but I am hoping not to have to re key everything I have done so far.

Regards

ADG

View 3 Replies View Related

How Can I Transfer A Password For OLEDB Connection Within A SSIS Package To Another Developer's VS2005 PC?

Sep 14, 2007

Hi,

After I had transfered SSIS package to another PC I tried to open package and I got an error. ".. Failed to decrypt protected XML node "PackagePassword" with error . ".
It turned out, that the password wasn't saved in a OLEDB connection manager of the Package. However "save password" options checked!
But password field was empty.

As soos as I enter password the package executes successfully.

Question: How can I transfer a password for OLEDB Connection within a SSIS package to another developer's VS2005?

View 4 Replies View Related

Creating A SSIS Package

Mar 5, 2007

HI All,



can any body give steps to import data from one sqldb to another through ssis package, i was comfotable with dts but ssis is a lil bit confusing.....



thnx

regards

View 1 Replies View Related

Creating An SSIS Package

Feb 18, 2008

Can anyone give a newbie a general script for creating an SSIS page to import data nightly from an Access database to sql 2005. They are not on the same server. The Access database us 366,968 kb and grows everyday from input from web content. It has 5 tables and 100's of queries, which is how it is updated.

Please give me a starting point, do not understand the info in books on line.

Thank you
Dee

View 6 Replies View Related

DTSX Package Data Transfer Error

Jul 10, 2007

I will try to explain things the best I can.

When the data is transferred from source to destination (replace not append), the data in one field in one table is incorrect. Both source and destination tables have the same number of rows (8493). The ProductID field data range at the source is from 58958 to 73008. When the table is copied the ProductID field data runs from 1 to 8493.



What would cause this skewing of data?

This happens on brand new dtsx packages and this only happens in one field out of 5 different tables.



I am baffled. Any help is appreciated.



Thanks.

Andrew

View 16 Replies View Related

SSIS And Datasources

Feb 12, 2007

I have several SSIS packages which I have setup using a datasource to connect to the database. I should be able to switch between databases by just changing the server and database on the datasource but for some reason the ssis package keeps on using the old database. I have the connection managers setup to reference the datasource and can see the connection manager pointing to the new database but when I run a task by right clicking on it and choosing execute task it calls the stored procedure in the old database. I know it is doing this because I renamed the stored procedure in the old database and the task errored out saying it could not find the stored procedure. Can someone please tell me what I am doing wrong?

View 4 Replies View Related

Need Help Creating A SSIS Package (Newbie)

Mar 12, 2006

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.



MA

View 4 Replies View Related

Programmatically Creating SSIS Package

Jan 4, 2007

Hi guys,
 
I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.
Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?
I am giving my code here too.
I will appreciate any kind of suggestion on this.
 
Code snippet:
 
public void CreatePackage()
{
string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;
      Package pkg = new Package();
      pkg.PackageType = DTSPackageType.DTSDesigner90;
ConnectionManager oledbConnectionManager =              CreateOLEDBConnection(pkg);
ConnectionManager flatfileConnectionManager =
             CreateFileConnection(pkg);
      // creating the SQL Task for table creation
Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);
ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;
      execSqlTask.Connection = oledbConnectionManager.Name;           
      execSqlTask.SqlStatementSource =
"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) GO";
      // creating the Data flow task
Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");
      TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;
      MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;           
      // Put a precedence constraint between the tasks.           
PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);
      pcTasks.Value = DTSExecResult.Success;
      pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;
      // Now adding the data flow components
IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
      sourceDataFlowComponent.Name = "Source Data from Flat file";
      // Here is the component class id for flat file source data
      sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";
CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();
      managedInstance.ProvideComponentProperties();           
            sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;
            sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);
     
managedInstance.AcquireConnections(null);
      managedInstance.ReinitializeMetaData();
      managedInstance.ReleaseConnections();
      // Get the destination's default input and virtual input.
IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];
                        // Here I dont find any columns at all..why??
      // Now adding the data flow components
IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
      destinationDataFlowComponent.Name =
"Destination Oledb compoenent";
      // Here is the component class id for Oledvb data
      destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();
      managedOleInstance.ProvideComponentProperties();
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager);
      // Set the custom properties.
      managedOleInstance.SetComponentProperty("AccessMode", 2);                 
managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");           
      managedOleInstance.AcquireConnections(null);
      managedOleInstance.ReinitializeMetaData();  // Throws exception
      managedOleInstance.ReleaseConnections();           
      // Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New();            path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],
      destinationDataFlowComponent.InputCollection[0]);
      // Get the destination's default input and virtual input.
IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];
      IDTSVirtualInput90 vInput = input.GetVirtualInput();
      // Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
      {
          managedOleInstance.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
      }
      DTSExecResult res = pkg.Execute();
}
public ConnectionManager CreateOLEDBConnection(Package p)
{
      ConnectionManager ConMgr;
      ConMgr = p.Connections.Add("OLEDB");
      ConMgr.ConnectionString =
"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";
      ConMgr.Name = "SSIS Connection Manager for Oledb";
      ConMgr.Description = "OLE DB connection to the Test database.";
      return ConMgr;
}
public ConnectionManager CreateFileConnection(Package p)
{
      ConnectionManager connMgr;
      connMgr = p.Connections.Add("FLATFILE");
      connMgr.ConnectionString = @"D:MyCSVFile.csv";
      connMgr.Name = "SSIS Connection Manager for Files";
      connMgr.Description = "Flat File connection";
      connMgr.Properties["Format"].SetValue(connMgr, "Delimited");
connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);
      return connMgr;
}
 
And my CSV files is as follows
 
NAME, AGE, GENDER
Jon,52,MALE
Linda, 26, FEMALE
 
Thats all. Thanks.

View 4 Replies View Related

Creating SSIS Package Failed

Aug 6, 2007

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

View 1 Replies View Related

SQL 2012 :: Creating Job To Run SSIS Package Remotely

Jan 7, 2014

I have a ssis package in the DW environment. How can I run this package in the production environment by creating the job and run remotely.

View 7 Replies View Related

Wizard For Creating Interface To Ssis Package?

May 21, 2007

I'm very new to ssis, so I hope I don't sound too ignorant. I was told there might be a way to create User interfaces for ssis projects. ie, I am trying to create an import package that will let a user import an excel file to a sql table. What I would like to have is some sort of web interface that lets the user select the source file and destination table.



The more I search for a way to do this the more it seems impossible without knowing a ton about coding. If anyoen can help I'd appreciate it.

View 4 Replies View Related

Creating SSIS Package Event Handlers

Sep 11, 2007

I would like to create an event handler that would catch any errors that result from a sys.<table> not existing. The package is designed to run on both SQL Server 2000 and SQL Server 2005 and when I query sys.<tables> there is an error when the query is run on SQL Server 2000. I just need a good starting point...I would like something that when the server isn't 2005 it just skips the server and doesn't fail the package and doesn't get counted towards the max error count. Thanks for any help.
-Kyle

View 1 Replies View Related

Creating A Table In Access From An SSIS Package

Sep 29, 2006

I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts???

View 1 Replies View Related

DTS Package In SQL Server 2000 To Transfer Data Across Tables Daily

Dec 11, 2007

Hi,
I have a Users table in Oracle database and same table (Users) in SQL Server 2000 database. I want to create a DTS Package through which I can copy the data from Oracle database to SQL Server 2000 database. This package should run automatically at mid-night daily so that if there are some entries done in Oracle database then it get copied in SQL Server 2000 database. Also is there any way to copy only those entries from Oracle database which are not present in SQL Server 2000 database. Please help me in this regard as I am new to DTS.

Thanks
Rohit

View 1 Replies View Related

Creating And Accessing Temp Tables In SSIS Package

Sep 26, 2007

Hi,

I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.

I follow the following steps to achieve this:

01. Created a new SSIS package
02. Create a connection string to "(local)/." server, "tempdb" database
03. Set the "RetainSameConnection" property value to "TRUE"
04. Set the "DelayValidation" to "TRUE", where ever I found this property
04. In Control Flow I added to items
a. Execute SQL Task
b. Data Flow Task
05. For "Execute SQL task" I set the connection to "tempdb"
06. I written the following query
Create table #transfer_CompaniesToProcess_tbl
(
companyID int not null
)
GO
07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination"
08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command"
09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl"
10. When I clicked on the "OK" button; I ended with following error:


TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".

------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------

I gone through the following article and it seems I missed some thing.
http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Can any one have any idea where I am doing wrong?

Thanks
Sreekanth

View 9 Replies View Related

Dynamically Creating SSIS Package For Each Flat File

Apr 18, 2007

Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.



Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.



What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:



1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.



2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).



Thanks,

Travis

View 1 Replies View Related

Ssis Creating A Basic Package Tutorial - Not A Query

Jun 13, 2006

In the tutorial Creating a Basic Package Using a Wizard > Lesson 1: Creating the Basic Package >

says to use the following sql statement on the query page:

SELECT * FROM [Customers$] WHERE NumberCarsOwned > 0

When I paste the query in I get the message :

This SQL statement is not a query.

Does anyone have any suggestions?  The input and output are set up correctly and I have the sample excel file Customers.xls. 

I am new to all this, is there some setting I need to change for the tutorial to work or..?

FYI I have installed Sql 2005, Sp1.

 

View 4 Replies View Related

Creating SSIS Package SQL Server Agent Job Without Sysadmin Rights

Mar 12, 2008

Hi

I have a problem i receive the following error message when i try to add an new step into a SQL Server Agent job :

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)


This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"

I have made the following configurations:

The user group (windows group) that the user belongs has the following roles in msdb :

db_dtsadmin
db_dtsltuser
db_dtsoperator
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

i have made a proxy to sql server agent which has the following subsystems :

"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.

Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.

Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.

Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.

View 1 Replies View Related

SSIS Data Transfer To Excel Lists

Mar 28, 2008

Hi All,

I need to do data transfer from three tables in SQL server 2005 to the single Excel sheet as a report. Even If I create a Lists or named range, I couldn't able to transfer the data into the Lists. The idea is, whenever the data is transfered into the Lists, the three Lists (or Range) having data should grow separately.

Currently the data is transfered out of the Lists.

Anyone can help me.

Thanks
Sanra

View 5 Replies View Related

SSIS Error During Excel Data Transfer

Jan 2, 2008

I am getting an error when an while tranfering data from excel

the error is

[Excel Source [31]] Error: There was an error with output column "Problem Description" (61) on output "Excel Source Output" (39). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[Excel Source [31]] Error: The "output column "Problem Description" (61)" failed because truncation occurred, and the truncation row disposition on "output column "Problem Description" (61)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "Excel Source" (31) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.


The "Problem Description " is a free text field

.I am also pasting a sample data from this field







Try to login to the Operational Risk System- EDCS system (https://oprisk4-dev.ny.ssmb.com:26539/siteminderagent/forms/login.fcc?TYPE=33554433&REALMOID=06-000f3e21-1533-1105-9e71-8088cb990008&GUID=&SMAUTHREASON=0&METHOD=GET&SMAGENTNAME=$SM$ZlvK8bQN3Gx6kXd9LY%2fFTznf3Vi5QSreVbn0vxHs7IUR6gJ9ncq2qnEXtM4wBS0%2fGP%2bU8qMBqC8%3d&TARGET=$SM$%2foprcs%2fjsp%2fcs%2ejsp"), but get the following after entering my id and password:

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Open the oprisk4-dev.ny.ssmb.com:26539 home page, and then look for links to the information you want.
Click the Refresh button, or try again later.

Click Search to look for information on the Internet.
You can also see a list of related sites.

HTTP 500 - Internal server error
Internet Explorer


Please help how to import this data without any error.
JigJan

View 2 Replies View Related

SQL 2012 :: SSIS - Transfer All Data From Source To Destination

Sep 1, 2014

I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.

I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.

View 2 Replies View Related

SSIS: Roll Backed Data Transfer Task

Aug 7, 2006

Hello,
I'm Designing sql server 2005 SSIS Packages.
According to my requirment i have a sequence container. It has few data flow task, on success of one next one is running. If any one of them get failed then it should roll backed all the transaction. Each Data flow task transfering a data from one server to another server in similar table.

Thanks.

View 3 Replies View Related

How To Transfer Data From Multiple Text File In To A Database Using SSIS

Mar 20, 2008

Hi All,
 i have mutiple text file. let us say,a1.txtb1.txtc1.txt
i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table)
now i have to transfer a1.txt file data ----to---  x1b1.txt file data ----to---  y2c1.txt file data ----to---  z3
using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process.
so, can any one give ur valuable sugession to solve this issue.
 

View 2 Replies View Related

Source And Destination Databases On Same Server While Data Transfer Using SSIS

May 9, 2007

Hi,

I am having one query regarding data transfer using SSIS. If we use

DTS packages for the data transfer between two databases then the source database and

destination database must be on different db servers or instances.Here, I

am talking about the DTS Packages with Distributed Transactions enabled.

I need to know that whether this constraint has been rectified with SSIS Packages or it still persists.



One more thing is that while transferring the data, can we view/insert/update source database or it is locked if the transfer is in process.



Please reply..........



Thanks and Regards,

Rajesh

View 5 Replies View Related

SQL 2012 :: How To Capture Data Flow Component Name Dynamically While Package SSIS Package Is Executing

Jun 3, 2014

I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?

View 5 Replies View Related

Integration Services :: SSIS Data Transfer Says Rows Copied But Count On Table Is Less?

Sep 18, 2015

I've a SSIS 2008  parent/child package solution to manage data transfers between two different data sources, so we can copy multiple tables and capture how many rows were transferred and duration for each transfer.  This solution was working fine up until last week, when I made some changes to allow the package to perform a source count using standard SQL determined by an expression, or SQL provided from configuration tables, I also changed the package to Truncate or not the destination table, again controlled by configuration settings in a table. The child packages which perform the data flows have not changed!

The day after the controlling package promotion to live, I saw the bizarre behaviour of the Package log stating all rows transferred, but the actual table counts were not what the log stated, see attached file. The package solution works ok on other servers and was ok in DEV, but there were less tables and rows transferred.Re-running the package gave the same errors, but on some of the same tables and some different ones. 

As it is the child packages doing the transfers and nothing has changed in them. I cannot see how the log would be able to say all rows are transferred and yet not all of the rows are actually moved?

Process output - where you can see counts and log Table transfer controller (as txt not dtsx)

An example of the data transfer child packages (as txt not dtsx)When I set the ExecuteOutOfProcess = True the package worked fine, unfortunately, this is not a good solution as SSIS 2008 does not tidy up the Dtshost.exe processes it starts and I'd be left with a memory issue after a very short time, we transfer hundreds of tables each day. ( I could write a .net script in the controlling package to kill the child processes, but that would still have hundreds of processes running before I could end them, as we have three parallel streams to allow a bit better performance.

View 6 Replies View Related

Transfer Data To New Table Then Transfer Indexes

May 30, 2008

Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table

if it is what flow item would be used to transfer/alter the indexes and defaults?

I'm very new to ssis so the more detail you can give the better.

Thanks

View 5 Replies View Related

Job Running SSIS Package Keeps Failing But The SSIS Package By Itself Runs Perfectly Fine

Aug 30, 2006

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved