Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







File System Watcher Can Not Read Excel From SharePoint


I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

 


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
File System Watcher
Hello,

I have to make a application using filesystemwatcher which watches the file on the remote server. If that file is changed then I have to fetch that file from ftp location to the server. I have made the SSIS job to fetch the file but not able to set a file system watcher on remote server that watches the file. If the file is changed then I need to insert a row in the SQL server database and then fetch the file.

I have made the application. the code is below:

private void Form1_Load(object sender, EventArgs e)

{

// Create a new FileSystemWatcher and set its properties

FileSystemWatcher watcher = new FileSystemWatcher();





watcher.Path = "C:\";

watcher.IncludeSubdirectories = true;

/* Watch for changes in LastAccess and LastWrite times, and the renaming of files or directories.*/

watcher.NotifyFilter = NotifyFilters.FileName | NotifyFilters.Attributes | NotifyFilters.LastAccess | NotifyFilters.LastWrite | NotifyFilters.Security | NotifyFilters.Size;

//watcher.WaitForChanged(WatcherChangeTypes.All );

// Add event handlers.

watcher.Changed += new FileSystemEventHandler(OnChanged);

watcher.Created += new FileSystemEventHandler(OnCreated);

watcher.Deleted += new FileSystemEventHandler(OnChanged);

watcher.Renamed += new RenamedEventHandler(OnRenamed);

watcher.EnableRaisingEvents = true;

}

public static void OnChanged(object source, FileSystemEventArgs e)

{

MessageBox.Show(e.FullPath + e.ChangeType.ToString("G") + "" );

}

public static void OnCreated(object source, FileSystemEventArgs e)

{

MessageBox.Show(e.FullPath + e.ChangeType + "");

}

public static void OnRenamed(Object source, RenamedEventArgs e)

{

MessageBox.Show(e.OldFullPath + e.FullPath + "" + e.OldName);

}

Now how can I give the ftp details such as IP address, User name and password in the above code.

 

Regards,
Deepesh Verma

 

 

View Replies !   View Related
Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.
I have the Excel Connection Manager and Source to read the contents from an Excel file.  For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150.  I am not sure why this is happening.  I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields. 

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

 

View Replies !   View Related
Is There A Way To Read A Excel File
into the sql query analyzer?
Thanks.

Jeff

View Replies !   View Related
Read XML File From File System?
Anyone reading XML disk-files into SQL Server?

I have a process that I may want to do this with.

It would be a stored procedure that would read the XML attributes into 2 tables, the number of attributes could be 1-N, so I thought XML would be a good choice. Also, one of the attributes could be up to 4000 characters. I think this may limit our options, can 100-150 4000 character strings be passed in a standard call to a query/proc in SQL?

Currently the client application makes round-trip network calls to save upwards of 100 pairs of data. 1 header row, and many detail rows. All within a transaction.

I think If we move an XML file to the SQL box, then do all the import/save work on the "Server" side it would be much better. Cutting the transaction time down a lot by not doing so many round-trips at network speed...

Thoughts?

View Replies !   View Related
File Watcher Task Doesnt Wait For The Entire File To Be Completely Finish Before It Completes
Hey there

Ive built an SSIS package which generates a file from a legacy system and then downloads the file into a designated folder on the server. I need the file watcher task to wait for a the file to completely finish loading before it says it is complete. Currently, as soon as the file is created, the WMI step finishes.

Any help would be greatly appreciated!

Kind Regards

David

View Replies !   View Related
File Watcher Task, Where???
Where the earth is this task??? I am not be able to see it anywhere!!

Thanks for any input,

View Replies !   View Related
File Watcher Task
Installed File watcher task, then created a new SSIS package by adding the file watcher task in the control flow, below are my setting's inside the file watcher task editor:
2. Options
Filter - *.*
Find Existing Files - True
Timeout warning - False
3. Path
Path input type: Direct imput
Path - \server1Sourcefolder
Include subdirectories - False
Actually my understanding about this task is:
This file watcher task will constantly check for the file and then will execute the SSIS package.
If my understanding is correct, then where do we specify the name of the SSIS package?? And also do we need this SSIS package which has the File watcher task to run every 5 minutes or so, so that it can keep checking for the file existence.
Please help, thanks!!

