I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.
This is set to delete a file, using the file name input from a variable.
If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.
Eversince I've moved my import packages to our new dev server, I have been experiencing strange behaviour in my packages that does the archiving of the .csv files which we import.
These 'Archive' packages doesn't do anything fancy in my opinion and most of them work. To explain how the logic works:
The package consists of a For Each Loop that picks up the file, passes it to a string variable. Inside the For Each Loop container, I have 2 File system tasks. 1 Renames the file and then flows out to another that moves it (I am aware of the fact that I can do the rename and move in 1 go but prefered to keep it separate). The FST that does the renaming, gets its source from the For Each Loop and after renaming the file, the file's new fully qualified path is stored in another variable. This variable then gets to be the source of the FST that moves it to the desired location...like i said...nothing fancy
I am getting errors where it tells me there is a mistake on my Rename FST's expresssion, which is nonsense since I run it on exactly the same files on my machine without problems. Another error is even more baffling...I get the Moving FST erroring out and complaining about not finding the renamed file?? how can this be?? it works also on my local machine, I wonder if it sometimes doesn't pass the variables correctly??
I recently got to install SQL service pack 2 on the box, which improved some things but these packages have been copied over from another server which had service pack 2 on. There are also quite a bit of updates that this server should be running and I was wondering whether these might solve the instability I get?
I have written an udf that checks for validations of an email address and returns 0 / 1 as per the validations. I came across an artical on MSDN that describes the steps to make my own system udf. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp
with help of that artical, I made my udf as a system udf. and now I can use it as follows.
select * , fn_isvalidemail(email) from #tp
now, I wanted to use this as a declarative check constraints in my tables. so I tried
Our VB application throws .dtsx on-demand by using Threads. Everything's fine but a little detail: when it goes to launch a SSIS package with a only task, concretely a Sequence Container task (empty, without tasks inside) fails Prevalidate method.
Hi, I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.
I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.
I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...
So can some please tell me what going on why isnt that data been recognised by the importer in sql server.
Hi, I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.
I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.
I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...
So can some please tell me what going on why isnt that data been recognised by the importer in sql server.
This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub
Hi to all! i had tried to install ads on a windows 5. i am able to connect to the pocket database. however when i trie to connect to the database that's on desktop i see a little window (probably a message) without anything and when i tap ok i see a message telling that the connection to the desktop database was not done! i had made the configuration before. can you tell me what is going wrong?
We have this webiste which uses SQL express as database engine. Sometimes certain features of the website stop working. Like membership provider and other database related things. I have described the problem in more details here: http://forums.asp.net/t/1172253.aspx In consice the problem is: One query with fixed inputs does not always return the same results, though the data has not changed, you restart the SQL express and the problem resolives! I think thats a problem with SQL express, because when you restart SQL express everything starts working. Our database is kinda big. Like above 500 MB with up to 50 concurrent users. And our machine got a 3.2 CPU with 512 MB of ram. And our application is the only application runing there. What do you think please?
I have a scheduled job that inserts some records into a table. It fails with the following message, Violation of PRIMARY KEY constraint 'PK_FuturesOut'. Cannot insert duplicate key in object 'FuturesOut'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
The strange thing about this is, if I copy the SQL statement from the job and paste it into Query Analyzer, it works without any modifications.
If anyone can explain this I would be most grateful.
I use bcp fairly often in SQLServer2000 but have never run across this before. In a 512 SQLCHAR column containing notes, when two spaces are encountered (i.e. ' '), bcp is replacing ' ' with ' '.
I figured it was a problem with my format file, but I have not found enough specific info on the MSDN site to resolve this.
This is my bcp command:
bcp "SELECT DivCode,CommCode,ContactID,substring(Notes, 0, 512) from frep.dbo.BeBackExtract" queryout "D:BeBackDetail.dat" -f "D:BeBackDetail.fmt" -e "D:BeBackDetailErrors.dat" -U user -P pass -S server
Incidentally I also tried using the REPLACE function which seems to work great until trying to replace 2 spaces with 1 space, in which case it doesn't do anything.
I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query
'SELECT ID FROM Table2'
returns: Invalid column name 'ID'. OK. Now when I run the query
'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)'
it returns all the records from Table1.
What gives? Is this a bug, or am I missing something?
I am facing a strange problem. SSIS is executing a piece of code which is inside comment !!!
I have got a master package which executes number of other packages. When I right click on a package and click "Execute task", SSIS respects the comments and ignores code inside it.
But when I execute master package, SSIS executes piece of code which is inside comment!!!
The only thing I can think of is when executed from Master package, SSIS somehow picks very old precompiled binary. BTW, precompile=false for the script component so it shouldn't use precompiled binary at all.
I am sure it will be fixed if I delete and recreate this particular script component with the same script. I (luckily!) picked up this problem but the thought of SSIS not executing intended binary scares the hell out of me.
Any thoughts??? or its just me happened to be using SSIS on Friday?
We have a view looking at all the columns of a table on a SQL server 7.0 db. When we link this view to MSAccess, we are seeing the old data which was there previously in the original table! not the latest data. We recompiled the table and views and also re-linked the view to MSAccess with no help,we still see the old data only.This is happening only for one of the views not for all.Any help??? sa.
When write the following in Query Analyzer (SQL-Server 7 SP1):
declare @aA int select @aa = 1
I get:
Server: Msg 137, Level 15, State 1, Line 3 Must declare the variable '@aa'.
SQL-Server 6.0 and 6.5 seems to work OK.
I have tried several other random combinations f.ex.:
declare @Aa int select @aa = 1
and all seems to work OK
I dicovered the error when I used the upgrade wizard to move a database from SQL-Server 6 to SQL-server 7
sp_helpsort gives the following:
Unicode data sorting ---------------------- Locale ID = 1044
case insensitive, kana type insensitive, width insensitive
Sort Order Description ---------------------------------------------------------------------------- -------------------------------------------------------- Character Set = 1, iso_1 ISO 8859-1 (Latin-1) - Western European 8-bit character set. Sort Order = 183, danno_nocasepref Case-insensitive Danish/Norwegian dictionary sort order, with case preference for collating purposes. Uses the ISO 8859-1 ch aracter set with Windows 3.1 additional characters.
HiI've done a search and found plenty on ORDER BY problems, but mineshopefully a syntax error.This is a test segment from my stored procedure.DECLARE @Name varchar(50), @SortType IntSET @Name = "canal lock"SET @SortType = 8SELECT dbo.ite_Item.ite_IDFROM dbo.ite_Item LEFT OUTER JOINdbo.iti_ItemImages ONdbo.ite_Item.ite_ID = dbo.iti_ItemImages.iti_LinkToItemWHERE iti_ItemImages.iti_LinkToPhotoType=1AND ite_Name LIKE '%' + @Name + '%'ORDER BYCASEWHEN @SortType = 3 THEN dbo.ite_Item.ite_DateWHEN @SortType = 5 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 7 THEN dbo.ite_Item.ite_DescriptionEND ASC,CASEWHEN @SortType = 2 THEN dbo.ite_Item.ite_NameWHEN @SortType = 4 THEN dbo.ite_Item.ite_DateWHEN @SortType = 6 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 8 THEN dbo.ite_Item.ite_DescriptionEND DESC,CASEWHEN @SortType <> 2 THEN dbo.ite_Item.ite_NameEND ASCI get various errors:@SortType = 2. This is a Name field varchar(50)Syntax error converting the varchar value 'Canal Lock' to a column ofdata type smallint.I only have one smallint col and that is the ite_Views column. Why isit trying to convert a varchar(50) col?@SortType = 3. This is a ISO date field nchar(8)The conversion of the nvarchar value '20030909' overflowed an INT2column. Use a larger integer column.Why?@SortType = 7, 8. These are description fields varchar(5000)Syntax error converting the varchar value '<snip>' to a column of datatype smallint.Again, why?All other sort types are fine.What I'm thinking is that when the ORDER BY code is run, it does someconversion but what?Many thanks in advance if anyone has any ideas?Sam
A colleague of mine has discovered some behaviour in the XML Source component that I am having trouble understanding or explaining. Here is the (obfuscated) XML document that we are looking to parse:
I'd like to draw your attention to the bit I've highlighted in red. SSIS has incorrectly defined this attribute as an unsignedLong. if you look at the data above you'll see that it has leading zeros therefore we need it to be interpreted as a string. Unfortunately (and here's the problem) there doesn't seem to be a way for us to change the generated xsd and thus refresh the external column metadata. The only way to change it is to go into the Advanced Editor and manually change the external column and output column metadata.
Is this by design? It seems very limiting if you ask me to not let us have control of what the metadata should be.
I've got a SSIS package which comprises a pair of Sql Task and after that a Data Flow. Such Data Flow own two Data Viewers, one between my Excel Source and my Derived Column and the another one between my Derived Column and my Ole DB Destination. Up to here, everything's fine.
Package really works properly but when I execute only the first Sql Task runtime shows (as usual) Excel Source Ouput Data Viewer1 and Derived Column Output Data Viewer1. Both windows empty, of course. Problem comes when I choose 'Copy Data'. Inmediately, appears a dialog with the message: "Windows has encountered a problem and needs to close"
I wonder, at first, why on earth these two windows are showed when I execute a task which belongs to Control Flow no Data Flow????? What happen?
I defined a report based on a matrix having 3 field groups. I defined Interactiveheight to have fixe number of rows in HTML rendering. My problem is that this parameter is only taken into account after first page. My first page always breaks after end of first field group + 1 row of next group.
Do you have any idea, what's causing such behaviour? And what would be the solution?
I have a VB6 front end connecting to SQL Server 2005 using the SQL Native Client.
When I execute a stored procedure the returned recordset contains more fields then it should. These extra fields seem to be the fields that the tables are joined on.
This happens with every stored procedure(simple to complex).
If I change the ODBC to use the standard SQL Server driver it works fine.
Under Management Studio, when I right-click a stored proc, select Modify, change the stored proc, then select Execute, the stored proc is updated on the server. But then I noticed that the sql tab holding the changed stored proc (in the right pane of Management Studio) still diplays and asterisk (*). When I right-clicked the tab it offeres the Save option but that is to save the sql file (with the * in its tab) to a file. This is confusing behaviour.
Is there any way to change this behaviour so running Execute causes the (*) to disapeear?
Hello to everybody. I am experiencing a strange behaviour of Report Builder. I have an AS2005 database with a cube with calculated members (all measures calculation). In the client application they need to see only the calculated members so I setup the visible option to "false" for all the others measures. If I browse the cube with browser of AS I have no problem, I can see the data in any shape possible. To give an example I have a report that shows for each Store how many visitors I received each month. The report has the store in the rows and the months in the columns.
If I try to create the same reports with Visual Studio 2005 I have no problem either. Everything works fine. After doing these test I build up a model for the report builder. I connected to the report builder and I tried to create the same report and I got this message: "No data was found that matched your report criteria". After that error I need to create a new report and start all over again. If I create a simple report with just the store and the number of visitors I do not have any problem.
I have a fact table which is a custim query in the data source view, I process and work with the data, after somewhile I make some changes in the data source view query but all my changes does not reflect in the cube after processing the same old data returned.
When select this data with an order by like: select test from table_2 order by test The result will be:
Code Snippet
test 1-1.00.00.00 1000 2000 If you sort the data by the SORT block of the SSIS the result will be:
Code Snippet
test 1000 1-1.00.00.00 2000This is annoying and dangerous, because it causes the next bug.
2/ Two datasources sorted by ORDER BY clause can give problems in a Merge Join.
If you have 2 data sources both correctly sorted by an order by in the query. When you join these 2 datasources with a Merge Join, you can lose some records in the dataflow. This happens with larger datasets than examples above.
There's *strange* behaviour with snapshot size during subscription initialization.
So...
The initial snapshot has size about 9Mb at the publisher.
After subscription creation, I notice that the subscriber downloads about 21 MB.
It seems to me, that the snapshot is unpacked on the IIS, next, part of the files are packed again and just after it the changed snaphot is sent to the subscriber.
I'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
Does anyone know how to do this using variables? Everytime I try it, I get the
Error: Failed to lock variable for read access with error 0xc00100001.
I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have: FileDestinationPath variable - set to C:TestFiles FileSourcePath variable - set to C:TestFiles FileNameAndLocation variable - set to blank
For Each Loop Container �� Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) �� Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(��User::FileDestinationPath�?).Value = dts.Variables(��User::FileDestinationPath�?).Value & ���? Month & ��_�? & Day & ��_�? & Year & ���?) which gives me ��C:TestFiles9_14_2006�?.
File System Task (within For Each Loop, second step) �� This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day��s date. Basically, how do I get this to work since I can��t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the ��Variable ��FileNameAndLocation�? is used as a source or destination and is empty.�?
Let me know if I need to clarify further��...I may be missing something very simple. Any help would be greatly appreciated!
