Any suggestions on a means to capture a pipe delimited flat file that contains different record types, each with their own layout...
Type1|field1|field2|field3
Type2|field 1
Type3|field 1|field2
Type1|field1|field2|field3...etc
Thanks!
My question is this.....
I inherited an application that creates reports and each report pulls data from a SQL database. The reports are for each School district in the state and pull the number of students that have Asthma conditions, simple enough. However what happens is this... Each initial report is saved with a code of 2 for initial. As long as there aren't and revisions I am fine. If a school makes a revision (Code of 4) I now have two records in the database and I need the one from the revision (Code 4). Not every record will have a revision, and sometimes there may be multiple revisions. How can I code my stored procedure to go through the database and check to see if there is a code of 4 or multiple codes of 4 and if so grab that data, if not grab the initial data? Below is the code currently being used. Frank
CREATE PROCEDURE dbo.usp_Rpt_Asthma
(@WhereClause VARCHAR(400))
AS
SET NOCOUNT ON
DECLARE @WhereClause_STR VARCHAR(400)
SELECT @WhereClause_STR = CONVERT(VARCHAR(400),@WhereClause)
Exec ('Select D.Code, C.Code, InstCtgyFK, C.[Description] as County, D.DistrictName, A.InstName,
Sum(K4+K+G1+G2+G3+G4+G5+G6+G7+G8+G9+G10+G11+G12+UnGrSpEd+Other) as TotalADM, Asthma, Asthmatics
FROM dbo.tblAnnualReports AR
Inner Join dbo.tblAddresses A on A.InstitutionFK = AR.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblInstitution] I on A.InstitutionFK = InstitutionPK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblActiveInstCtgy] AIC on A.InstitutionFK = AIC.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCounties (PA Standard)] C on C.Code = I.DOHCountyCode
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCommunityHealthDistricts] D on D.Code = C.CommunityHealthDistrictCode
Inner Join dbo.tblAverageDailyMemberships ADM on AR.[ID] = ADM.AnnualReportID
Inner Join dbo.tblChronicConditionsInjuries CC on AR.[ID] = CC.AnnualReportID
Inner Join dbo.tblMedicationAdministration MA on AR.[ID] = MA.AnnualReportID
Where (A.StartDate <= AR.DOHDateProcessed and (A.EndDate >= AR.DOHDateProcessed or A.EndDate is Null or A.EndDate = ''1/1/1900''))
and ReportTypeCode = 2
and IndOrdStandOrd = ''I''
'
+ @WhereClause_STR +
'
Group By D.Code, C.Code, InstCtgyFK, C.[Description], D.DistrictName, A.InstName,
Asthma, Asthmatics
')
GO
Hi all,
I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.
Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.
It seems easy to output the record, but how do I store it as a variable !!
This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable....
Function higher_manager_ein() As System.Data.IDataReader
Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Can I declare variable and assign a user defined table name in Sql server 6.5. if so how can I do that ,
Thanks
Ali
Hey all!
Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?
Any thoughts?
In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?
Thanks for any suggestions!
Jim Work
I am using the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 2 datasources, ordering each on a field commmon to both.
I have created the two derived columns headers and am now moving on to the merge.
It is failing with the following error:
"the input is not sorted"
And whilst I definitely have an order by on the query, when I look at the metadata between the datasource and the derived column, the Sort Key Position items displays "0" for all my fields, I was expecting the sort field to have a "1" in this column. What am I missing?
Any help would be most appreciated!
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Can someone say how to declare a record like variable in MSSQL-2000 Like:
mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
Thanks, Vinnie
I am running a query in with my daily import job that lets me know if a duplicate record was imported into the system. If so I want to setup my email table to mail me the following information. My code works fine except the value of the variable @ID is only giving me the first record in the set. How do I pass it on to the next record? I get the right number of rows inserted into the email table but the @ID variable is the same for all 3.
Drop Table #SER1
Select id#,
Colx=Count(*)
Into #SER1
From Business
Group By id#
Having Count(id#) > 1
Declare @ID Varchar (4)
Select @ID = ID# from #SER1
INSERT INTO Email
( [To],
[Subject],
[message],
[Table],
TableUKey,
TableSource
)
SELECT
'Firstname.Lastname@Company.com',
'Duplicate ID# Imported',
'ID# ' + @ID + ' has been imported multiple times ' + ' on ' + cast( getdate() AS varchar(30)) + '. You need to remove the record and rerun the import.',
'Daily Import',
@ID, 'Daily Import'
FROM #SER1
If I script the SQL statement with a constant, deleting the record from the database works.
If I script the SQL statement to delete based on the WHERE clause being a variable name, it will not delete the record.
The value being compared in the WHERE clause comes directly from the Sequel database.
I have a dropdown box that is filled from the database. The dropdown1.selecteditem.text is placed in a variable. The script is to delete a record from the database where the table.name in the database equals the item name selected from the dropdown box.
This querystring does not delete the record from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'variableCompareText')"
This querystring does delete the recrod from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'John')"
Why can't I delete a record with a variable in the querystring? Otherwise, you would have to always know in advance which record to specify rather than being deleted dynamically.
Each user who inserts a new SQL record from the FormView control needs to have their UserID in one of the fields of the record. I have the user ID stored in the Session("UserID") variable. I am having trouble finding the right way to get this done. I have tried using a hidden text box but I can't seem to assign the value. I have tried the Insert Parameters but that will not accept <%# Session("UserID") %> as a DefaultValue. Any ideas would be helpful. Thanks.
View 5 Replies View Relatedhello,
I am trying to insert date and time into my table.
insert into <table_name> values('12/12/2006','12:23:04');
but it displays error at " ; "
can anyone help me to figure out the problem
Thanks a lot in advance.
Regards,
Sweety
I want to pass a couple of date variables in British date format to a function
Here's the function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies View RelatedI have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable. I need to check whether count > 0 or not in stored procedure.I have used below query.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
I'm trying to create a variable for use in a DTS job for yesterday's date in the string format YYYYMMDD for use in pulling yesterday's material transactions from my ERP system. I've got the Query statement created and working fine, but I have to manually go into the DTS job and insert the proper date manually. I'm looking into an Active-X script inserted into the workflow for the job to create a global variable, but I can't seem to come up with anything that works.
Any help or direction would be appreciated.
Thanx in advance!!
Glenn
I am using Execute sql task in my SSIS package, and I am trying to make the following query:
<o:p></o:p>
Select max(sqlid) from archive.dbo.Archivebbxfbhdr
where timein <= ?<o:p></o:p>
Where ? is my input parameter variable migration_start which is a datetime.<o:p></o:p>
My issue is that variable name migration_start which give me default format of 6/11/2015 1:26 AM
But I expecting to get in 2015-06-11 01:26:22.813 format.<o:p></o:p>
How I can I change the datetime format of my variable to be (yyyy/mm/dd)hh:mm:ss)?<o:p></o:p>
I have a txt file with format as following
MailAddress:Kienpt@ifi.local
DomainName:ETH2K
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:
- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)
Can you help me?
Hellois there a variable that is available to me that contains the numberof rows contained in a dataset return from a database call?
have a class that runs a stored proc and returns a dataset/resultsetlooking to simply assign an integer this value if it is possible
i'm using (learning) vb.net and sql server
thanks in advance
I have created a File System task which is contained in a Foreach Loop Container. I have .bak files that are populating a directory from a maintenance backup plan.
There is a point where I need to delete the .bak file's after I've zipped them all up.
How do I set the SourceVariable to read through the directory and pick up just the .bak file's in the directory to delete.
I'm exporting reports daily to a file share and I need to rename the reports with a pseudo time stamp.
Example: I have a report named "Disk Usage" and when I export (using a data-driven subscription) I want to rename it "Disk Usage - (Jan07)" - or something to that effect.
Can anybody tell me how to accomplish this.
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
Hi,
Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :
Must declare the scalar variable "@BookMarkArrayA".
when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error
This is the code :
Public Sub NewCustomer()
Dim temp As String = " "
Dim ID As Integer = 1
'Restore the array from the view state
BookMarkArrayA = Me.ViewState("BookMarkArrayA")
temp = BookMarkArrayA(6)
Dim Customer As SqlDataSource = New SqlDataSource()
Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()
Customer.InsertCommand = "INSERT INTO [Table1] ([ID],[Program]) VALUES (@ID, @BookMarkArrayA(6))"
Customer.InsertParameters.Add ("ID", ID)
Customer.InsertParameters.Add ("Program", @BookMarkArrayA(6))
Customer.Insert()
End Sub
Cheers
Ken
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
I have a simple CSV/XLS dump that i need to put up on the server. I would need to create a file output daily and need a variable name to identify them easily.
I have the problem of naming it in the "connection string" easily and i have tried variables and expressions too.
I am new to this so please need help.
Thanks
Hi,
I want to retrive the values from the database 'northwind' and then i want to store the backup files in "D:/Sample/north_database.bak" format(local machine).
I retrive the database values in .txt,XML format. Now i want to take in .bak format.
give the Suitable solution for this.
Subashini.G
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Backend: MSSQL 65
My datafiles are bcp out from Syabse 11.
Because the order of fields are different, I need to use format file with BCP.
I generated my fmt files by using MSSQL's bcp utility with the instructions from SQL Server books onLIne
(section: Using a Format File to Selectively Copy data)
I cannot bcp in with format file. I got
Starting copy...
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total = 50
Since the datafile is small, I manually shift the order.
Then, I bcp it in without format file and it worked fine.
Now I got data in my SQL Server
I thought it may be the datafile. Though it's text file, it's generated by Sybase bcp utility. :-)
So, I bcp out the data to a new ASCII file. I change the ID to make those records different.
Then, I re-generate the format file.
Now, I have everything generated by MSSQL. I tried again and I got the same error.
This table contains 1 Blob field and 4 varchar(255) fields. Will this be a problem?
We have to solve this problem. When our applications go live, the datafiles will be huge and we cannot edit it manually.
Please advise
Thanks in advance.
Elizabeth Pickering
set @date = getdate()
set @month = convert (varchar[2], datepart(mm,@date))
gives me 2 instead of 02 for month of February
any clues ?