Who's The Idiot That Designed The File Destination?
Jan 23, 2007
It's funny:
Everyone thinks that CSVs are awesome to transport data. I mean after all, SSIS defaults to Comma Delimited files. Even Excel defaults to it. Microsoft is supposed to be our leader! They should get this right. And get the terminology correct too. How many people describe the file as comma separated? It's delimited! Even SQL server calls it delimited by the "delimited" drop down.
CSVs suck and i will tell you why:
When you transport any text field (especially Address) it has the possibility of containing a comma. This causes data to be parsed into the wrong fields. Why in God's grace would you EVER get in the habit of choosing a delimiter that SOMETIMES doesn't work?
I'll tell you a little short story:
I have been waiting (at work) for like a month for a guy to export data and give me this file. Well today i finally got that file. He was in a hurry and used the Defaults to export this file. I don't blame him for being in a hurry and using the defaults. Well the defaults made the import NOT work because the data parsed into the wrong columns. Comma delimited would work if there was a text qualifier. But the default is comma delimited and NO text qualifier. What idiot thought that would be good. Or it was the separation of duties that eff'd this one up? Who knows?
Fix your products!
That's my .02
View 14 Replies
ADVERTISEMENT
Sep 1, 2005
I have three tables: table1 has a one-to-many relationship with bothtable2 and table3. When I do a left join to get a count from table2,it works:SELECT table1.field1, COUNT(table2.field1) as MyCountFROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1WHERE...GROUP BY table1.field1I want to also get a count from table3:SELECT table1.field1, COUNT(table2.field1) as MyCount,COUNT(table3.field1) as MyOtherCountFROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1LEFT JOIN table3 ON table1.field1 = table3.field1WHERE...GROUP BY table1.field1The Count totals from the above query end up being a multiple of thetotals that I actually want. (If there are 8 records each in table2 andtable3, the counts would be 64 for each table). Can someone help meget the proper counts, in this case 8 from table2 and 8 from table3.Thanks!
View 2 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
Aug 26, 2015
I have a ssis package where I need to have excel destination. In the Excel file, I need to have few rows with some text and then populate data below the text. One the text is like this:
Data as of: 08/25/2015
if the report ran today, then Data as of will have Yesterday. So, if the user opens that excel file after a week, then user should see same Data as of: 08/25/2015. not today()-day(1).
I was planing to handle on excel side with today()-day(1). but it only works the day it was run. Then the excel file is open after few days later, then it might as Data as of: 08/30/2015 which is not true. It should still stay Data as of:
08/25/2015 on what ever date the excel file is open. The SSIS package runs only once.
How do I handle this so that whenever user open the file, they will see Data as of: 08/25/2015. This is not a column in excel. It is like a description of data in excel.
View 3 Replies
View Related
Oct 9, 2006
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(User::FileDestinationPath?).Value = dts.Variables(User::FileDestinationPath?).Value & ? Month & _? & Day & _? & Year & ?) which gives me C:TestFiles 9_14_2006?.
File System Task (within For Each Loop, second step) This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that days date. Basically, how do I get this to work since I cant hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the Variable FileNameAndLocation? is used as a source or destination and is empty.?
Let me know if I need to clarify further...I may be missing something very simple. Any help would be greatly appreciated!
View 10 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
Jun 17, 2004
Tried using webmatrix code builder to simply bind a web grid to a sql table.
Works great in WEbMatrix, but when I move the code to Visual Studio.VB,
I get a logon error when the code is run.
Any Help is Greatly Appreciated... I am stuck.....
Here is the code:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DataGrid1.DataSource = GetCustomers()
DataGrid1.DataBind()
End Sub
Private Function GetCustomers() As System.Data.SqlClient.SqlDataReader
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='ASPExamples'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Customers].* FROM [Customers]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
End Class
View 1 Replies
View Related
Dec 20, 2007
I have a table which im joining that looks like this
123,1
123,2
123,3
123,4
456,1
456,18
456,71
456,99
I want to return the first column #s where there isnt a 2 in the second column. and i need it as a group by...
like, select column1 from table1 where column2 <> 2....
but that above query would still grab the 123.. because of the fk..
i would like the query to only pull the 456 b/c none of the fks with 456 have a 2... understand?
thanks
View 6 Replies
View Related
Apr 17, 2008
Hello friends,
I have the following (simplified):
1. Flat File Source
2. Conditional Split, Case Good = !ISNULL(KEY) Case Error = ISNULL(KEY)
3. Case Good -> Writes to Good Flat File (with timestamp in the title)
4. Case Error -> Writes to Error Flat File (with timestamp in the title)
Most job runs have no errors but the error file is created as a zero byte file anyway. If there are no error records I don't want the error file created. How might I accomplish this?
Thanks
View 5 Replies
View Related
Feb 14, 2007
I have a variable called @ORComm which has been selected using a cursor from each line on an order.
DECLARE TC2 CURSOR FOR
SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @OrdNum AND [Companydiv] = 'GLPC-TRANS'
OPEN TC2
FETCH NEXT FROM TC2 INTO @ORComm, @ORTotal
I need to compare the resulting @ORComm against a list of valid commodity types selectable by
SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0
What's the easiest way to do this?
View 2 Replies
View Related
May 22, 2007
Hi all, I run several contact websites. One of them is www.ramc.org.uk - they used to fill in a form and results emailed to me. I then manually copied and pasted into page on server. Very tiresome.
I was hoping with sql express i could create a page where they enter there details into the database after verifying details via a link..... Then as the new details are added they would be displayed on the contacts page in alphabetical order.
Is this really possible or even feasable.
Cheers, your all stars. Mark
View 5 Replies
View Related
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
Feb 5, 2008
Hi,
Is it possable to have control which name the flatfile has? F.ex. have the same process created files with the name:
filename_2008-01-01
filename_2008-01-02
filename_2008-01-03
Thank you.
View 10 Replies
View Related
Jun 10, 2007
Hello,
I'm trying to send a file with FTP.
In the Flat File connection manager I have to write the name of the file.
Is there any way of deciding at runtime what the name of the file should be?
Thank you.
View 13 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
May 28, 2008
Hello All, I want to know.
I have an SSIS Map which I am running. The destination is Raw File Destination.
I want to know what is the Raw file destination.
I mean in what format and where I can view this data.
I tried notepad but its not showing proper results
Please let me know thankss
View 1 Replies
View Related
Feb 26, 2008
I am new to SQL Server 2005. I have tried to be a good student and learn on my own about designing the database. However, I am facing a blockage of getting data into the database. I want to verify I have setup the relationships correctly. Here is the tables.
1. A Manufacture has an Address.
2. A Manufacturer has one or more Distribution Points.
3. A Distribution Point has an Address.
The Manufacturer --> Address is a one-to-one relationship.
The Distribution Point --> Address is a one-to-one relationship.
The Manufacturer --> Distribution Point is a one-to-many relationship.
The manufacturer table has a FK to the address id (PK).
The distribution table has a FK to the address id (PK).
The distribution table has a FK to the Manufacturer id (PK).
Address has several other FK, but not used in this scenario.
The INSERT and UPDATE Specification for the Delete Rule and Update Rule is set to "No Action" for the Manufacturer and Address tables. In the Distribution Point table, the relationship for the update rule involving the relationship with the Address and Manufacturer tables is set to CASCADE. What this tells me is SQL Server 2005 will update the table should changes occur in Address and Manufacturer table.
Is there anything else I can check to be sure the issue I am facing is NOT the deisgn of the database?
Thanks for taking the time to read my post.
Russ
View 1 Replies
View Related
Jul 2, 2002
Hi,
I am exporting a table to an excel format. The package should change the name of the destination file everytime the package is executed(preceded by date). I need to deploy this urgently.
View 4 Replies
View Related
Aug 29, 2006
Hi, I am new to SQL Server. Trying to convert a pc sas program to a SQL Server 2000 DTS package. Need fixed format comma delimited destination text file from a DB2 data source. Problem: SQL wants to make financial field 19 bytes long but I want it to be 12. Tried casting as a DECIMAL(7,2) but SQL still wants it to be 19 bytes long. Tried converting to CHAR but then it is still left-justified. I need decimal to be in the third byte from the right. Is this possible?
View 1 Replies
View Related
Jul 5, 2007
Hi,
How can I check if my Flat File Destinatino is empty or not?
Im sending it with FTP, but I dont wanna send an empty file.
Any ideas?
Thank you.
View 1 Replies
View Related
Jan 27, 2007
Per the subject line :Is SQL Server Express designed for production use ?
View 11 Replies
View Related
Sep 27, 2001
I have a Access database which has a address field with multiple data sets in it. I am new to writing code and am having trouble finding a example which will help me split these values out into three seperate fields.
Notice how there is no good order in how they are placed.... Some entry's have commas and some don't. Some have periods and some don't.
EX:
[BILL_ADDR3]
AMHERST, WI. 54406
ASHLAND, WI 54806
ASKOV MN 55704
I then need to convert the state to a state code ie:
2 AK
3 AL
4 AR
5 CA
6 CO
7 CT
8 DC
9 DE
10 FL
11 GA
12 HI
13 IA
14 ID
15 IL
16 IN
17 KS
18 KY
Any good sources of information would be greatly appreciated..... Thank You
View 1 Replies
View Related
Mar 28, 2006
I am working on an application with a number of tables (which I can't
change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3,
c3... The tables contain 5 to 10 sets of data in each row.
I want to create a stored procedure to return a cursor with a row for
each set of data for a row of the table. I know I can use unions:
select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk
union
select a2, b2, c2... from sometable where pk=@pk
union
select a3...
Is there a way that is more efficient (eliminates requerying the table and all the unions)?
View 4 Replies
View Related
Jan 28, 2007
Per the subject line :
Is SQL Server Express designed for production use?
View 9 Replies
View Related
Jun 11, 2001
Hi,
I try to use Data Transaction Service (SQL Server 7) to copy information from an Sql Server Table to an text file (fixed field). When I run the process, no problem, the text file is created and a Ok message appears. But when I look in my text file, every time a field was NULL or empty in the source table, the following fields are not aligned !
Somebody knows what the problem ?
Thanks
Vincent
Ps: I'm french so excuse me for my english
View 1 Replies
View Related
Jul 5, 2007
Hi,
I'm trying to output the results of a query straight into a raw text file. The problem I'm facing is that the tool seems to write some unwanted characters at the top of the file and within it. For example, I get the name of the column that was used as input at the top of the text file:
[] [] <<name of column>> [] []
I need to have a clean file containing strictly the results of the query.
Any ideas?
View 1 Replies
View Related
Mar 14, 2007
I am using SSIS to create a weekly data extraction that will be emailed to an external agency. I can extract the data, create the file and email it without any problems but I want to compress and encrypt it before I send to give some measure of protection to sensitive data it would contain.
If I did this manually, I would simply use Winzip to compress and encrypt the file to be sent. How can I achieve a similar result programtically?
View 1 Replies
View Related
Jun 29, 2007
Hi,
I have created a package and when i was trying to configure a flat file destination, i am getting the following error:
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.
===================================
Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
Please advise.
Thanks & Regards,
Deepak
View 1 Replies
View Related
May 4, 2007
Hi,
I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.
For Example,
CustID, ProductID, Product Name, Product Description
Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.
It is being done in a single Data Flow Task.
Right now the Property Expression for the File Name is which is not working)
Code Snippet@DestFolder + [Data Conversion].ProductID + @TodaysDate + ".txt"
The ProductIDs are in the ascending order. Any help or guidance?
Thanks
-Leo
View 7 Replies
View Related
Apr 15, 2008
I am creating an XML file from 'n' number of tables using Dataset.Writexml()
Now, I join tables using INNER JOIN and fill the resultset into a dataset (There is a foreign key called ID in each of the tables).
The resultant XML doesn't shows the nested nodes, It shows the nodes sequentially.
The Result I got was,
<Root>
<Name>
<FName>Fxxx</FName>
</Name>
<Name1>
<LName>Lxxx</LName>
</Name1>
</Root>
The FName and LName resides in different tables.
<Root>
<Name>
<FName>Fxxx</FName>
<Name1>
<LName>Lxxx</LName>
</Name1>
</Name>
</Root>
I have tried the DataTable Relations and Dataset merge.
Any work-around / Straight approaches ?
View 7 Replies
View Related
Sep 18, 2007
I have been searching for the answer to something I thought would be easy to find. But, no luck.
I need to load a csv file into a SQL Server table. The rub is that I need to also parse the filename for a couple of pieces of data also.
Example filename: c:importCustomerX_LocationY_1234.csv
For each record in the csv file I will call a stored procedure the has a parameter for each column in the file plus a parameter for the customer name and a colummn for the location name.
I assume that I will need to use a Script Component to parse the filename fro the information. What is not clear is how I get the filename from the csv connection object and how to get to result of the script component to the inputs of the OLE DB command component that I am using to call the stored procedure.
I have succeeded in using the Derived Column component to put constant values in for the customer and location. But, I cannot figure out how to get to the next step of deriving those two values from the input filename.
Thank you n advance for any assistance,
Jim
View 4 Replies
View Related
Mar 3, 2006
Hi,
I would like to know if there is any trick to convert numbers to csv files. I don't have any control to format the numbers.
Source: Sql server
for example:
select cast( 1 as float)/ cast(201 as float)
Destination:CSV file
the output of the file is 4,9751243E-3
i don't want to have scientific notation on the number. I want the result to be 0,00497512437810945. how can i do it?
I tried to change the type of destination columns to String or numeric but nothing works. what is the influence of changing data type in the destination columns e the output to files?
I tried using convert but with the same results.
Can it be because of local settings or something else?
thanks
prb222
View 4 Replies
View Related
Feb 27, 2006
when using a raw file destination it would be nice to be able to use an environment variable for the filename property.
like
%my_extract%data.txt
instead of
c:my_extractdata.txt
View 23 Replies
View Related
