Challenging Search Task Is Not Working As Expected

Sep 14, 2007



Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1
----------------------
-searchword- column name
--------------------------------------------
Learn more about melons row0
--------------------------------------------
%.txt row1
-------------------------------------------


table2
------------------------------
-testname- column name
--------------------------------------------
FKOV43C6.EXE
-------------------------------------------
frusdr.txt
-------------------------------------------
FRUSDR.TXT
------------------------------------------
SPGP_FWPkg_66G.zip
------------------------------------------
readme.txt
-----------------------------------------
README.TXT
----------------------------------------
watermelon.exe
----------------------------------------
Learn more about melons read me.txt
-------------------------------------------------------


Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================


select * from @table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @table1 ta

JOIN @table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @table1)



)
===============================================================================

script to create tables
============================================================================

DECLARE @table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @table1 (

searchword

) VALUES ( '%.txt' )



DECLARE @table2 TABLE (

testname VARCHAR(255)

)



INSERT INTO @table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @table2 (

testname

) VALUES ( 'readme.txt' )



INSERT INTO @table2 (testname

) VALUES ('README.TXT' )





INSERT INTO @table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @table2

DECLARE @table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )






===================================================================================





View 4 Replies


ADVERTISEMENT

FTP Task Not Working As Expected - File Not Downloading

Feb 11, 2008

I have a package with an FTP task in the Control Flow. Nothing complicated, its configured to download a file from FTP with overwrite true, and the get and download paths in variables. Once this step completes it goes to a sequence container that does stuff with the file, that part works fine.

The problem is if i run the package in debug mode using Visual Studio, everything works perfectly (even if run over and over). The problem occurs if i use my "driver" application to try and execute the package. All my driver does is use C# code to create an Application object, set the PackagePassword, LoadPackage based on the path and then .Execute().

Here is when the strangeness begins:. If the file exists in the destination path, then the FTP task fires. If the file does not exist (lets say i manually delete it after its being downloaded before using the IDE) the FTP task dose not fire and my Sequence Container fails to use the fail, since it is of course missing.

Monitoring the FTP server that i am running locally i can see commands firing when the file exists, but when the file is killed it never even tries to connect to my server. Very odd.

Am i missing something? Why would this work from the IDE but never from my driver?

Any help would be appreciated. Please let me know if any additional detail is required.


Dmitry
http://blog.lyalin.com

View 6 Replies View Related

Sql Server 2005 Full Text Search Not Working As Expected On Production Server

Jan 28, 2008

I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows,  the full text search works perfect, but as the number of rows increases,  the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)       

View 2 Replies View Related

SQL Search :: Can't Get Expected Results With Contains And Full Text Search?

Nov 1, 2015

I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:

123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999

I am trying this query:

select * from Mytable where
contains(MyColumn, '123.345.')

I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:

select * from MyTable where
MyCOlumn like '123.345.%';

View 12 Replies View Related

Encrypt Not Working As Expected

Oct 18, 2007

Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.


select *

from [dbo].[dmTable]

WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'



select *

from [dbo].[dmTable]

where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')

View 1 Replies View Related

RMO - FrequencyType Not Working As Expected

Oct 9, 2006

Hi,

In SQL 2005 SP1 - In my transactional replication RMO C# script, I want my snapshot job schedule to run daily at 2:58 AM. 

Instead it runs hourly in the 58th minute. Sample code below shows I use the value 025800. That should be interpretted as AM. The frequencytype is daily. The frequency interval is 1. There is no subday frequency. Yet the job runs hourly and disregards the specified hour.

Is there something missing in this code? Is this a SQL Server bug?

    // Set the required properties for the trans publication snapshot job.
    TransPublication tpublication = new TransPublication();
    tpublication.ConnectionContext = conn;
    tpublication.Name = publicationName;
    tpublication.DatabaseName = publicationDbName;
    tpublication.SnapshotSchedule.FrequencyType = ScheduleFrequencyType.Daily;
    tpublication.SnapshotSchedule.FrequencyInterval = Convert.ToInt32(0x0001);
    tpublication.SnapshotSchedule.ActiveStartDate = 20051101;
    string newString = "025800";
    tpublication.SnapshotSchedule.ActiveStartTime = Convert.ToInt32(newString);
    tpublication.Create();

 

