Generate File Task With 2 Files
Sep 18, 2007
Hi Guys,
I've one Dafta flow task where I'm getting data from OleDb source and then doing some scripting using script component and then generating a file.
Now I would need to get the same data and apply some different things and generate another file.
Can I used this same task for doing the secondry work? If yes how woulld I put the thing in place, I would need to get the same data but I would need to use a seperate scripting and generate a seperate file?
TA
Gemma
View 1 Replies
ADVERTISEMENT
Oct 1, 2007
Hello,
I would like programmatically generate rdl files with image.
I am using this XML Text Writer to create the rdl file.
But i have no idea how can i carryon on this task.
Can anyone help me in this topic?
Thanks.
View 4 Replies
View Related
Feb 26, 2008
I want to rename files in a folder using filesystem task. My files are like these:
C:FOLDERDatabase20080225.bak
C:FOLDERDatabase120080225.bak etc
I want to remove this datepart and put in a C:FOLDERDatabase.bak file.
I am not quite sure about the string functions usage here...I think I have to map the destination variable to a string function output.
Any other ways to do this job ?
Thnaks for your help,
-Gish
View 2 Replies
View Related
Jan 25, 2007
Hi All,
I don't know if anyone faced this issue. We are having a strange problem. Our process was working well when it was implemented on 32 bit processor.IT ran perfectly for 6 months with out a problem. But when we moved the packages to a 64 bit machine, this issue along with some other issues started to show up.
The issue is we are missing files in the source folder.
Our process is designed such that a source process, brings in a file and updates a status for the file in a audit table. The ETL process picks up the file, then assigns the status as €˜running€™ when SRC process is complete and loads into Target DB, and updates ETL status to complete. But current problem is the ETL is losing files after it assigns the status as running. When we looked into the DB weather the data is loaded, we could not find any data related to these files.
we are have mapping level parameters for source path and target path.
We are using a For Each Loop task, and processing files(which are simple flat files) in the source path. The file name is stored in the mapping level parameter. Once the file is process we are moving them into a target path.
Our src and target file paths are on the same drive, just have src folder, inside src folder we have processed folder and failed folder. So files are picked from the source folder and moved into processed folder after processing. The files are not even moved to a failed folder.
There are lot other processing going on this box, and the trend observed is that when more processors are running at peak hour, the missing files€™ count is more.
Right now we are refetching those files, as a work around, but does any one has any suggestion why this is happening or any better implementation suggestions?
Thanks
View 1 Replies
View Related
Jan 16, 2008
I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.
When I try and remove the old files with a clean up task I get an error and the files don't get deleted.
The version is as follows
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)
The error result is as follows,
Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'\\EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
system cannot find the file specified.'". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
The maintenance plan seems to be adding extra "" though when i enter the
code directly in a query i get same error.
Query:
EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1BACKUPSABCD_BACKUPABC_DAILYABCD',N'trn',N'2008-01-13T12:52:49'
Error:
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.
Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.
Regards,
Scott
View 6 Replies
View Related
Dec 11, 2014
I have been strunggling to find solution to convert XLSX files with multiple sheets to csv file.
Requirements
>>Â Convert XLSX file with multiple sheets to CSV file
>>Â CSV file names : XLSX filename + '_' + sheet name
>>Â scirpt has to be in VB as i am using ssis 2005
>> I started develping scirpt using Micorosoft.office.interop.Excel.dll . this dll is referenced to script task.
>> found web link as useful.. [URL] ....
View 4 Replies
View Related
Apr 24, 2008
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 16 Replies
View Related
Jun 15, 2005
Hello All,Looking for an opinion on the following scenario.Lets say you need to create a report for a 1000 stores. The report format is the same but the data changes for each store.Option 1:Write an asp.net page where the user selects the store, or a store parameter is pased, hit SQL Server with a SP and display the data. - This option seem to have the advantage of flexibility and simplicity.Option 2:Write out a static XML/HTML report for each store and have the asp.net page load the static file. - This options seems to have the advantage of speed on the client side but is less flexible and more complex on the backend.Any thoughts?For option 2 is it possible to write out the XML/HTML files using SQL Server or does something custom need to be created?Thanks, Gary
View 2 Replies
View Related
Sep 16, 2003
This script generates a format file for every table in a database.
It's set up for fixed width files, not too common in this world...but they can easily be modified.
Also the do not at this time handle text or image columns.
Any hints/advice here would be great.
I'll post it when I figure it out.
happy bcping..
SELECT FORMAT_CARD FROM (
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ char(9)+'"
"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
Brett
8-)
SELECT @@POST=NewId()
That's correct! It's an AlphaNumeric!
View 1 Replies
View Related
Aug 17, 2006
Our organization would like to add tasks to users' Exchange accounts from our SQL Server using a USP. Basically, we are looking for the same functionality as the xp_sendmail syntax provides, but instead of sending an email to a user, we would like to create a task in the user's Exchange Tasks folder based on the information passed from our database via the USP.
Here is an example:
A client must receive paperwork every 6 months based on a date stored in our SQL database.
2 weeks prior to the date the paperwork is due, a USP would detect that John Doe has upcoming paperwork needed.
Bob is John Doe's sales rep. The USP would create a new task in Bob's Exchange Tasks folder indicating that John Doe's paperwork is due on such-and-such a date, setting reminders, etc.
We are currently running SQL 2000 and Exchange 2003 in an Active Directory environment. Any help or pointers would be greatly appreciated!!
Thank you - Jeremy
View 1 Replies
View Related
Apr 24, 2007
Hello all,
I'm new to SSIS and have a question about the best way to generate multiple Excel files with my current package design. I have a stored procedure that I run from an Execute SQL Task in a foreach loop container, and it generates results as appropriate for each of the parameters it loops through.
What's the best way to take the result set from each of those executions and generate an Excel file from each set? How do I map that result set variable to be the input for creating a new Excel file? Is this best done as a script task somehow?
Should I not be using SSIS for this task? I thought I would just create a package and schedule it to run daily with SQL Agent, and it would autogenerate the Excel files as needed.
Thanks for your advice!
View 6 Replies
View Related
May 22, 2008
Hi,
There is one manual process done by my friend. The process is " In query analyser, she runs a sql query and the resultset of Sql query, she copies and pastes into a excel workbook".
I wanted to write a SSIS package to automate it and providing a UI, in which she can give connection details and paste the SQL query and the package will in turn generate excel workbook in the target path". I thought of trying ExecuteSQL task, but the output resultset of ExecuteSQL task cannot be mapped dynamically to excel source(correct me if I am wrong). If I use Dataflow task, then also whether dynamic mapping of SQL output to Excel destination input is possible?
Can you please tell me the best approach for achieving the above functionality?
Thanks,
Venkataraman R
View 5 Replies
View Related
May 16, 2008
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
View 7 Replies
View Related
Apr 7, 2008
Hi All,
I am using a Data Flow task which copies data from an Excel Source to a SQL Database Table Destination. From 15 columns I require only 10 columns to be imported to the DB Table. So I have mapped those colums. In SQL DB there is a colum called say X, whose value should be the "Remedy" for all the columns which are imported. Is there any task that can achieve it.
Please Help.
View 10 Replies
View Related
Oct 22, 2015
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
View 0 Replies
View Related
Jan 21, 2006
Is there a way to do this with some sort of task ?
View 7 Replies
View Related
Nov 28, 2006
Hi
in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").
We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.
How can we achieve that with a vb.net data script task ? (Or anything else ?)
best regards
Thibaut
View 9 Replies
View Related
Jun 15, 2015
I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only). Â So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.
Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.
View 2 Replies
View Related
Mar 15, 2007
I cannot set a breakpoint in a Script task and have it complete successfully. I am running Vista 32-bit and only the workstation tools for SQL Server 2005 SP2. The steps to recreate are:
1) Create a new SSIS project/package.
2) Add a Script Task.
3) Set a breakpoint in the Script Task.
4) Run the package.
When I remove the breakpoint, the script task succeeds. When I put it back the task fails. The execution results say "Error: The script files failed to load." I completely uninstalled all SQL Server 2005-related entries using the Programs and Features control panel, rebooted, reinstalled the Workstation Tools, then applied a freshly-downloaded SQL Server 2005 SP2 and rebooted. If I change "RecompileScriptIntoBinaryCode" to false the script fails whether there's a breakpoint or not. If it's true (the default), the script only fails when a breakpoint is set. I would like to be able to set a breakpoint to assist in debugging the package. For the time being, I can move the package to a different (non-Vista) OS, where it works fine, but I would like to be able to develop and debug on Vista.
View 4 Replies
View Related
Jul 15, 2015
We have the following requirement :
1. In daily basis auto loop through each item in the invoice table.
2. passing invoice number into a Summary SSRS report as parameter.
3. Auto download ALL generated PDF reports into a window folder with a special file name format i.e. <INVOICE_NO>_<DATE>.pdf
how to achieve this via SSRS, Store Procedure or Power Shell?
View 4 Replies
View Related
Mar 13, 2008
In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
View 3 Replies
View Related
Mar 28, 2007
In the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:
Column Name 1 : Column Value
Column Name 2: Column Value and so on
I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.
Code for Writing the Full Result Set into a Text File
Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString
Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf
oLogFile.WriteAllText("C:MyFile.txt", strValue)
oLogFile.WriteAllText("C:MyFile.txt", rsSQLOutput)
I went through this link that explains how to write XML Result Set into a File, But this doesn't help as it writes in XML format.
Would you please give me a hint of code how I can go upon.
View 7 Replies
View Related
May 2, 2006
hi all,
how to generate txt file from sql table. I want text file with respective columns without any seperator between columns. and if for an example a column of int having 5 digits but i want it in text file with 10 digits with remaining all as blanks. guide me how to do this.
thnks in adv
View 6 Replies
View Related
Apr 28, 2008
This is my first time on sqlteam forum so a big hello to everyone!!
Firstly I am very noive user of sql to say the least, but i have be requested to create a .txt file
Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far, like i said i am a very novice user so try to help me and not condem me for my lack of knowledge!
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not to how to do that and how to generate it in a txt file?!
Keith
View 5 Replies
View Related
Dec 6, 2007
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 11 Replies
View Related
Nov 7, 2007
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
View 5 Replies
View Related
Dec 20, 2001
Hi everybody
EM has ability to Generate SQL Scripts to file(S)
I traced it with profiler but can not see how Sql server output objects to file(s)
I want to run DTS and sript all procedures to files every night
I don't want to use
xp_cmdshell 'osql -UTheUser myPass-P -Qsp_helptext 'Mystproc -oc:est.txt'
Could it be done another way ?
View 3 Replies
View Related
Apr 23, 2003
i want to generate a sql script file along with the data
how to generate it?
View 12 Replies
View Related
May 22, 2006
I am a Newbie to programming and databases and would like to create a simple program for doing full text searching on a SQL database.
I have downloaded and installed Visual Basic 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express. I have watched several hours of video tutorials and done numerous tutorials. I feel I am making progress and having a lot of fun. My machine is running XP home edition.
I have found the following resource that looks like a good place for me to start with creating a full text search program:
€œSQL Server 2005 Books Online
Item Finder Sample
http://msdn2.microsoft.com/en-us/library/ms160844.aspx
However I am stuck at the beginning with trying to €œgenerate a strong name key file.€? I don€™t know how to €œopen a command prompt€? (I did confess to being a Newbie!!). I searched the database for this forum and found a relevant thread stating that €œSQLCMD is the command-line utility for SQL Server and is included with SQL Server 2005 Express Edition.€? I found the SQLCMD program on my computer but when I opened it, it does appear to provide the options listed below in the tutorial (i.e., €œclick start,€? €œPoint to all programs,€? etc.).
Initially here is what I need based on the first section of the tutorial:
Building the Sample
If you have not already created a strong name key file, generate the key file using the following instructions.
To generate a strong name key file
Open a Microsoft Visual Studio 2005 command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
-- or --
Open a Microsoft .NET Framework command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.
Note:
To determine the folder where samples are located, click theStartbutton, point toAll Programs, point toMicrosoft SQL Server 2005, point toDocumentation and Tutorials, and then clickSamples Directory. If the default installation location was used, the samples are located in <system_drive>:Program FilesMicrosoft SQL Server90Samples.
At the command prompt, run the following command to generate the key file:
sn -k SampleKey.snk
Important:
For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
How do I complete this initial step of generating a strong name key file for the tutorial? I guess I am missing something simple and once I get past this I will be able to complete the tutorial. Any help will be greatly appreciated.
View 7 Replies
View Related
May 23, 2008
hi i was trying to generate a .xml file by using this command below
DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)
SET @Table ='Authors'
SELECT TOP 1 @Filename = au_fname FROM Authors
SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'
SELECT @FileName
DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)
SET @Table ='Authors'
SELECT TOP 1 @Filename = au_fname FROM Authors
SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'
SELECT @FileName
SET @SQL = 'EXEC master..xp_cmdshell '+ ''''+'bcp'+ ' "SELECT ''au_fname '' AS ''Name/First'', au_lname AS ''Name/Last '' FROM pubs.dbo.authors ORDER BY au_lname FOR XML PATH (''Books''), ROOT(''Authors''), TYPE, ELEMENTS " queryout '+ @FileName + ' -c -SlocalHost -Usa -P'+ ''' '
SELECT @SQL
EXEC(@SQL)
i got an error saying
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'au_fname'.
i could no tfigure it out my mind window is closed.can any one help me put to find a bug.
many many thanks
View 5 Replies
View Related
Jul 10, 2006
hi there.
i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?
generate xml and cache it and use the xml file rather going to sql server database?
has anybody have any help on this?
the steps invloved:
1) first generate a xml file from table something like this:
select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?
which one should i use and how do i access after i gnerate a xml file
thanks.
View 5 Replies
View Related
Oct 20, 2012
I want to create an excel file with .xls by using VB script.
Because i want to create a DTS package for our requirement. DTS accepts excel files which has .xls extension only.
View 1 Replies
View Related
Jul 20, 2005
I need to develop some crystal reports on some .NET ado datasets.This is easy to do if I actually had data to work with. It is mucheaser creating reports with you have data.Therefore, I would like to run the stored procedure that the .NET adodataset is built on and generate an XML file. I could run the projectand try to figure out where the developers are making the call to thestored procedure and insert a line to writetoxmlfile. I would rathernot have to mess with their code.Is there a way working with SQL Server (either query analyzer orenterprise manager, dts, or whatever) that I can generate an xml file.I see that I can run a stored procedure and get an xml style return inquery analyzer, but I don't know how to save that as an actual file.Thanks for the help.Tony
View 3 Replies
View Related
