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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
Nov 4, 2006
Hi,
I am new to SSIS. I followed the direction of the tutorial Creating Simple ETL Tutorial package in BooksOnline. I have tried more than five times and have done exactly as suggested in the tutorial but it does not work.
The URL is:
http://msdn2.microsoft.com/en-us/library/ms169917.aspx
I get these warnings and finally fails:
1)[Lookup [30]] Error: Row yielded no match during lookup.
2) [Lookup [30]] Error: The "component "Lookup" (30)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
3) [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (30) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
4) [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
Can someone help me with this tutorial error? or Am I doing something wrong.
Thank you,
View 6 Replies
View Related
Oct 10, 2007
Hi Gurus,
I am new to SSIS. I am trying to install just the SSIS in one machine("SSIS Machine") and just the DB Engine ("SQL Server Machine") in another machine. What I am trying to do is, separating the SSIS service and packages from the Database Engine and trying to run in in another machine. I have few questions on this topic. I searched on this forum but I couldn't find a concrete answer to those questions. Forgive me if it already answered/asked multiple times.
1. When I install SSIS in "SSIS Machine", do I need to install client components also in the same machine?
2. I already established this setup (SSIS with client components in one machine and SQL Server in another) but when I tried to connect to the SSIS thro' Management Studio from Sql Server machine, I keep getting "Access Denied" error. Is it possible to connect to SSIS server from another machine (using Management studio)? I tried the DCOM security permission options I found in the internet(I don't have domain id so I gave "Everyone" full access) but still I get the same error. Any help would be appriciated.
3. Do I need 2 SQL Server Licenses (Enterprise) if I go with this environment?
4. Is it possible to configure SQL Job to run SSIS installed in another machine?
I would appriciate any help on this issue.
Thanks
View 4 Replies
View Related
May 16, 2006
I have now created a few simple SSIS packages. In BIDS I right-clicked on a solution and selected "Build". I went to the "bin" directory hoping to find a standalone ".exe.dll" file but found only a ".dtsx' file.
Can BIDS can actually build a standalone exedll or is the .dtsx file all that's available? (If so, I guess that another program is expected to invoke the .dtsx file.)
TIA,
barker
View 3 Replies
View Related
Feb 8, 2006
HI,
I have problems with executing packages in the SQL Server 2005 and before I go into the details I would like to check, if my workflow is correct.
Can someone explain me the best way to move a package from BIDS to a SQL Server? I would like to store the packages in the SQL Server.
In addition if I login to Integration Services with SQL Server Management Studio I can see in the folder "stored packages" my package but I cannot view the folder "MSDB". Ususally I receive an error message from the named pipe provider, that I could not connect to the server (I am using Windows Authentication and my login is an administrator on the SLQ Server box)
Thanks in advvance
Nobs
View 3 Replies
View Related
Jan 2, 2008
Please forgive my ignorance in advance, as I am just becoming familar SSIS. I am sure this is possible, but I would like a definite answer and perhaps a good tutorial on this. I have data in SQL 2005 that needs to reach a third party Oracle DB. The party doesnt give us direct write access, however they have provided a webservice to insert data. Is this feasible using SSIS... SQL 2005 ----> 3rd party web service -----> Oracle? If so can someone point me to a tutorial that explains this process? Thanks a bunch.
View 1 Replies
View Related
Dec 12, 2005
There are two options to specify the subpackage location (SQL Server or file location). I'd like to know how I can specify a variable name that points to the file location so I avoid hard coding the file location which could change during production installation.
View 4 Replies
View Related
Jan 26, 2007
I am tasked with loading the data from one SQL Server 2005 database into another of the same type and schema on the same server. I'd like to use this task to get my feet wet in terms of using SSIS. Can someone suggest a way of doing this?
Thanks in advance
View 15 Replies
View Related
May 19, 2006
I had just installed SQL 2005 dev on my laptop and got an error message when I tried to create a package using the BI IDE. I received the same error using VS2005 IDE. But the project was created regardless without any packages. When I tried to create a new package in the project, I received the same error again, but with an option to view the error details.
Following is the text of the error details:
TITLE: Microsoft Visual Studio
------------------------------
Error creating package
------------------------------
ADDITIONAL INFORMATION:
Failed to save package file "C:Documents and SettingsmyoungbloodLocal SettingsTemp mpD4B.tmp" with error 0x80040154 "Class not registered".
------------------------------
Failed to save package file "C:Documents and SettingsmyoungbloodLocal SettingsTemp mpD4B.tmp" with error 0x80040154 "Class not registered".
------------------------------
BUTTONS:
OK
------------------------------
I found a similar post that suggested that they try the following:
regsvr32 msxml3.dll
regsvr32 msxml4.dll
regsvr32 msxml6.dll
But msxml6.dll could not be found.
I did get a copy of this file from a coworker, and after copying it and registering it, I was able to add a package to the project.
My concern now is what is the likelyhood of this being the only file missing from the installation.
I'm wondering if I should reinstall, or (if it exists) do a repair on the installation.
Thanks.
Marshall
View 12 Replies
View Related
Jan 10, 2007
Hi,
In our project we have two SSIS package.
And there is a task (Execute SSIS package) in First package that calls the execution of second package.
I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."
As we are running first package by job, job runs successfully logging above error
The protection level of second package is set to "EncryptSensitiveWithUserKey"
Can anybody please suggest how to handle it?
View 4 Replies
View Related
Apr 27, 2006
Hi!
I'm new to SSIS (and quite new to SQL Server). I have a process which I'd like to automize via SSIS - just don't know how and couldn't figure it out yet by playing around with the program. Shouldn't be too difficult though.
First of all, that's the process as I do it now:
1) Load several flatfile sources (dumps of SQL tables) into an SQL database.
2) Add identifier rows (to some tables), set the primary and foreign keys so the database is "recreated" and I can work on it.
3) Do several simple transformations, aggregations and selects across tables and finally write a new table containing information for reporting stuff.
I succeded in loading flatfiles within the data flow view, doing some transformations and saving the output to a flatfile. What I didn't find out: how can I "recreate" the database enabling me to perform "SELECT/FROM/WHERE" statements across tables? Will I have to write the imported files to tables within a db (how?) or can I avoid this step?
A little guide (newbie friendly) would be great help!
View 11 Replies
View Related
Jan 9, 2008
I used the SSIS import/export wizard to create a single data flow of about a dozen source/destination tables. The tables however have some foreign key constraints between them. Executing the wizard-created package causes foreign key violations. Is there a way to control the order of the table load process within a single data flow? Or is the preferred way to set up several data flows that are ordered via the control flow tab?
View 6 Replies
View Related
Apr 11, 2008
Hello ALL,
My source columns are:
Customer Record DESTINATION TABLE
Name Address_line_1
sex city
PID state
legal_street zip_code
legal_state ID
legal_city
mail_street
mail_city
mail_state
mail_zip
My client needs. All Unique Addresses from the Source.
.
That is the duplicates should be removed and uniques should be available.
Please let me know first what he wants then also tell me what to use in the data flow and how to start. I want to understand the logic. Note: Two persons might have the same address or One address might belong to 2 or more people.
This is what my client said.
Please give me what should i have in the output
View 2 Replies
View Related
Aug 25, 2006
Hello, I have a file I need to retrieve nightly from a web server, it's not a SOAP request. It's just a wget call to a remote php script that returns a csv file.
I can't seem to find a way to do this, I see ftp, and web service, but no web get type of function. Is this not available in IS as provided from microsoft, and something I'm going to have to write? Seems like this type of thing would be allmost as common as ftp.
If anyone has any suggestions, any help would be be greatly appreciated. Right now, it looks like I'm going to have to use an external wget.exe to get the file.
Thanks
View 2 Replies
View Related
Mar 29, 2006
Hi all,
I've exposed my data (that exists in a proprietary format) with the ADO.NET provider interfaces (IDbConnection, IDataReader, IDbDataAdapter and IDbCommand). I can't seem to find any examples of how to get Integrated Services to hookup to this .NET code in my class library. Is it possible? My goal is for this provider to be both a destination and a source and for others to be able use IS to manipulate the data however they want.
Some links or examples would be great.
Thanks,
Dave
View 5 Replies
View Related
Aug 23, 2007
HI All. I'm trying to tweak the Transfer Logins task to exclude Windows Logins that are local to the Server (e.g. servernameusername) which obviously can't be transferred off the server. Annoying that we have a couple of local Logins on this system instead of all Domain Groups, but we're stuck with them due to firewall issues, and a policy excluding SQL Logins.
My idea is to create a text file as part of my Package that lists Logins to be Excluded From the Transfer - I think I then need to create a New File Connection to the Text File as a Connection Manager, then somehow get that data into a Variable, and then use an Expression to populate the 'LoginsList' Collection from syslogins where loginame not equal to logins in my textfilevariable?
Or maybe I'm over complicating this, and there's an easier solution? Lots of info in Books Online about Expressions and Variables, but having trouble finding examples that I can use. As a DBA, this is my first foray into SSIS, and as you can possibly tell, I'm floundering....
View 1 Replies
View Related
May 10, 2007
The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:
1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
View 12 Replies
View Related