View 3 Replies View Related

Trim And Update Not Working As Expected

Feb 25, 2006

I was hoping that the Trim function inside the update command, cmdUpdate.Parameters.Add("@doc_num", Trim(txtDocNum.Text))
, would deal with any leading and trailing spaces, but it does not seem
to be doing anything at all. The value from the textbox still arrives
in the database table with leading spaces!!

Am I doing something wrong?

View 7 Replies View Related

Transactions And Checkpoints Not Working As Expected

May 2, 2007

I have a package that has a container containing multiple DF Tasks.

The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.

This works as expected.



However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.

However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.

This is not the functionality I see. The package restarts from the failed task within the container every time.



According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.



A previous forum message encountered the same issue it appears:

See SSIS Checkpoints 04 Dec 2006.



This is an extract from it:

"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.

In short, I felt that check points were not aware of transactions.

So, I ended up with this setting and it works for me:

Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.

That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."



While this makes sense to me it is not the same properties that the SSIS book has that work.

Additionally, this didn't work for me either !!

I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.



The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.

View 1 Replies View Related

Report Viewer Links Not Working As Expected

Nov 20, 2007



Hi all,

Heres the setup...

We have an asp application that runs the reportserver URL for the selected report, passing it through parameters. This opens the report viewer and the report runs.

The problem im having is that one report is not working as expected. When the report is run from report manager, everything works fine. The links do what they're meant to (they link to other reports passing through parameters). When the report is run from our asp application with the report viewer, the links fail.... they dont pass through the correct values or sometimes dont pass through a value at all.

Were the report viewer and report manager applications developed seperately?

Any help would be appreciated.

Thanks.

View 5 Replies View Related

Parent/Child Packages And Transaction Option Not Working As Expected

Dec 18, 2007

Has anyone had experience of using Parent/Child packages while enlisting them in Transactions.
I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.

The parent package essentially calls three child packages.
In each child package there are multiple DFT's that import and transform data into SQL Server.
All data must be imported or not at all.
Therefore I created a FELC container into which three Exec child package tasks were placed.
The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported.
Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.

So initially we had in terms of container hierarchy for the Trans Option property:
Parent package Supported
FELC for calling child packages Required
Task execute child package Supported
Child package Suppored
Tasks Suppored



Looking at this more closely we thought that we would need
Parent package Supported
FELC for calling child packages Required
Task execute child package Required
Child package Required
Tasks Suppored