View Replies !   View Related
File Watcher Task
In the file watcher task in the filter section does it look for exsiting file name which we have mentioned or is it possible to give first few letters of the file name like
pat*.txt
 
Please let me know

View Replies !   View Related
HELP: How To Avoid Ridiculous System Resource Issues When Connecting To A Huge Excel File
Hi all,
 
I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.
 
I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:
 
Intel Centrino Duo 2.00 GHz
2GB RAM
XP Pro SP2
 
There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.
 
Sincerely,
 
Brian Pulliam

View Replies !   View Related
Konesans File Watcher Tasks
Hello All

Just curious if there are any tutorials/help files/forums on using this and other Konesans control Flow and data flow task? most import the flow watcher task.

thanks

Karen

View Replies !   View Related
Passing Variables Through File Watcher Task
 

Grettings All

Since I am a novice still with ssis this may seem like a simple task to most. What I want to do is use an sql task that executes a view and stores the results is some varaible (var1). Next I use the konesans File watcher to look for the existence of a file (testfile.txt). If the file exists the data currently stored in var1 is appended to the bottom of the existing file, if the file does not currently exists it is created based on var1 data.  With all this out in the open what is the best method for doing this?  I appreaciate any help I can get on this

thanks

kam

View Replies !   View Related
Anytime I Connect To An Excel File From SSIS05, I Start Getting Endemic System.outofmemoryexception Errors And Cannot Save
 

This is my error text... how do I get this nonsense to stop?!

 

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

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

------------------------------
Program Location:

   at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
   at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
   at System.Text.StringBuilder.Append(Char value)
   at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
   at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
   at System.IO.TextWriter.Write(Char[] buffer)
   at System.IO.TextWriter.Write(String value)
   at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
   at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
   at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
   at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
   at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
   at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
   at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

 

View Replies !   View Related
SSIS File Watcher Multi-Threaded Task?
Hi All,

I have a problem in which I want the run an SSIS based on a file being dropped in a directory.  I've tried the WMI event watcher as well as the File Task Watcher component.  The problem that I'm seeing is that if the process is currently processing in the pipeline while another file dropped, the newly dropped file doesn't get picked up.  Is there a way to create a FileWatcher Task in SSIS which will spawn a SSIS job and return immediately for watching files?

View Replies !   View Related
Reports Exported As Excel Cannot Be Read By Mac Version Of Excel
I searched the list but did not see anything related.

I cannot open a file directly exported from RS in the Excel format.  If I open the file in the Windows version and then close it, no need to even save it, I can then open it with the Mac version of Excel.  The file size after closing is about 2K less so the RS program is adding something onto the file that the Mac version does not like.  It will crash Excel when trying to open.

I cannot export the report as CSV as it will not correctly import into Excel on the Mac.

Is there a work around for this?

Thanks.

--sean

View Replies !   View Related
Read Text File From SQL Server, Read Its Content, And Load It In RichTextBox (Related Component: Context.Response.BinaryWrite(), And StreamReader)
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.

View Replies !   View Related
Beginning SSIS - Sharepoint And Excel
Hi all, I have never used SSIS before, but am looking to use it in this aspect:

1) A user uploads an Excel file into Sharepoint. This will be through a Document Library in a Sharepoint page.

2) When this file is uploaded, I'd like SSIS to notice there is a new file and process it - it will pull information from the Excel file, put it into a database, and - if this is possible - delete the new file.

3) This is iffy. Can SSIS then generate an InfoPath document  from the information stored in the database? If not, I can just have a InfoPath query form.

I'd just like to know if this is possible. If you have any helpful links, please let me know - I would greatly appreciate it!

Thanks,

James

View Replies !   View Related
WMI File Watcher In Control Flow Issues &&"Quota Violation&&" Error
My SSIS control flow includes a standard "textbook" WMI Event Watcher Task that monitors a folder for incoming files. Generally it works fine, but now I regularly see the error message:
 
"Watching for the Wql Query caused the following system exception: "Quota Violation." Check the query for errors or WMI connection for access rights/permissions"

 
I do not see any indication of trouble in the event logs. The SSIS log simply states that it failed.
 
Is there any magic about WMI Event Watcher?
 
When I restart, it runs fine for hours.
 
