Hi,
my configuration file provides parameters that are used for an SQL query in my package. However, for these parameters it must be possible to pass NULL to the query.
This seems to be impossible in SSIS..? If the config file provides no value, the package uses a default value - and this default value can not be null.
Any suggestions?
Thanks a lot,
Regards
Jan
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I have a custom assembly that uses a config file to obtain database connection settings. This dll is then used by my sql report. I have added refrerenced to all the required dll's in my report and added the required code groups to all the necessary files. I have also placed my app.config file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServer and C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies
However when I actually run the report in the designer (vs2005) I am receiving Null reference exceptions when my code tries to read my config file because it is reading the machine.config file and not my app.config file. If I hard code my connection string everything works fine.
Can anyone tell me why the machine.config file would be referenced instead of my app.config file and how I can stop it from doing this?
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedI'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
When exporting the results of a query to a file any values that are null are displayed as NULL in the file. Is there anyway to have them be blank instead? I looked around in the Studio but couldn't find anything that would do this globaly.
View 3 Replies View RelatedI have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
All,
I don't know what is the problem, i am not able to retrieve connection string from web.config in my code behind page
I used every solution provided by people in differents forums.
can anybody help me with this.I used
WebConfigurationManager.OpenWebConfiguration("/TimeSheetApplication").ConnectionStrings("TimeSheetConnectionString").ConnectionString;
WebConfigurationManager.ConnectionStrings("TConnectionString").ConnectionString;
Hi,
I have the following problem: I'm connected to flat file source and trying to read data that is later inserted in an MS Access database. Everything wokrs fine instead of one thing - when I have null values in the flat file, I want those NULL values to be inserted in the MS Access db, instead of that what happens is that I actually get the default values for a column type from the flat file and later insert that defalut value. For example if I have a null value in an four-bite-signed-integer column of the flat file, I get 0 as value.
I thouth of solution using a "Derived Column" transformation which can transform the zeros into nulls, but decided to check with you guys if there is a smarter thing to do (for example to edit the flat file source to read the NULLs correctly).
Any advice is appreciated! Many thanks
Ventsy
Hello,
I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.
My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.
I need help...does anyone know what's wrong?
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
Hello All,
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
Thank you in advance for your help
Sumesh
I am trying to write a Stored proc to retrieve only records from my table tat do not have a DateTime value ie DateTime is NULL and for some reason simply adding a DateRemoved = NULL does not work. Can anyone please help me with solution to this issue please??? Thanks in Advance
View 3 Replies View RelatedHi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
Hi there,
I have inherited a databse and am building a new website to go wiht it.
There is a file upload page which will upload images to a directory. I need to insert into the database retrieve the id just added then upload the image renaming it in the format locID(QueryString)_ImageID(retrieved from database).jpg
The page has a file upload control and a button.
I am trying to write my code behind so that when the button is clicked it inserts location id into the images table retrieves Image id. Renames the file and uploads it to the images folder.
II think i need to call the routine from another routine for the button click but the signatures are different, where am i going wrong? or for that matter have i been pissing into the wind for the last 4 hours?
CODE BEHIND
Imports System.Data
Imports System.Data.SqlClientPartial Class admin_Add_Images
Inherits System.Web.UI.PageProtected Sub UploadImage(ByVal Sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim LocationId As String = Request.QueryString(ID)
' create a new SqlConnectionDim NewConn As New SqlConnection
NewConn = New SqlConnection("server=desktopsqlexpress;uid=xxxxxx;pwd=xxxxxxx;database=MYLOCDEV") 'OleDbConnection i
' open the connection
NewConn.Open()Dim MyInsert = New SqlCommand("INSERT into image([LocationID]) VALUES (@LocationID); SET @NewId = Scope_Identity()")
NewConn.Close()
If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
'RENAME THE FILEDim newid As Integer = e.Command.Parameters("@NewId").Value
Dim fn As String = (LocationId & "_" & newid & ".jpg")Dim SaveLocation As String = Server.MapPath("oicImages") & "" & fn
Try
File1.PostedFile.SaveAs(SaveLocation)Response.Write("The file has been uploaded.")
Catch Exc As ExceptionResponse.Write("Error: " & Exc.Message)
End Try
ElseResponse.Write("Please select a file to upload.")
End If
End SubProtected Sub Submit1_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Submit1.Click
UploadImage()End Sub
End Class
Does anyone have an example of a web.config file that works with Dynamic Data controls? I asked because I can't seem to arrange my "config sections" in any working order. I use 1 connection string in the web.config to establish a conversation with the database. Thanks in Advance.
View 1 Replies View RelatedHi the data connectionstring will be generated automatically in web.config file after dragging a data table from database into webpage.for example: <configuration> <add name="SQLProfileConnString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MSPetShop4Profile.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </> But what if I have to write the connectionString part myself, I mean where I can get the syntax of it? Thanks a lot
View 1 Replies View RelatedI have a simple SSIS package which includes one Analysis Services Processing Task. I have created a connection manager object (cmo) and assigned it to the AS task. Within the cmo I selected a specific user name and
password to log on to the server with. Tested this [domain][user account] and it works. Now here is the problem. When I run the package either in debug mode or from a command line, the packages ignores the connection manager and
attemps to use the account I am logged into the server with.
Using a config file to hold the credentials does not work either. When DTEXEC is used, the package attempts to use the credientials used to log in to the server.
Does anyone have any ideas why this is happening?
Hi All!
I've been struggling with a problem I have. When I use DTEXECUI to run a package and manually add the config file, it gets ignored and there is no warning as such.
Here's the set up:
1) create SSIS package that uses a DB connection
2) enable config, add XML config, export the connection string, disable config
3) edit config file, change connection string
4) open package from Windows Explorer (double click)
5) add config file created in step 3
All of that works fine, but there are times when it doesn't. I think it depends on the editor used in step 3. If I use textpad and "pretty format" the XML (from the huge one-line built from within Visual Studio), then sometimes the changes aren't picked up. I can tell this because the "default" server in development is not accessable in production. When we "reformatted" the XML back to one line, the correct server was connected to.
I have no idea what the problem with the formatted XML was. I thought it was CRLF vs LF, but I have at least one config that formatted that way and it works fine. My current thought is character encoding when using TextPad.
My biggest problem is that DTEXECUI doesn't tell you that the config file isn't going to be used. It just ignores the "bad" config format and goes on its merry way using the information in the package itself. That really scares me.
Has anyone else seen this? What is the problem being introduced into the config file?
Thanks,
Frank
Hello there,
I need to get the last value (status) from a child table. I try to simplify the problem with the following example.
Create Table Users
(
UserId int,
Lastname nvarchar(50)
)
Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)
Create Table Status
(
StatusId int
Status nvarchar(50)
)
A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:
Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2
Will not return the wanted records because all authorized Users have been in this status.
I hope you understand the problem and can help me out.
Thx in advance.
Etinuz
Here is some sample data:
CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)
[code]...
I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?
i am able to store multiple email ids in table (separated by comma).
now the problem is how do i retrieve those email ids from the table so that i can send mails to those email ids??
thanks
Jaimin
Is there any way to retrieve the weight % of some values?
A quick example:
A 10 -> Result i want is 10 / 60 (60 is the SUM of A,B and C)
B 20 -> 20 /60
C 30 -> 30/60
Code for number of subscriptions
EVALUATE
SUMMARIZE('Subscriptions',
'Subscriptions Type'[DS_SUBS_TYPE],
"Number of Subscriptions ",'Subscriptions'[NR_SUBSCRIPTIONS]).
I think I've read to many post and now I'm confused on what the exact syntax should be and where in the web config file for connecting to my remote database on the web. I have a SQL Server 2005 database on the web. It was imported by the host from the members ID and passwords database created in Express 2005 from the ASP.Net configuration
Here's the portion of the config file:
<configuration>
<appSettings/>
<connectionStrings/>
<system.web>
Here is the sample connection string my host provided:
DRIVER={SQL Server};SERVER=sqlserver5.loosefoot.com;UID=myadmin;PWD=Your Password;database=eldersmembers
Would someone show me exactly what and where the syntax for the connection string goes?
Thanks in advance
how to solve error SQL server does not allow remote connection
View 2 Replies View RelatedHi
Config files doesn't seem to work for me :( i want to make a common config file for all of my packages but it is giving me error
Validation error. The AcquireConnection method call to the connection manager failed with error code 0xC0202009
I have placed pasword in my config file too. Following is config file which i m using, server name n password are masked.
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="SSBKD955688" GeneratedFromPackageName="DTS_ACPM_MarkIT_Indices_Composites_TRAC_X_Asia_Index" GeneratedFromPackageID="{CB3660A1-5E6C-4D08-A7F5-4D6CE83578E2}" GeneratedDate="2006/10/19 14:05:20"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[ssmgn838.ACPM.acpmuser].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>server=########;user id=sa;password=******;database=ACPM;
</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[Text File (Source)].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>C:acpmfiletouploadCopy of ACPM_MarkIT_Indices_Constituents_Composites_1_ITRAXX_ASIA.tab
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Hi to all
i have sql-clr function written in C#.
i am performing data access opertaion using ado.net with in this function.
what i want ,to store connnection string inside config file.(appesettings)i got one article about this which is bellow
http://www.sqljunkies.com/Article/6CA864E9-E107-408E-B30A-4BA15B0CD11C.scuk#[4]
i follow all steps given in article.
but still getting error when executing function
A .NET Framework error occurred during execution of user-defined routine or aggregate "Amex":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at UserDefinedFunctions.CreateExpense()
can anybody help me....
thanks in advance,
Chetan S. Raut
I am using package configurations to store connectionstring for my database.
While moving the package from development to production, I used the same configuration files (without changing the connection string to point the database to production one). I ran the package in production and it updated the development database.
Then I changed the config files in production to point it to the production database.
However, the packages are still pointing to dev. database.
It seems that after running the package one in Production, it is not reading the updated config file for subsequent runs.
How to I make sure that the packages now read the latest config files.
Hi All,
I am looking for help trying to configure an XML dtsconfig file, I am using different connection managers for my Source and Destination Databases, I am also using an XML dtsconfig file to set these parameters dynamically, however I dont want to use userid/password to connect to my source/destination databases and instead use trusted connection or windows authentication, I remember we had an option in earlier DTS where we could set it up as 0 or 1and use windows authentication, I cannot find this in the new SSIS package properties.
Appreciate your help in advance.
In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.
I just want to be sure I don't need both for my package to run successfully.