Executing SQL Statement From Flat File
Jul 20, 2007
I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.
I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.
I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.
Any help would be greatly appreicated. Consider me an SSIS novice.
Thanks
View 4 Replies
ADVERTISEMENT
Aug 24, 2007
Hi,
I am testing SSIS and have created a Flat File Destination. I defined the Flat File Connection as New for the first time and it worked fine. Now, I would like to go back and modify the Flat File Connection in the Flat File Destination Editor, but it allows only to create a New connection rather allowing me to edit the existing one. For testing, I can go back and create a new connection, but if my connection had 50-100 columns then it would be an issue to re-create it from scratch.
Did someone else faced this issue?
Thanks,
AQ
View 1 Replies
View Related
Dec 27, 2006
Hi,
I have a situation where a tab limited text file is used to populate a sql server table.
The tab limited text file comes from a third party vendor. There are fixed number of columns we need to export to the sql server table. However the third party may add colums in the text file. Whenenver the text file has an added column (which we dont need to import) the build fails since the flat file connection manager does not create the metadata for it again. The problem goes away where I press the button "Reset Columns" since it builds the metadata then. Since we need to build the tables everyday we cannot automate it using SSIS because the metadata does not change automatically. Is there a way out in SSIS?
View 5 Replies
View Related
May 11, 2006
I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?
Any inputs will be appreciated.
M.Shah
View 3 Replies
View Related
Apr 3, 2015
I am unable to load data from flat file to sql table using bulk insert sql statement
My code:-
DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'
[Code] .....
View 1 Replies
View Related
Nov 10, 2006
Hi all,
I m using SSIS and i am transfering the data from Flat File Source to the OLE DB destination File. The source file contain some corrupt data which i am transfering to the other Flat file destination file.
Debugging is succesful but i am not getting any error output in the Flat file destination file.
i had done exactly which is written in the msdn tutorial of SSIS.
Plz tell me why i am not getting the error output in the destination flat file?
thanx
View 1 Replies
View Related
Feb 11, 2008
First, a couple of important bits of information. Until last week, I had never touched SISS, and therefore, I know very little about it. I just never had the need to use it...until now. I was able to convert my first 3 flat files to SQL2005 tables by right clicking on "SISS Package" and choosing "SISS Import and Export Wizard". That is the extent of my knowledge! So please, please, please be patient with me and be as descriptive as possible.
I thought I could attach some sample files to this post, but it doesn't look like I can. I'll just paste the information below in two separate code boxes. The first code box is the flat file specifications and the second one is a sample single line flat file similar to what I'm dealing with (the real flat file is over 2 gigs).
My questions are below the sample files.
Code Snippet
Record Length 400
Positions Length FieldName
Record Type 01
1,2 L=2 Record Type (Always "01")
3,12 L=10 Site Name
13,19 L=7 Account Number
20,29 L=10 Sub Account
30,35 L=6 Balance
36,37 L=1 Active
37,41 L=5 Filler
Record Type 02
1,2 L=2 Record Type (Always "02")
3,4 L=2 State
5,30 L=26 Address
31,41 L=11 Filler
Record Type 03
1,2 L=2 Record Type (Always "03")
3,6 L=4 Coder
7,20 L=14 Locator ID
21,22 L=2 Age
23,41 L=19 Filler
Record Type 04
1,2 L=2 Record Type (Always "04")
3,9 L=7 Process
10,19 L=10 Client
20,26 L=6 DOB
26,41 L=16 Filler
Record Type 05
1,2 L=2 Record Type (Always "05")
3,16 L=14 Guarantor
17,22 L=6 Guar Account
23,23 L=1 Active Guar
**There can be multiple 05 records, one for each Guarantor on the account**
and the single line flat file...
Code Snippet
01Site1 12345 0000098765 Y 02NY1155 12th Street 03ELL 0522071678 29 04TestingSmith,Paul071678 05Smith, Jane 445978N 05Smith, Julie 445989N 05Smith, Jenny 445915N 01Site2 12346 0000098766 N 02MN615 Woodland Ct 04InfoJones,Chris 012001 01Site3 12347 0000098767 Y 02IN89 Jade Street 03OWB 6429051282 25 04Screen New,Katie 879500
As you can see, each entry could have any number of records and multiples of some of the record types, with one exception, every entry must have a "01" record and can only have one "01" record. Oh, and each record has a length of 400.
I need to get this information into a SQL 2005 database so I can create a front end for accessing the data. Originally, I wanted one line for each account and have null values listed for entries that don't have a specific record. Now that I've looked at the data again, that doesn't look like a good idea. I think a better way to do it would be to create 5 different tables, one for each record type. However, records 2 through 5 don't have anything I can make a primary key. So here are my questions...
Is it possible to make 5 tables from this one file, one table for each of the record types?
If so, can I copy the Account number in record 01, position 13-19 in each of the subsequent record types (that way I could link the tables as needed)?
Can this be done using the SISS Import and Export Wizard to create the package? If not, I'm going to need some very basic step by step instructions on how to create the package.
Is SISS the best way to do this conversion or is there another program that would be better to use?
I know this is a huge question and I appreciate the help of anyone who boldly decides to help me! Thank you in advance and I welcome anyone's suggestions!
View 13 Replies
View Related
Mar 13, 2006
How can I take this example Flat file and parse out each section to a new flat file? Each section starts with HD (header row)
http://www.webfound.net/flat_file_example.txt
e.g. an example output file based on above (cutting out the first section) would be:
http://www.webfound.net/flatfile_output.txt
Also, I'll need to grab a certain value in each header row (certain position in the 100 byte header row) to use that as part of the filename that's outputed. I assume it would be better to insert these rows into a temp table then somehow do a search on a specific position in the row...but that's impossible? The other route is to insert each row into a temp table separated out by fields but that is going to be too combursome because we have several formats to determine separation of fields based on the row type so I'd have to create many temp tables and many components in SSIS when all we want to do is again:
1) output each group (broken by each header row) into it's own txt file
2) use a field in the header row as part of the name of the output txt file (e.g. look at the first row, whcih is a header row in flat_file_example. txt. I want to grab the text 'AR10' and use that as part of the filename that I create
Any suggestions on how to approach this whole process in SSIS...the simplest approach that will work ?
View 1 Replies
View Related
Nov 2, 2007
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Jul 5, 2001
Hello All!!
Below is a part of my code. I need to find out if I could execute the below statement.
select @string_sortorder = 'select max(sortorder) from geo_lineobj where pipeid = ' + str(@geo_pipeid)
select @string_sortorder
exec @string_sortorder @max_sortorder
When I select the @max_sortorder the value I'm getting is a null. The sortorder is an int datatype and the @geo_pipeid is also an int datatype.
I need to to get the @max_sortorder to continue my coding. Does anyone know why I'm getting a null @max_sortorder?
Any suggestion would be of great help.
Thank you
View 2 Replies
View Related
Mar 25, 2008
Here's a little script to find the currently executing statement. I put this together to get around a couple of limitations in DBCC INPUTBUFFER:
1) The 255 character output limit ... often I would get the "CREATE PROCEDURE" statement and the first couple of variable declarations, and that's it.
2) The fact that, in a multi-statement batch, you get the entire thing. This gives you the currently executing statement, not batch.
So, without further ado ...
/****************************************************/
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT
SET @SPID = <SPID to be examined>
SELECT @Handle = sql_handle
FROM master.dbo.sysprocesses
WHERE spid = @SPID
SELECT text
FROM ::fn_get_sql (@Handle)
/****************************************************/
Enjoy!
I geek, therefore I am
View 7 Replies
View Related
May 16, 2006
I have multiple SqlDataSources on a page. I only want them to execute the select query when the user performs a certain action. Is this possible? Or do they all execute as soon as the page is loaded?
View 1 Replies
View Related
Jul 23, 2005
Hi all,I was just wondering if it could be possible to excecute a statementwhich is extracted from variabele, Example:declare @SqlStatement varchar (100)set @SqlStatement = 'select * from company'the @SqlStatement contains the actual sql query, is it possible somehow to straight away convert it into excecution?I've tried:exec @SqlStatementbut exec or execute are meant to be for proceduresAny idea?
View 1 Replies
View Related
May 21, 2008
HELLO I'M NEW OF SQL SERVER. I'VE BEEN CHARGED BY MY COMPANY TO MOVE THE MDB WHICH WE WORKED TILL NOW TO SQL.
OBVIOUSLY I HAVE NOW TO CREATE THE DATABASE'S UPGRADING PROCEDURES WE WERE USING ON ACCESS.
MY PURPOSE IS TO MAKE RUN THE FOLLOWIG PROCEDURE, ONLY IF THE VALUE OF THE FIELD 'UPTGRD11' IS EQUAL AT '1', BELOW THE SQL STATEMENT I'M USING.
THIS OPTION IN ACCESS IS VERY EASY, BY MACRO OR BY VBA, I'M NOT ABEL TO DO IT IN T-SQL
IT DOESN'T RUN LIKE THAT
IF 'dbo.uptgrdt.uptgrd11' = '1'
BUT IT RUNS USING IS NOT NULL
IF 'dbo.uptgrdt.uptgrd11' IS NOT NULL
is there an easy way to do what i'm trying to?
View 3 Replies
View Related
Jun 23, 2015
I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file. I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the data from Source to Destination.
View 5 Replies
View Related
Jul 24, 2015
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
View 2 Replies
View Related
Apr 14, 2014
I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
[code]....
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.
View 4 Replies
View Related
Jan 31, 2008
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
View 7 Replies
View Related
May 13, 2008
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 10 Replies
View Related
Oct 24, 2007
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
View 5 Replies
View Related
Apr 6, 2015
I am running my package in sql server 2012, in which i am giving network path for flat file destination. And its working fine. But if i give m local path, its giving me error " cannot open data file" ...
Nothing is wrong with package.
View 10 Replies
View Related
Dec 27, 2007
Here's a really annoying problem. Let's say you have a text file with 2 million rows.Delimiters all look good and rows are previewed well but the file has a missing row at say lin 1234567 - way deep in the file. When SSIS encounters the blank row, an error is raised and processing on the file STOPS! I verified this in by checking the SSIS log and have even developed an error routine to notify me via email when the error occurs (really cool if I do say so myself ). The main problem still remains - how to resume processing from the point of failure in the file? Any help is appreciated. Thanks.
View 13 Replies
View Related
Mar 29, 2006
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)
insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @Event " & _
",[Year] = @Year " & _
",[Contract Loss] = @ConLoss " & _
",[Company Loss] = @CompLoss " & _
",[IndInsured Loss Prop] = @IndLossProp " & _
",[IndInsured Loss WC] = @IndLossWC " & _
",[Event Info] = @EventInfo", dbconn)
upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
View 6 Replies
View Related
Apr 3, 2007
Hi,
I have a flat file, comma-delimited, with strings in double-quotes.
In the connection manager for the file, I have specified that the Text Qualifier = ""
However, in the preview tab, it still shows the strings as surrounded by the quotes, e.g. "mycol1" whereas it should show mycol1 without the quotes.
Next, when I examine the data in the database after the load, it's messed up there also.
"mycol1" ends up in the database as "mycol1
"mycol2" ends up as "mycol2
This is not right.
I have format set to delimited, header row delimiter crlf, etc.
Any ideas?
Thanks
View 3 Replies
View Related
May 27, 2008
Hi,
I have a package A which is copied from another existing package B as most of the data structure and ETL mappings are same.
What I need to change in Package A is to change the file in Flat File Connection Manager. I can change it in the conneciton manager editor. However, it is automatically changed back to the previous one everytime when I try to Save All or run the package.
I also tried to copy/paste this Flat File Connection Manager in the same package but samething happen. The package file is not set 'Read Only" so anything else can Saved well except for the File name in connection manager.
Is this a bug? It would be very appreciated if anyone can give me any idea about this.
Thanks,
Jenny
View 7 Replies
View Related
Mar 29, 2006
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)
insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @Event " & _
",[Year] = @Year " & _
",[Contract Loss] = @ConLoss " & _
",[Company Loss] = @CompLoss " & _
",[IndInsured Loss Prop] = @IndLossProp " & _
",[IndInsured Loss WC] = @IndLossWC " & _
",[Event Info] = @EventInfo", dbconn)
upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
View 3 Replies
View Related
Aug 28, 2002
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);
MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1
UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence
RETURN @sequence_id
END
View 1 Replies
View Related
Sep 14, 2006
Hi,
I have a task to traverse a folder of CSV files of same format and then populate into one sql server table.
Is there a way where I can change the source CSV file name runtime using FOR EACH loop container for flat file connection manager ?
any help would be much appriciated.
Thanks,
Furrukh Baig
View 5 Replies
View Related
May 29, 2008
Hi,
I'm attempting to use a variable to define the file name in my flat file connection (for error files in my SSIS package).
I have it defined as follows:
@[User::ValidRootFolder]ConditionConditionSourceError.csv
However it throws an error 'the filename, directory name, or volume label syntax is incorrect'.
If I put the value of the variable in explicitly it works, so I know my variable name is correct - am I just not calling the variable properly?
Thanks
View 11 Replies
View Related
Mar 28, 2006
Hi,
we have one requirement to run the package daily basis.
The package should run at specific time on that day.
we are using windows schedular for that.
we will have one new flatfile everyday.
Is there any process to attach this file to flat file source dynamically?
The requirement is,
The flat file should be able to read the new flatfile everyday.
we have no option change it manually, the flatfile source should have to take the file automatically at that time.
So that it can take that flatfile and load it into database table.
View 1 Replies
View Related
May 22, 2008
Hi All,
We have a SSIS package which is accessing a remote Windows file share location.
The package first moves the file from folder-1 to folder-2 and also renames the file during this process.
Then the package reads the file (using a flat file connection FF_SRC) from folder-2 and renames it again after processing it successfully.
The permissions given to the user executing the package on folder-2 are: Read+Write+Modify+List folder contents.
We are facing an error:
Code SnippetFile or directory "Z:folder-2XYZ.txt" represented by connection "FF_SRC" does not exist.
We are getting the above error when the SSIS package is trying to rename the file the second time in folder-2.
However, the file exists in folder-2.
The OS is Windows 2000 Server SP4.
Any ideas why this could be happening and how it could be resolved?
Best Regards,
Avnip
View 8 Replies
View Related
Feb 14, 2007
I am wondering how easy is to check for file locks and have our SSIS Package to wait until file has been release by the process which is using it.
Also, same question when we're writing to a Flat File (or Flat File Destination).
Thanks,
View 3 Replies
View Related