SQL05 is 9.0.3054 on W2003 with all microsoft updates applied. It is basically a bare machine with SQL Server, SSIS running and a service that kicks in occasionally.
 
Thanks for reading!

View Replies !   View Related
File Watcher Task (Konessans) Vs For Each Loop Task
 

HI, I need to trigger some packages upon existance of specific files in a particular directory. Sound lkike the file watcher task (from SQLIS) would do the job but I am wondering what is the difference of using this tool instead of a for each loop container. I mean, If a file exists in a directory, the for each loop container will detect it. Since the file watcher is not a service, the package containing it needs to ne scheduled on a regular basis for the filewatcher to detect the file, right? So, a for each loop container would do the job? So, waht wouldbe the advantage of using the file watcher task?
 
Thank you,
Ccote

View Replies !   View Related
Excel - Read-only
I created a system DSN for an Excel file and I have been using it for awhile. I can read from it as well as update it. (The Excel fileincludes weekly updates to some records.)Then I tried to create the same connection with an explicit connectionstring. It opens the file fine, I can read from it and all. But whenI try to update it, it complains that the database is read-only. I amusing the connection string: "Driver=Microsoft Excel Driver(*.xls);DBQ=excel-fle-path;Uid=;Pwd="Is there a switch to include to say that I would like to open the Excelfile as a read/write database just like via the ODBC set up?

View Replies !   View Related
How Tro Read Excel Through Variable
Hi Team,
I have created on SSIS package which export data from excel to SQL server 2005.It's working fine.Now I am trying to make it dynamic using variable. It means I wil just change the path or name of the excel file
How to access excel file using variables ?
Need help any link,suggestion .....

Yogesh V. Desai. | SQLDBA|

View Replies !   View Related
Read Each Excel Sheet
 Hi,
I have an excel spreadsheet with several sheets.
These sheets get populated with data from an external source database i.e. a third party application.
I would like to create a SSIS package to read the data in each sheet.
Please note that I do know how to create SSIS package to read an excel file with only one sheet.
Is there a functionality in SSIS to loop though each sheet in one excel file?
Thanks

View Replies !   View Related
Export To Excel: Read-Only Columns?
 

When exporting a report to excel is there any way of marking certain columns as being readonly in the excel file?
 
Thanks
Niall

View Replies !   View Related
Read Text File From Flat File Connection Manager SSIS
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
 
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{
 
//Some code to read file and write it into new file

return DTSExecResult.Success;

}
 

public const string Property_Task = "CustomErrorControl";

public const string Property_SourceConnection = "SourceConnection";

 

public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)

{

if (node.Name != Property_Task)

{

throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));

}

else

{

try

{



_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;



}

catch (Exception ex)

{

infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);

}

}

}

public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)

{

try

{

// // Create Task Element

XmlElement taskElement = doc.CreateElement("", Property_Task, "");

doc.AppendChild(taskElement);

// // Save source FileConnection

XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);

sourcefileAttribute.Value = _sourceConnectionId;

taskElement.Attributes.Append(sourcefileAttribute);

}

catch (Exception ex)

{

infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);

}

}
 
In UI Class there is OK Click event.

private void btnOK_Click(object sender, EventArgs e)

{

try

{

 

_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);

btnOK.DialogResult = DialogResult.OK;

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

#endregion

}

View Replies !   View Related
Can One Remotely Access And Read Messages From A System Dead Letter Queue (MSMQ)?
 

I want to read messages from a system dead letter queue remotely. is this possible?

I tried using 

"FormatName:DIRECT=OS:<servername>XactDeadLetter$"

as the queue path but the CanRead property of the message queue returns false.

Please help.

Thanks

yvidarsh

View Replies !   View Related
How?-- SQL Read/import Excel Worksheets Within One Workbook???
Hi all,
I have a huge data set captured in excel file which contains 90 worksheets.
There are total 3 worksheets I need to work on.

I need to load/transfer them into a table. How do I import them into SQL?
Can SQL read/load excel by reading each worksheet (sheet1, sheet2...sheet90)?

Any suggestions are welcome and highly appreciated!

shiparsons

View Replies !   View Related
Excel: Read Column As Text (IMXE=1 Does Not Seem To Help)
Hi!

