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?
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!
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:
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.
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!
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" ...
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
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)
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?
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?
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
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.
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.
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
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?
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?
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.
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?
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
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)?
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 !
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.
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?
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)
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)
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.
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?