for it to work.
However, the latter now gives us failures with error messages on the tasks on the child packages.
[Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.

Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error.
These tasks also have Event handlers.

View 7 Replies View Related

Full-text Index Search Not Returning Expected Results

Apr 11, 2007

Hello,

My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.

I'm testing this search, and it doesn't work. The table has the following columns:
attachemtId (int) - primary key
fileContent (image) - contents of the file
fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")


I created a .doc file that simply says "ssn", and then run the following query:

SELECT * FROM TempAttachment
WHERE CONTAINS(fileContent,'ssn')


and nothing is returned! I tried the same thing with a .pdf file, and same results.

I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!

View 1 Replies View Related

Full Text Search Does Not Return Expected Results On Production Server

May 7, 2013

I have a FullTextSQLQuery which I am trying to search a phrase(The Multi-part identifier) on full text indexed table. I am getting expected results on running the below sql query on QA machine and PreProduction servers, but not getting the same results on our development and production servres as even though same code running.

SELECT DISTINCT TOP 50 c.case_id,c.status_id,cal.cas_details
FROM g_case_action_log cal (READUNCOMMITTED)
INNER JOIN g_case c (READUNCOMMITTED) ON (cal.case_id = c.case_id)
INNER JOIN CONTAINSTABLE(es.g_case_action_log, cas_details,
' "The multi-part identifier" OR "<br>The multi-part identifier" OR
"The multi-part identifier<br>" ') as key_tbl
ON cal.log_id = key_tbl.[key]
ORDER By c.case_id DESC

We are using SqlServer 2008 R2 version on all servers.

View 1 Replies View Related

ForEachLoop Task Not Behaving As Expected

Mar 30, 2007

Hi,



I have a ForEach Loop that has 3 script tasks in it.



I have them set up so that they execute in order, such as:



script1 ---> script2 ---> script3



script1 creates a file

script2 creates a file

script3 compares the files using a diff command



Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.



The error is "The system cannot find the file specified".



Thanks



View 2 Replies View Related

Full Text Search- Substring Search Not Working

Jul 6, 2007

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.

View 7 Replies View Related

[Execute Process Task] Error:The Process Exit Code Was -1 While The Expected Was 0.

Mar 11, 2008

Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server

What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%



However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".

There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log

Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0

thanks in advance
Dave

View 1 Replies View Related

Execute Process Task Error - The Process Exit Code Was 1 While The Expected Was 0.

Jan 30, 2007

How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:

SSIS package "IngramWeeklyPOS.dtsx" starting.

Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".

Task failed: Unzip download file

SSIS package "IngramWeeklyPOS.dtsx" finished: Success.

Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:

SSIS package "IngramWeeklyPOS.dtsx" starting.

Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".

Task failed: Unzip download file

SSIS package "IngramWeeklyPOS.dtsx" finished: Success.

The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.

Thanks,

Monisha

View 1 Replies View Related

Execute Process Task - Error :The Process Exit Code Was 2 While The Expected Was 0.

Mar 20, 2008



I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.

For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.

I am using the Execute Process Task and the process parameters I am providing are:



WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat

The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"

the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.

The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :

[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]

Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.

Can anyone help ?




View 9 Replies View Related

Search Engine Is Not Working

Sep 12, 2002

Swynk have implemented a new search engine on this site. I have found that
whatever I search for it finds nothing. Does anyone know of a work around?

View 2 Replies View Related

SQL Search Results Not Working???

Dec 10, 2005

Can anyone suggest whey I dont get any results with this search?

SQL reads:

SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'

MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("location")
MMColParam3 3 Request.Form("category")

Mally.

View 4 Replies View Related

Search Not Working With String Consisting Of Two Letter

Feb 25, 2007

hi friends,
 I have search in my project based on strings(surname, firstname).. but it does'nt works wen name consists of only two letters.... could somebody please help me on this.
 
Thanks sandeep

View 3 Replies View Related

Wild Card Not Working In Search String

Jan 3, 2005

In the below section of code I'm connecing to a SQL database and when I use the "=" in the search it works ok, but when I try to add the ability to use wildcards by changin "=" to "LIKE" its not working

Working code:

Function GetName(ByVal first_Name As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name = @First_Name)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function


Code that I would like to work

Function GetName(ByVal first_Name As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name LIKE '%' + @First_Name '+')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function


Any help would be greatly appreciated

View 3 Replies View Related

Full Text Search Not Working For Sql2k

Dec 7, 2007

Hi

Issue1:
I have a sql 2k machine. I hav enabled full text indexing on this machine. There after, i inserted and updated so many records. When i do a search using the below query:
It gaves me nothing

select *
from table1
where contains
(
col1,'"keyword"'
)

Issue 2: Another issue is when i gives multiple columns in the below query for search, it throws up a syntax error message at (

select *
from table1
where contains
(
(col1,col2,col3),'"keyword"')
)


Do sql 2k does not allow this format of query(above). But, the same is working fine with one column.

Any inputs on this is highly appreciated.
Thanks!
Santhosh

View 1 Replies View Related

Full Text Substring Search Not Working

Jul 12, 2007

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.

View 2 Replies View Related

SQL Mail Task Not Working

Apr 3, 2008

Hi,

I set up a sql mail task. Getting this error at runtime:

[Send Mail Task] Error: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Unable to relay for me@my.com".


Has anyone seen this? For the SMTP Server in the smtp connection, I am simply using the mail server name as was set up in db mail.

Help!

View 7 Replies View Related

Process Task - Time Out Not Working

Dec 6, 2007

I am assigning package string variable to the StandardOuputVariable of a Process Task, and setting the TimeOut to 3, TerminateProcessAfterTimeOut to True, plus the Executable name. The program is executing about 40 seconds, and it seems not to terminate after the 3 seconds has passed.

When I execute the same package but without the StandardOuputVariable the task terminates after the 3 seconds.

Is this a bug or I shouldn't expect the task to terminate when the StandardOuputVariable is set???

View 3 Replies View Related

FTP Task - StopOnFailure Not Working As I Expect?

Dec 8, 2006

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.


What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

View 7 Replies View Related

For Each Loop And Script Task Not Working Properly

Oct 5, 2007

Hi,

I have a FOR EACH LOOP that loops through a bunch of folders in search of particular filename (including path). That part works fine.

Then, INSIDE the FOR EACH LOOP, I have a script task that evaluates the filename and grabs the date portion of the filename. That part works fine too.

Now, here's my dilemma. If the date portion of the file name IS GREATER THAN the the value of another date in a variable, I want it to load the file into a table using a data flow task.

So, this is what my package looks like:

[ FOR EACH LOOP (Script task --- evaluate the date using an expression (see below) ---> Data flow task) ]

My expression looks like this:

@[User::fileDate] > @[User::maxLoadDate]

You think that this would work, right??

However, after the VERY FIRST iteration of the loop, the script task turns green, then it goes right to the data flow task, which then says that the file name is "", and can't be loaded. (I'm not sure why this is occuring)

My question is, is my logic correct? Can I use an expression inside the for each loop and have it work?





View 1 Replies View Related

Script Task Working In Visual Studio But Not When The Package Is Run By A Job?

Aug 5, 2006

I have a script that changes the name of a file after a data upload. The script works fine if I execute the package in Visual Studio but when I run the file package from a SQL server job it does not rename the file. The data does get uploaded it just does not run the final script.

Any help would be appreciated.

Steve

View 1 Replies View Related

Calling Oracle Procedure From SSIS 'Execute SQl Task' Is Not Working

Nov 6, 2007

Hi ,


Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine

Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.

Please help me.. thanks in advance

Regards,
GK

View 5 Replies View Related

Transfer SQL Server Objects Task Not Working With Stored Procedures

Sep 17, 2007



I'm unable to copy my Stored Procedures from one database to another. I'm using mixed mode authentication. I have set CopyAllStoredProcedures to True, DropObjectsFirst to True and CopySchema to True.


Nothing gets copied. I have followed many web sites that say Transfer SQL Server Objects Task is broken. Is this true and I should give up?

Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume is the update for SSIS? yes ?

Thanks for any help

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "CREATE PROCEDURE [dbo].[del_Admin_RemoveContractorFromContract] @ContractID int, @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID " failed with the following error: "There is already an object named 'del_Admin_RemoveContractorFromContract' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

View 4 Replies View Related

Exec SQL Task: Capture Return Code Of Stored Proc Not Working

May 19, 2006

I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".



Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure





CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT

---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO

Thanks in advance

Dave

View 6 Replies View Related

Integration Services :: Send Mail Task Not Working Properly When Configured In Precedence Constraint (Failure)

Jul 29, 2015

Send Mail task not working properly when it configured in Precedence Constraint (Failure)...

Scenario:

I'm using multiple containers in my ssis package. Also I have configured mail task for capturing error.

I'm getting mail only when error occurs in foreach loop container. If error occurs in other containers it doesn't works.

Actually I would like to receive mail notification on error when it occurs in any of the containers or DFT.

View 9 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

I'm just wonder if this is a bug in MS Search or am I doing something wrong.

I have a query below

declare @search_clause varchar(255)

set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'

select * from results

where contains(finding,@search_clause)

I don't get the correct result at all.

If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"

then i get the correct result.

It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?

Anyone know?

Thanks

View 3 Replies View Related







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