We currently have a problem while reading Excel files. The file has one column we want to read as DT_(W)STR which Jet delivers to us only as DT_R8 as there are plenty of numerical entries in that column before one alphanumerical appears.

We first tried with IMEX=1 but as described in http://support.microsoft.com/kb/194124/en-us will only parse the first eigth characters as "TypeGuessRows" is set to 8 and can't be edited.

Is there any way to read a column in Excel in a way typed by the SSIS designer (DT_(w)STR in our case)?

 

With best regards,

Hauke Schlichting

PS: Designing a "decoy" Excel file with column headers and first columns set is not really considered an option ;-)

View Replies !   View Related
SSIS Excel Source Read Problem
I have a problem with reading data from an Excel file in SSIS. I'm trying to read a column that mostly consists of decimal values, but there are couple places where column entry is 2 numbers separated by a slash (e.g. "100/6.0"). SSIS tries to be smart and identifies the column data type as decimal and when it reads the cell with the slash in it, it reads as NULL. I tried to make my excel source reader component to read that cell as a string, but it gives me an error. If anybody has come across something like this, I would highly appreciate some help

-Erlan

View Replies !   View Related
Read From Multiple Excel Sheets Using SSIS
 

How can I read from the multiple excel (sheets) file using SSIS? I want to read it using C# - desktop application.
 
Please let me know.
 
Thanks

View Replies !   View Related
How To Read A Cell Of Excel In Script Task?
BTW, I cannot use Office automation because MS office is not installed on the machine.

Thanks,

View Replies !   View Related
Excel Type Report Not In 'Read Only' Format
Hi,

When we export our tabular report in EXCEL format, we can change the values of the report data.
How can I export the report data in read-only EXCEL?

Awaiting some reply.

Thanks in advance!

View Replies !   View Related
How Read File CSV File In Remote Server Using Bulk
Hi All,

I need to read a csv file, which is in remote server using SQl Bulk Insert Command.

Can I read a file Which is in remote server using BULK INSERT.

Thank you.......

View Replies !   View Related
Using A Excel Source To Get The Data From An Excel File Gets Null Values For A Couple Columns
 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

View Replies !   View Related
Excel Destination Appends The Excel File Everytime A Package Is Executed
i have an SSIS package that exports to an excel file.  This works fine.  the problem is that it appends the data instead of overwriting the file.  Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually.  Any help would be appreciated

View Replies !   View Related
Reporting Services And Sharepoint Integration - Sharepoint Alternate Access Mappings (Zones)
I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:

System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.


Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?

Many thanks to anyone who can help!

 

 

 

View Replies !   View Related
Sharepoint Issue : Access/Download Documents From A Sharepoint Site From Application In XP.
Hi
I need to write an windows desktop application which will download documentsdata from a Sharepoint site.
I am developing the application on windows XP.
 
Is there a way to create an application in windows XP which will talk to Sharepoint sites.
I am using Visual Studio 2008.
 
Please let me know if you need more clarification from side.
 
Your help will be very much appreciated.
 
With Regards
Subhajit Dutta
 
 

View Replies !   View Related
File System Task - Move File With Dynamic Destination Path
I am having an issue with the File System Task.

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!

 

View Replies !   View Related
Help Getting Error When Using Operation Rename A File In The File System Task Editor?
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:

@[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:Documents and SettingsmroushDesktopOSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

 

View Replies !   View Related
File System Task Error When Using SQL Server Agent (when Move File On Network Drive)
I am able to run SSIS packages as SQL Server Agent jobs with a  Control Flow items "File system task",  if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive.  But, if I try to move a file on a network drive, the package fail.

 

What I can do to solve this issue.

 

Bye!

Daniel

View Replies !   View Related
DTS Migration Wizard Failed To Save Package File -with Error 0x80070002 - Th System Cannot Find The File Specified.
Hi,

I use the DTS 2000 Migration Wizard to migrate one of the DTS 2000 packages to SSIS.  The migration failed with the following error message:


LogID=17
#Time=6:31 PM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:Documents and SettingsfuMy DocumentsVisual Studio 2005ProjectsKORTONKORTONProcessCubesMF.dtsx" with error 0x80070002 "The system cannot find the file specified.".
 ---> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:Documents and SettingsfuMy DocumentsVisual Studio 2005ProjectsKORTONKORTONProcessCubesMF.dtsx" with error 0x80070002 "The system cannot find the file specified.".

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
   at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
   at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)

