I have a folder that gets 2 to 3 files dropped everyday. I want to create a package that loads only the new files into the database. The old files (Processed files) remain in the same folder and cannot be moved. How do i accomplish this in ssis?
I found something about the file watcher task with the for loop, but just can't put it all together.
Any help would be appreciated.
Thanks
Can't an SSIS package run "in the background", so to speak, without having either the cmd.exe or dtexecui windows open while executing? I'd obviously rather not have to have a window open when the thing is running right?
View 2 Replies View RelatedI need an example of a WQL query to use in the WMI Event Watcher task to watch for the presence of a file. The task has to succeed for either:
1) The file already exists when the task is run
2) The file shows up while the task is running (waiting)
Also the query has to reference a UNC path and file name. The file I'm looking for is just a flag file telling me a table is refreshed in our Data Warehouse.
I also need pointers on how to set the WMI connection manager. For the server, do I use the \servername of the fileserver? Do I need to include the folder the files are in (eg. \servernameflags)?
Thanks!
Kory
hi frnds,
i want to view the runtime variable value in the watch window while the package is running,
how i will get that watch window. i searched all the menus but i didnt get it.
im using Microsoft Visual Studio 2005 for SSIS package.
I honestly don't see where to view the watch window. Can someone please advise.
Thanks
All,
I cannot use the watch window while debugging. I want to use it to look at the runtime variable values. The watch window is blank when I click on it, could someone please tell me where and how to configure it?
Many thanks
Hi,
I am trying to have my old DTS packages working on SSIS. The first basic package I have, simply copied all tables/views and sp from one database to another one, quite like a backup/restore.
I converted this DTS package onto dtsx, tried to run it in VS2005, and it appears that the package just bcp from source to destination without dropping the dest table first.
I have checked that the parameter dropObjectsFirst was set to 'true' and ExistingData was set to 'replace'.
From the profiler, it seems that the data is appended each time in the table.
I am sure there's a small workaround to make that package working!
thank you in advance,
I need to create a file that removes (drops) all of your database objects.
View 12 Replies View RelatedHow to drop transaction log file and create another one
or change logical name of transaction log file?
Thank for any help
I am not able to to drop few file groups that has been created to add partition range.
Steps taken so far:
--Empty files started
DBCC ShrinkFile(YEAR2015_FG,EmptyFile);
GO
--Removing files
Alter DATABASE ETL_MART REMOVE FILEGROUP YEAR2015_FG;
GO
-- Remove parition scheme depednecy
[code]...
Hello,Using Enterprise Manager, I deleted from my database the only tablethat contained records (Right-Click on Table, Choose "Delete Table").My expectation was that the LARGE .mdf file would be reduced to minimalsize (at least as small as the Northwind MDF). However, it's still 4+Gig in size!! (Northwind is 2.62 MB). This is a problem, bc I deletedthe table in order to recapture hard drive space.NOTE: I already tried using Shrink Database in EM. This significantlyreduced the size of the .mdf file from its original (i.e. pre-delete)size of 38 Gig to present size of 4 Gig.What can I do to further reduce the size of the MDF file?Thank you.
View 1 Replies View RelatedIs there a way i can see the value of the variables during debugging ?
View 10 Replies View Relatedbetween my wrox book, google and looking everywhere in BIDS, I couldn't figure out how to watch the variables being set in an Exec SQL Task control flow item. My wrox book said something about right clicking the var in the script (I wasnt in a script), or using the debug menu or pressing ctrl-alt-q. None of this worked. Is it possible that at installation a certain choice wasnt made, or maybe watch windows only work in the context of something other than the control flow?
View 9 Replies View RelatedI have a T-SQL Statement Task to create table every time the package run. It doesn't return error but it doesn't drop the table either. The data is appended every time. The code is working fine in SQL server query window.
IF OBJECT_ID (N'M020_Vendor', N'U') IS NOT NULL
DROP TABLE M020_Vendor
GO
SET ANSI_NULLS ON
GO
[code]....
Hi all,
i have a requirement that user only sees drop down list and a button. when he selects an item from drop down and click button then i need to load rdl dynamically depending on the selection from the DDL.
i have created 3 rdl files, each one contains different content. I need to show the user, the selected rdl from the drop down.
is it possible in SSRS?
if you didn't understand well, feel free to ask.
please help me out.
thanks
-Praveen.
Hi Guys,
I've got an order generation query that will _sometimes_ run long. By that I mean every few days a query used to estimate the required stock quantities for an order will time-out because it has taken an extended time to run. This only occurs at one particular site. This site shares a common schema with others who do not experience this issue, although site is a particularly busy one.
This query (a stored proc call) needs to aggregate sales data from ~200k rows and usually takes 10-15 seconds. On the days when it 'runs long' it will hit the 5 minute time-out period and be failed by the application.
The problem I have with this process is that if I have the application dump out the query it's using to generate the order (this query will vary as the stored proc is passed the name of a temp table to use for this order) so I can run it in Query Analyzer to get an execution plan and maybe some clue as to why it takes so long; the issue will vanish. After I've tried to investigate in this way an order generated from either Query Analyzer or the application will work in the normal time. When the query is running long any processes which attempts to write to the sales data tables will get blocked until the order generation has finished.
The application is accessing the database via ADO 2.6. The database itself is running on SQL Server 2000 sp3 under Win2k.
I believe that the SQL Profiler is the tool best suited to investigate this but I'm hoping for some advice on which of the myriad of watchable metrics I should be checking here.
Any profiler advice or estimates as to what the issue may be here would be great!
-Karl.
Not sure if this is a "user training" issue or a bug, I seem to have difficulty dragging and dropping variable(s) to the watch window. Is there a specific sequence I need to follow to enable this feature in BIDS? Appreciate any feedback.
View 5 Replies View Relatedhi frnds,
i have one SSIS package with single SQL Execute task.
select empname from emp
This is the query i had mentioned in that SQL task.
i had created the varibale User:rs as object and i mapped that to the resutset and i selected fullresultset in general tab.
In the debugging mode im getting the com object as the result of that variable.
This is the o/p which i got in the debugging mode.
- User::rs {System.__ComObject} __ComObject
Type __ComObject
Value System.__ComObject
but i requires the empname to be displayed in that mode.
how to get that?
pls tell me .
I am splitting data from SQL table and sending it to excel file but everytime i rerun the package ,it appends the existing data in excel file ..I tried using execute sql task with excel connection and write drop table `tablename` and then one more execute sql task with create table `tablename` (`Id` int ,`fname` varchar(100)) ....But it does not seem to work.
View 1 Replies View RelatedI have an SSIS package created on SQL Server 2005. I have moved this to a SQL Server 2008 R2 server and amended the package on this new server to point at the correct databases.
The package runs manually. However, I cannot see the package when trying to schedule a job. The dropdown list does not contain the package.
I imported the package by right clicking on MSDB and selecting the package from the file system. The package then appears under this folder (SQL Server Integration Services). I then create the SQL job but cannot see the package I just created.
I'm writing my first SSIS custom task. I have added Public properties, which appear in the standard task properties window, and to one of them I have added an EditorAttribute as follows:
<Category("General"), _
Browsable(True), _
EditorAttribute(GetType(UIFileNameEditor), _
GetType(System.Drawing.Design.UITypeEditor))> _
Public Property FilesToArchive() As String
[Code] ....
When I select the FilesToArchive property in the Properties window, I get an ellipsis that appears in the property value. When I click on the ellipsis, it brings up the FolderBrowserDialog, which is defined in the UIFileNameEditor class. This all works fine.
What I want to do is to bring up the EditorUI, the one you get when you double click on the task, and also populate that with properties which can be edited. I have a class which inherits DTSBaseTaskUI, which is displayed when I double click on the task.
I can also get properties to be displayed in that UI but I cannot get them to be editable in that UI, using the same technique as in the standard Properties window, as described above.
Hi all.
First, sorry about my poor english.
I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
View 11 Replies View RelatedCan we drag and drop Existing SSIS package into for each loop container ?
View 3 Replies View RelatedI am writing my first custom SSIS task and I can see that, if I put a public property into the task, I see that property in the standard Properties window. If I add a property of type String, I can put a value in that property, in the task code, and when I instantiate the task in a package, I can see the value I entered.
To try and get a drop down list property in the Properties window, I declared a property of type Combobox, and indeed a drop down list appears in that property.
My problem is trying to get values in that property. I have used the test items:
Property.Items.add("Fred")
Property.Items.Add("Jim")
But I do not see the values in the drop down list. All I do see is one item with a value of "(none)".
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedHello 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
}
I want to enumerate all *.xls and *.csv file. How to fill the Files box? I tried
*.xls, *.csv
*.xls *.csv
*.(xls|cvs)
all doesn't work
Hi all,
In a foreachloop, I am inserting records into a flat file which is working fine. But the thing is that as the file grows, it takes longer for it to locate the EOF(End of File) of the flat file so as to insert the records.
I have around 70-100 lines written to the file at each loop and there are more than 20k records to be looped. wihich means that at the end I should be having 1400k - 20000k line in the text file.
One solution would be to insert the records at the start of the file itself so that it does not has to lookup the EOF each time before writting.
Another would be to generate separate files and then merge it.
Any idea how can this can be done?
Beside this I have to zip the file and then SFTP to a given address.
Any suggestion or help would be welcome.
Rdgs
David
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
View 7 Replies View RelatedI need to build an asp.net/C# application to read values from an Excel spreadsheet. Once the values are read from the spreadsheet, the C# code will do some elementary statistics on the values read. Then the values read and their computations will be written to a sql server database.
My manager suggested that SSIS might be a good candidate technology for doing this type of work. Does that sound correct? My only hesitation with using SSIS is that I want to keep the application as simple as possible, so that the code can be more portable. Maybe might argument is not a good one, but maybe someone can help me out here.
Ralph
Dear Friends,
I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database.
How you think is the better way to do that?
Using a FlatFileSource to read the file and a script to save the value into a SSIS variable?
Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable.
How the best way you suggest?!
Regards!!
Thanks.