I have one package (Primary) that calls 4 Packages(1,2,3,4) throw an SQL Server Connection.
That 4 packages create 4 distinct files from a a Streamwriter.
The files area created in a Script component
I call the execution of the packages from an Web Page that calls the job an execute the package Primary.
The first two packages (1,2) create the file and everithing goes ok.
The third one(3) "blows up" with the exception:
System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
The next try that i made was to comment in the package Primary the execution of the packages 1,2 and run the package 3,4.
The package 3 run ok and the package 4 blows up with
"System.ArgumentOutOfRangeException: Index and length must refer to a location within the string. Parameter name: length at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)"
The next try that i made was to comment in the package Primary the execution of the packages 1,2,3 and run the package 4.
It run Ok.
When the package 3 and 4 blows up they create the file but they don't write.
In all the executions that i made i deleted the files that were created.
When I run the packages from the development solution and using file system connection everything runs.
I have a script that is utilizing an FTP task to retrieve files from a Windows FTP site without any issues. I need to check the dates of the files on the FTP site prior to downloading them as I only need files that have been modified. Since these files are hundreds of MB in size, downloading them first and them checking the date is not an optimal approach. Can somebody please help me figure out how to check the creation dates of the files on the remote FTP directory? Thanks!
I'm trying to read the folder which has flat files and transform it and put it another folder with the SAME original name. I created a For Eachloop containter and assign it to variable infile. In the data flow I added some transform and flat file destination. currently its reading 3 files and output is only one file. I would like to create dynamic flat file connection so that it'll create separate files (same name) in different folder. Thanks,
I'm new to SSIS. I have a small problem. In our system we are trying to create flat files based on data from returned from different SQL stored procedures. The configuration settings for creating the flat files are stored in a SQL table.
For example, the config table will look like below :
Feed Id Feed Name Feed File Name Feed Stored Procedure Name ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1 Biz C:Biz.txt dvx_GetBiz 2 PG C:PG.csv dvx_GetPG 3 NexT C:NexT.txt dvx_GetNexT 4 Amez C:Amez.csv dvx_GetAmez
The problem is dvx_GetBiz, dvx_GetPG,... all have different number and types of columns. So, I'm not able to create a dynamic Flat File destination depending on the columns returned from the corresponding stored procedure.
Iīve having some issues with a package that Iīm currently building. The package is pretty much finished and doing what itīs supposed to, just doing some testing to ensure everything is in order.
The package is pretty basic: Read a fixed with text file, do a lookup on destination database table, conditional split to determine what to insert and what to update, and thatīs it!
At the source of my data flow I have the error output set to redirect all rows, counting those rows, and finally writing them to a dynamically created file.
However, I just now realized that this file seems to be created ALWAYS, regardless of rows actually being redirected or not. Everytime I run the package with no rows being passed through the error output, I still end up with a dynamically named, zero sized text file.
So my question: Is it possible to supress the creation of this file, or do I have to add a file system component to my control flow, deleting this file if my number of errorrows = 0 ?
Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex() method reads the script file from starting to end and returns script text. My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime, @prmEndDate datetime,@prmEvent char(1)ASBEGIN SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) as EventDate from dbo.Parishioner P left outer join dbo.Sex S on P.SexId = S.SexId where (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) between @prmStartDate and @prmEndDate END But when i executing script file having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm
i am using sql2005, the data inserted into the database by using createuserwizrd. after that anyone give me this error anyone ? any thought? thanks please An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error: The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
I want to create a XML file with data in my table. I have a question about tags.
SELECT -- Root element attributes 'http://tempuri.org/Form.xsd' AS 'xmlns', 'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsd', ( SELECT -- Creating a default element
[Code] ....
This is my query. When I use 'xmlns' namespace the result is below:
i have this error when trying to run the package in development mode. and i can not decipher this non-intuitive message:
Error: 0xC001604D at <package name>: Checkpoint file "" failed during creation due to error 0x80070003 "The system cannot find the path specified.".
please note that i have no checkpoints on package anymore; i had two but i deleted them but this message still appears. i have searched the web but no luck in finding an answer.
maybe someone would be able to point out my obvious mistake here.
i am creating/uploading a new file on the webserver, and if it is successfully i want to insert a record in the database (with the filename).is there a way to create a transaction for this so that if either operation fails they both fail?
here is my error message wich can also be seen live at www.aspdoug.com/menu log in with user: doug pass: macromedia! -------------------------------------------------------------------------------------------------------------------------------- Server Error in '/menu' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error: The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +421 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +133 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Below is my web.config file. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<?xml version="1.0"?><configuration>
<system.web><!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. Visual Basic options: Set strict="true" to disallow all data type conversions where data loss can occur. Set explicit="true" to force declaration of all variables.--> <compilation debug="false" strict="false" explicit="true"></compilation> <pages><namespaces> <clear /><add namespace="System" /> <add namespace="System.Collections" /><add namespace="System.Collections.Generic" /> <add namespace="System.Collections.Specialized" /><add namespace="System.Configuration" /> <add namespace="System.Text" /><add namespace="System.Text.RegularExpressions" /> <add namespace="System.Web" /><add namespace="System.Web.Caching" /> <add namespace="System.Web.SessionState" /><add namespace="System.Web.Security" /> <add namespace="System.Web.Profile" /><add namespace="System.Web.UI" /> <add namespace="System.Web.UI.WebControls" /><add namespace="System.Web.UI.WebControls.WebParts" /> <add namespace="System.Web.UI.HtmlControls" /></namespaces> </pages> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Forms" /> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors>--> </system.web> </configuration>
------------------------------------------------------------------------------------------------------------------------- MY host told me that they do not support sqlexpress and to change it MySql 2005 or something. im using hostmysite.com builder plan.
I have one task in which i have to collect lots of .txt file having ## delimiter my requirement is to convert the delimiter from ## to comma and save the new file with .dat extension in different folder.
I have done all required process and run the application which should flow like collect source .txt file do Script component processing and create new .dat file with processed data in Data Flow task, but in my Task the Source and Destination start on same time and process start after words which cause empty file or some time a.txt file data stored in b.dat file where as a.dat file is completely empty.
The process should flow in sequence but behavior is totally against the process, i am using Foreach Loop Container for pick up each file.
I am importing Differennt Excels Files into table. my require ment is after importing completed I need to insert all these Filenames ,File creation date into table. (for Auditing).
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
I know this is not SQL forums but still if some body has solutions..then plz help My backend is SQL. My Ms Access database is always open and getting updated continously.
I need to create Job for SQL. This job will run every 10-15 min may be. This job should pick latest data from MS Access and put in my SQL server database Is it possible to create such kind of Job>
OR
Can my application read this MSAccess database when its open by some other application and getting updated continously?----I tried this in vain!!!!
I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..
Hello to all! I have a procedure "rebuild_index" and I would like to create a job running that procedure. May someone send me a script (template) how to create this job, the most important: When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script) Thank you very much for your help!
create proc p_rebuild_index as declare @name varchar(100), @string varchar(200) declare c1 cursor for select name from sysobjects where type = 'U' open c1 fetch c1 into @name while @@fetch_status = 0 begin set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)' execute (@string) --print @string fetch c1 into @name end close c1 deallocate c1
How can i create sdf files? Is there any way to convert a mdb or a xml file to sdf? The only way i was able to do this was with data port wizard from primeworks, but its not freeware... Can i populate my sdf database from xml files by writing code...?? Does someone knows?? I have searched everywhere and didnt find anything... I'm working in VS2005 vb.net and i have installed compact sql... Thank you!
Is it possible to create a database structure in MS-Access and somehow import it into Visual Web Developer 2005 Express or SQL Server Management Studio 2005 Express?
Hi, I am not sure is this a proper place for my question. All we know that creating a database can be done as the following. 1) Create a project named DBExample 2) Click Add->New Item. 3) Select SQL Databse and type a name.(Ex: Database1.mdf) A database is added to the project 4) Then add table and input values. 5) Set the primary key.... My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?
Hello... I want to develop a web site having two features 1. Online Shopping2. Forums Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once. ____________Thanks in advNauman Ahmed
I'm sorry to beat this to death but something odd is happening that I'm not quite sure I understand.
I have a number of DTS packages that when originally created with the wizard were scheduled and the jobs have run fine. Today, I created an import job that runs great but then crashes when I try to schedule it from the wizard. When I manually go in and try to schedule the DTS job, it accepts the input but does not create the job.
Since I've done this before, I'm confused about why I was able to do this previously, but not now. Permissions, etc. have not changed at all and I am the dbo for the database. The DTS package and attempted job scheduling are being done on Windows 2000 Server. SQL-Server version is 7.0.
I have monthly tables named as 'Tablename_yyyy_mm' etc. I want to make a view that will capture the current months table and the last 3 months data. for eg: if today is november 19th, 2003. The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables if today is jan 01,2003 The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created create table CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL, NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
procedure creation I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created create table CUSTOMER ( CUSTOMER_ID NUMBER(6) NOT NULL, NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
any clues? i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.
create table CUSTOMER ( CUSTOMER_ID int identity(1000,1), NAME VARCHAR2(45), ADDRESS VARCHAR2(40), CITY VARCHAR2(30), STATE VARCHAR2(2), ZIP_CODE VARCHAR2(9), AREA_CODE NUMBER(3), PHONE_NUMBER NUMBER(7), SALESPERSON_ID NUMBER(4), CREDIT_LIMIT NUMBER(9,2), COMMENTS VARCHAR2(256) )
But while writing insert statement didnt specify this column.
Here is the problem : I can not create a DTS package in SQLServer. => Error description : access denied Environment : - Windows NT4 sp6 Server, french, logon on as "Administrator". - SQLServer 7.0 sp4, french, logon as "sa" on the NT Server with Enterprise Manager. - SQLServer Agent runs under an Administrator account
Why can't i create ā single DTS package ? Strange, isn't it ? Many thanks for your ideas
We normally map a single SQL user to all application users . From performance standpoint and from best practices perspective , what is the preferred method of mapping OS users to SQL users viz one to one OR many to one .