Looking at the call stack, it looks like COM wrapper fails on SaveToXML.  Can someone tell me how I should workaround this problem?

Thanks,

Bobby Fu

View Replies !   View Related
Cannot Attach Mdf: Create File Encountered Operating System Error 5 While Attempting To Open The Physical File...
I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.

Any way I can recover it?

Thanks.

View Replies !   View Related
File System Task - Output File Variable Syntax????
 

Hi

This should be incredibly simple and easy, but I can't find any examples of how to do this.

I just want to make a File System Task move a file, and have the destination be filename + date and time.  For example \serversharefilename02072007.txt

What syntax do I use in a variable to make this work?

Thanks

 

View Replies !   View Related
DTS From Excel File (excel Filename Is Different Everyday)
Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.

View Replies !   View Related
Sharepoint Integration Microsoft.ReportingServices.SharePoint.UI.WebParts.dll
Does anyone know the location of Microsoft.ReportingServices.SharePoint.UI.WebParts.dll after you install the web part?  I am wondering if I can inherit from this web part to wrap some other functionality into it. I know that i see it in the GAC buyt can't seem to find it so I can look at the assembly using reflector.

Also,  how easy is it to pass in parameters to the Report Viewer?

Thanks!

Erik

 

 

View Replies !   View Related
Problems With SharePoint Integration And Reporting On SharePoint With SSRS
Since I wasn't getting any replies with answers or suggestions to my initial posting, I figured I would continue to list the problems and solutions I've encountered while trying to setup and utilize SharePoint integration mode, and read data from SharePoint with SSRS reports (developed in VS2005). Hopefully this is helpful to anyone that is also trying to learn all this stuff and figure it out. 
 
(Single server - W2K3R2_SP2 as: DC / SQL2005 Standard / MOSS2007 Enterprise - see the bottom of this posting for the steps I took to set that up)
 
Note: After I get this to work, I intend to post my experiences for setting up SharePoint Integration with SQL and SP on separate boxes.
 
1. In the new reporting section under SharePoint application management in the Central Admin Console, the Grant database AND Set Server Defaults work when using the domain admin account, but not when using the SQLSPS service account I used to setup both SQL and SharePoint. Why can't I use the service account? Is the domain admin account now going to be used as a service account for accessing the sharepoint database?
 

This question remains unanswered as of yet.
 
2. Reporting Services configuration tool shows Sharepoint integration as red x when I create a new database in integration mode. (the service account SQLSPS and the Server object are members of the group WSS_WPG). If I switch back to native mode (the original reportserver database), the red x goes to a blue exclamation instead. Using Service Credentials or Windows credentials (as either the service account SQLSPS or as domain admin) doesn't change anything.
 

RESOLVED by using a domain user account for the ReportServer WebApp. Even though everything is installed on the same machine, it seems using the default of NETWORK SERVICE wasn't acceptable. Possibly because SharePoint is configured to allow additional machines in the farm?
 
3. http://servername:8080/reportserver will display Report Server information as it did before the integration on port 80, but  http://servername/reportserver doesn't display anything through SharePoint.
 

Resolved. I needed to define a repository before I could view it (go figure). When I created a sharepoint site based on a report center template, created a data connection library and report library, and then referenced that by http://servername/sites/myReportCenterSite/myReportLibrary, I was able to see the equivalent of http://servername/reports (which is now disabled due to integration mode).
 
4. Unable to deploy to new report center sharepoint site I mention above using Visual Studio. I kept getting "A connection could not be made to the report server http://servername/sites/ReportCenter. Server was unable to process request. The request failed with HTTP status 503: Service unavailable (System.Web.Services). In Visual Studio, the project properties were: TargetDataSourceFolder = http://servername/sites/myReportCenterSite/myDataConnectionLibrary, TargetReportFolder = http://servername/sites/myReportCenterSite/myReportLibrary, TargetServerURL = http://servername/sites/myReportCenterSite/
 

Found these errors in the event logs on the server.
 
Event Type: Warning
Event Source: W3SVC
Event ID: 1021
Description:
The identity of application pool, 'ReportServer' is invalid.  If it remains invalid when the first request for the application pool is processed, the application pool will be disabled.  The data field contains the error number.
 
Event Type: Warning
Event Source: W3SVC
Event ID: 1057
Description:
The identity of application pool 'ReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool.  Therefore, the application pool has been disabled.
 
Event Type: Error
Event Source: W3SVC
Event ID: 1059
Description:
A failure was encountered while launching the process serving application pool 'ReportServer'. The application pool has been disabled.
 
RESOLVED the issue by changing the Application Pool used by reporting services from ReportServer to DefaultAppPool and changing the account DefaultAppPool used from NETWORK SERVICE to my service account SQLSPS. I have no idea why ReportServer is invalid, it was set to use the service account SQLSPS the same as DefaultAppPool is. Perhaps the reporting services add-on makes a change to invalidate this?
 
5. Attempting to read a SharePoint list using a SSRS report. This should be fun.
 
First, created a new custom list in the ReportCenter SharePoint sites I've been using and called it myCustomList. I added one item with a title of myCustomListItem1. I don't know if I need to do more than that for a simple test of reporting or not yet.
 
Second, configured new shared data source. Name = myCustomList1DataSource, type=XML, Connection String = http://servername/_vti_bin/lists.asmx (this URL is viewable in IE), Using Windows authenticaion.
 
Third, in VS2005 on a workstation in the domain, create new report item (report1.rdl). New dataset for report; Name=Report1DataSet, Data Source=myCustomList1DataSource (Shared), Command type = text, Query String = [See Queries and associated errors below]:
 
Initial query obtained from: Connecting SQL Reporting Services to a SharePoint List (David Wise)
http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx
 
Note: Queries when executing the dataset (which errors out) and when executing a query in CAML Viewer for the same information (which does work just fine) both initially return 401.1 and 401.2 errors (as viewed in Fiddler). Since the CAML query works, I have not tracked this down yet.
 

A. Original format from posting
 


<Query>   
<Method Namespace=http://schemas.microsoft.com/sharepoint/soap/ Name="GetListItems"/>
<SoapAction>
http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>
 

Parameter; Name => listName, Value => =myCustomList
 
Was NOT prompted to define the query parameters
 

- Gets this error: "An error occurred while setting the command text property of the data extension command. The XmlDP query is invalid. (Microsoft.ReportingServices.DataExtensions)."
 

- Advanced information from the error => 'http' is an unexpected token. The expected token is '"' or '''. Line 2, position 19. (System.Xml)
 
B. Changed: Placed the namespace URL in quotes (also tried with and without / in namespace after /soap)

<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>
 
Parameter; Name => listName, Value => =myCustomList  (also tried Value => myCustomList) (no equal sign)
 
Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList
 
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
 
- Advanced information: <faultstring>Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sharepoint/soap/GetListItems/.</faultstring>
 

C. Try the query string that seems to work for Stramit CAML Viewer to read myCustomList

Stramit CAML query:
     <Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
          <OrderBy>
               <FieldRef Name="ID" />
          </OrderBy>
     </Query>
 
Also tried
     <Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
     </Query>
 
And tried entering no query...
 
Parameter; Name => listName, Value => =myCustomList  (also tried Value => myCustomList) (no equal sign)
 
Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList
 
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
 


- Advanced information: <soap:Fault><soap:Code><soap:Value>soap:Receiver</soap:Value></soap:Code><soap:Reason><soap:Text xml:lang="en">Server was unable to process request. ---&gt; Data at the root level is invalid. Line 1, position 1.</soap:Text></soap:Reason><soap: Detail /></soap:Fault>
 
RESOLVED. Went back to the query used in B above, and mysteriously it works now. This could be the "funny things happen" symptom I've been reading about. We'll see if it continues to function.
 
Here's what I've learned so far on Issue #5:

1. XML is confusing. XmlDP is even more confusing...whatever that is. Apparently it's new to/with Reporting Services in SharePoint integration mode?
2. There is far too little documentation regarding XmlDP and querying SharePoint lists from SQL Reporting Services.
3. According to http://msdn2.microsoft.com/en-us/library/ms159741.aspx: You must use the generic query designer to create the query. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter tab on the Dataset dialog box.

Tried this query:

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems" />
<Parameters>
    <Parameter Name="listName">
       <DefaultValue>myCustomReport2</DefaultValue>
    </Parameter>
    <OrderBy>
      <FieldRef name="ows_Date" />
    </OrderBy>
</Parameters>
</Query>
I was still prompted for the listName and the list returned with ows_Date still out of order. So maybe that proves this statement is true?
<translation: why make it convenient when it's already [somewhat] functional?>
4. When building the dataset, anything I place in the "Query string" box has to be enclosed in <query></query> tags or it errors out. I am trying to understand how to translate the CAML viewer query (which does work but isn't wrapped in query tags) into something that fits between query tags, and it hasn't gone well so far.
<translation: To be at one with your reports Grasshopper, you must understand xml>
5. According to http://srv1/sites/ReportCenter/_vti_bin/lists.asmx, "GetListItems" operation is supported
<translation: you're getting warmer...>
6. According to the soap output of my site http://srv1/sites/ReportCenter/_vti_bin/lists.asmx?op=GetListItems, I am only allowed to specify the following parameters: listName, viewName, query, viewFields, rowLimit, queryOptions and webID.
<translation: that and $3.00 will get you a cup of coffee>
7. According to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) (query and queryOptions may not be working properly with SSRS).
<translation: that kind of sucks>
8. When this started mysteriously working again, I tested using the "/" at the end of the namespace (wouldn't work without) and the "=" sign in front of the parameter value as defined on the parameter tab of the dataset (turns out to be optional).
 
As long as I can query SharePoint and get my list information back, I'm going to consider this issue closed and move on to actually using this information in a report. I'm sure that won't work right either, but why stop now?!
 
6. Ok, now that I can read SharePoint list data with SSRS, I need to try and actually do something with it.
 

A. Begin by setting up new data to report on
 

1. Create a new custom list (myCustomList2) and add a test record. This list will simulate the data we intend to submit to SharePoint using InfoPath, so it has a number of custom/created columns in it
 

2. Modify the working information from #5 above; add a new shared data source (myCustomList2DataSource), tell the dataset to use the new shared data source, change the information set in the parameter tab (Name => listName, Value => =myCustomList2) and test the dataset.  Lucky me, it actually returned the record I entered on the first try.
 
B. Begin to build the report with the dataset returned

1. So, as I'm feeling good that it's actually reading the list in Step A without me threatening to destroy the machine, I look to the left in the Dataset results navigation pane in VS2005 and realize there are NO fields for me to choose to report on.
2. At this point, I have no clue what to do about getting this XML data into a report. Not that I have a large clue about SSRS otherwise, but I have at least been able to build SSRS reports when using a SQL database as the datasource. Fyi, http://msdn2.microsoft.com/en-us/library/ms159741.aspx states: "Reports can use data from XML documents and Web services, or embed XML in the query. There is no built-in support for retrieving XML documents from a SQL Server database.", in case anyone is trying to do that.... 
 
RESOLVED. Since the fields were not appearing automatically, I had started to add new fields to the dataset manually (if it doesn't work, beat it with a hammer, right?). I entered half a dozen or so fields, each one with the exact name as displayed in the query results, and then decided it would be fun to hit the refresh fields icon. As soon as I did that, all the fields shown in the query immediately appeared in the dataset and I was able to add them to a report. I don't know if I hit the refresh fields when working in issue #5, so I can't say if they are there now because of that, or because of adding some fields manually first, a combination of the two, or if it's just one of those mystery items I'm reading about with sharepoint reporting.
 
7. Rows are being returned even though they have NULL values (so far anyway), and most data is returning nicely for the bulk of the columns I created in the list I'm reporting on. Here are the current issues:
 

A. I have one column in my SharePoint list that is multi-lined text. If the text type is either rich text or enhanced rich text, the value returned in the query is formatted like this: "<div class=ExternalClassyadayada<div>My notes display here". If I set the column to be plain text, this doesn't happen. I will need to figure out how to filter that for my report.
 

RESOLVED. David Wise had some code listed at http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx that addressed this issue somewhat. I modified it to match my circumstances. Disclaimer: I'm not a programmer, so use this at your own risk.


function ExtractMultiLineText(myField as string) as string
  dim strBegin as string
  dim strEnd as string
  dim myBegin as integer
  dim myEnd as integer
  dim myTextLength as integer
  strBegin = "<div>"
  strEnd   = "</div>"
  if myField = nothing then return ""
  if myField = "" then return ""
  myBegin = Instr(myField, strBegin) + Len(strBegin)
  myEnd = Instr(myField, strEnd)
  myTextLength = myEnd - myBegin
 
  if myBegin < 1 then return myField
  return mid(myField, myBegin, myTextLength)
end function
 
B. I have two columns in the SharePoint list that are set as number values with 0 decimal places. When I query those however in SSRS, they return with the number plus like 12 zeroes behind the decimal. Not a major issue, but I'll put it on the list anyway.
 

RESOLVED. Used the Int() function; =Int(Fields!ows_myProblemNumber)
 
C. If I go to "/_vti_bin/lists.asmx?op=GetListItems", it appears from the sample soap output that I am allowed to ask GetListItems for is: listName, viewName, query, viewFields, rowLimit, queryOptions and webID.  As I mentioned in issue #5, according to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) and query and queryOptions may not be working properly with SSRS.
 
So, if I can't put anything other than this in my query string;

<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
and I only have a couple parameters to pass on top of that, then it appears to me that my options for filtering the amount of data returned from the SharePoint list is to figure out a way to do it with expressions in the parameters or in the report itself. Am I missing something? Is this really all I get to work with out-of-the-box?
 
 
More to come...
 
 
==================================
Installation steps taken to build the server VM:
==================================

(Using VirtualPC 2007)
1. Build OS (2003R2SP2) and promote to DC

1a. Create domain user service account SQLSPS
2. Prep for SQL

2a. Install ASP.NET, IIS (www) and .NET 2.0
3. Install SQL/SSRS 2005 Std 

3a. Verify SSRS works
3b. RS config tool shows SP integration as blue exclamation
3c. Use service account SQLSPS for anything requiring Windows credentials
4. Install SQL2005 SP2 

4a. Verify SSRS works 
4b. RS config tool shows SP integration as blue exclamation
5. Prep for SharePoint

5a. Install .NET 3.0
6. Install MOSS2007 Enterprise and configure 

6a. Webapps, SSP and anything else use svc acct SQLSPS
6b. Move Default website to port 8080 and re-activate (SharePoint stopped the site)
6c. Verify SSRS works on 8080
7. Install Reporting Services Add-in
8. Create new RS database in integration mode (with service credentials)

8a. RS config tool shows SP integration as red x
9. SP central admin /application management / manage integration settings

9a. reports server = http://srv1:8080/reportserver (since default port has been moved to 8080)
10. SP central admin /application management/grant database access

10a. using the FQDN, confirm servername and default instance - click OK
10b. Prompted for an account to use to retrieve information; svc acct SQLSPS did not work, domain admin did though
11. SP central admin / application management/set server defaults

11a. Accepted all defaults
12. Verify reportserver answers at http://srv1:8080/reportserver (via reportserver AppPool and DefaultAppPool)
13. Changed the application pool identity for the ReportServer AppPool from Network Service to the domain user service account I used for all SQL and SharePoint configurations (SQLSPS) and rebooted. The red x for sharepoint integration in the RS configuration tool is now a green checkmark.

View Replies !   View Related
File System Task - Dynamic Source File Name
 

Hi All,
 
I have a source files folder where the files generated everyday.
My goal is pick the latest file and copy this single file to another folder.
I used the Foreach loop container and got the latest file and stored the file name to a varible i.e. LatestFile
Then i want to use the File System Task to copy this to the destination.
On the beginning, I could not setup the Latestfile since I don't its name then, so when I setup the Source Connection property of the File system task, it is not allowed to leave the SourceVarible as blank!
 
Any suggestion?
 
 
Thanks
 
Micor

View Replies !   View Related
Move And Rename File With File System Task
Hello

I want to move and rename a file and embed the date/time into it, so that each time the package runs a new file is created. For example MyFile_20060712_150000.doc.

Can someone give me a hint how to do this with the File Systen Task SSIS Control Flow Item?

Thanks for an early reply

Regards

Chaepp

View Replies !   View Related
Rename File Using File System Task Editor
Could someone please instruct me on how to use the File System Task Editor to rename a file?  I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved