SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output
Mar 2, 2014
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.
Can someone please point me as to where I can find info for the following. I have seen this somewhere but I am unable to find it.
I am trying to do INSERT INTO Table SELECT ... FROM OPENROWSET(BULK '\sharedDriveDataFile.txt', FORMATFILE = '\sharedDriveFormatFile.txt', FIRSTROW = 2) AS Q
How can I make that shared drive dynamic? as such..hoping not to use dynaic SQL INSERT INTO Table SELECT ... FROM OPENROWSET(BULK @SharedDrive DataFile.txt', FORMATFILE = @SharedDrive FormatFile.txt', FIRSTROW = 2) AS Q
I am somewhat new to DTS packages - please bear with me...
:: Overview: From a web page, I need to create a text file on a User's mapped drive drive (note: the mapped drive is always the same and it is a drive that the IIS cannot see, and FTP is not an option here). One of the parameters I need to pass from the web page (to the DTS) is the text file name.
Currently, I have a stored procedure that creates my dataset for my text file, and I can create the text file from the web page onto the IIS server, but this does not solve my problem.
My dilemma is how do I create the dynamic text onto the mapped drive? The SQL server CAN see the mapped drive.
Can I bring together what the Stored Procedure creates AND pass thru the dataset and the dynamic name to a DTS so it can create and save the file?
Can the Stored Procedure be eliminated and it all be done via DTS?
What about passing in parameters directly to a DTS from the web page? I'm not finding much help on this portion
Any help and/or direction would be appreciated as I am on a tight deadline!
I have a SSIS Package that exports data from Sql Server to an Excel file. I need help figuring out how to have the file name be "Report_02132007.xls". Basically I want to append the date to the file name. Any ideas?
I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv,trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv etc.,
so it will vary as per user selection . i need to run the same SSIS package to execute the different file name with the same structure.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
I'm a newbie to SSIS so this question may cause people to point and laugh. I apologise if that's the case.
I have a configuration database that holds connection details to a remote database. I want to use these connection details to dynamically create a new connection to some remote database.
I usually work in with C# where this would be simple but I can't find how to do this using SSIS.
I have found people using configuration files but as I already have my connection data in a table I don't really want to strip it out. (Also, is it a security risk to have database connection strings stored in a config file?)
I have been able to implement a custom script object to build the connection string and assign it to a user variable within the package but can't find any way to then use that variable to connect to the remote database.
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
I have packages that must generate log errors dynamically including time of execution and the name of the task.
I make it changing the properties of the connection inserting two expressions.
1.-I alter the File Usage Type to 1 to generate this files. 2.- I alter the connection string as: @[User::myvariable] +"constant_description"+ time description+ +".txt"
The time description is : (DT_STR,40,1252) DAY (GETDATE())+"-"+(DT_STR,40,1252) MONTH( GETDATE())+"-"+(DT_STR,40,1252) YEAR( GETDATE())+" + REPLACE( (DT_STR,10,1252) (DT_DBTIME) GETDATE(),":","_") But it is not the problem
In those packages I have one connector for all the tasks and in execution time it creates one file for each of the tasks.
The problem is the way I insert in the filename the task name.
I have a pre-execute event handler in each task that modifies a string variable( myvariable) appending the task name. When I execute de package it works great but when I only execute a task, the program do not enter in the event and do not put the tasks name.
How can I put that name without using that handler? There is another handler can I use to do it that happens before the system generates the new file name and after pre-execute? Anyone knows another way to do this kind of things?
My task is to read the filename from a database table and transfer the flat file data in to a table. In SSIS,I am able to fetch the file name using a Data Reader Source; but how to pass this fileName parameter to Flat File Source ?
In DTS I have used ActiveX script to pass filename variable as flatfilecon.Source. Any help ?
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 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 have a source files folder where the files generated everyday. My goal is pick the latest file and copy this single file to another folder. I used the Foreach loop container and got the latest file and stored the file name to a varible i.e. LatestFile Then i want to use the File System Task to copy this to the destination. On the beginning, I could not setup the Latestfile since I don't its name then, so when I setup the Source Connection property of the File system task, it is not allowed to leave the SourceVarible as blank!
I am having trouble figuring out why the following code throws an error:
declare @cols nvarchar(50), @stmt nvarchar(max) select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W select @stmt = ' select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4 Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT IF OBJECT_ID('#External_Referrals') IS NULL DROP TABLE #External_Referrals; GO DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
In my destination table i am having some 30 columns and the CSV files what i get may have 10 columns or 20. How do I map columns between source and destination dynamically?
I am new, very new with Integration Services. I try to export a monthly report from a table to an excel file. How can I dynamic the file name. For example, reporting for May, I want to have the file name Report_May_2006 and if the report for June, I want the file name: Report_June_2006, etc. Can I use a variable for this? Please help! Thank you!
I want to create a txt file from a table i have. I have the data in the correct formats but i want to include some padding around my selected four columns.
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location 2. Create a table according to that xml data file metada 3. load data on newly created table. 4. Pickup the next xml data files. 5. loop through, till the XML data files are exists on that location.
I am working on FTP TASK in SSIS Package. i have to get files from FTP that file names are like 20141110.txt. i want to download any particular date file from ftp. How to i set expression in Remote path?
updating a recordset contained in an System.Object variable during runtime.
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?
I have a stored procedures that will import 28 CSV files with different columns layout for each one, into their own table in SQL Server 2008 and a master stored procedure which will execute the other 28 store procedures.
All the CSV files are stored on the network drive in one folder.The naming of these files will vary from week to week and I don't want to manually change the names in the stored procedures. Each table name has a unique Identifier F1 to F28 that will be a constant before the rest of the name for example ...
F1_country23 F2_region12
Can I use the unique Identifier to add an wildcard or variable to the stored proc so it will pick any file in the folder for example with "F1" or "F2" meaning format layout 1 or 2 and use the correct stored procedure like below?
WHEN 'F1' THEN 'usp_F1_ImportScript' WHEN 'F2' THEN 'usp_F2_ImportScript'
Example SQL scripts so far ...
Code:
Create PROC [dbo].[usp_F1_ImportScript] -- Check if template table already exists and if it does then delete the table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CSVTest]') AND type in (N'U')) DROP TABLE dbo.CSVTest -- Create a new template table
[Code] ....
the SP which will run the 28 SP
Code: CREATE PROCEDURE [dbo].[usp_FilePicker] (@FileName VARCHAR(255)) AS --check if @FileName is null IF @FileName IS NULL BEGIN SELECT 'No Files Match' AS ERROR
[Code] .....
I cannot use SSIS because other users from different countries (50+ so far) may need to add/delete columns in the raw text/cvs files (for their own test environment) meaning there may be changes needed to made to the SQL scripts and with documentation any user without experience with MSSQL would be able to make simple changes.
I am encountering the same problem above and I did exacly as described . But it is not working.
I must send emails every month with dynamically named files as attachments. The files are named according to the date on which they are generated. For example on the first of November 2007, the file will be named myfile_1_11_2007.
I have created a variable called DynamicFileName with package scope, data type string and default value: d:\tests\
In "Send Mail Task Editor" Dialog Box, I have specified the following:
smtpConnection: smtptest.server.com From :nemo@smtptest.server.com To: nemo@smtptest.server.com Subject: Dynamic File Email MessageSourceType: Variable MessageSource: blank Priority: blank Attachments: blank
When I execute the package, I get the following errors: ----------------------------------------------------------------------------------------- Error at Send Mail Task [Send Mail Task]: Either the file "d:\tests\myfile_1_7_2007.csv" does not exist or you do not have permissions to access the file.
Error at Send Mail Task: There were errors during task validation. ---------------------------------------------------------------------------------------------------
Of course, the file does not exist. It will exist at tun-time. How can I tell the Send Mail Task to use a filename that is dynamic ?
By the way, once I have specified the code for FileAttachments, on trying to edit the Send Mail Task Properties, I can see that the Atachments field has been set to "d: estsmyfile_1_7_2007.csv by itself: I never typed it there !! It seems that the task executes the code even before it is run. If I remove the attachment path manually, on running the dts, I get an error saying that "either the file does not exist or you do not have permission to access the file.
I would be most grateful if anyone could be of help
I have a requirement to create a reusable process that will be able to read a file and insert it into a table. The complication comes from the fact that the process will not just need to handle one file format, but files that will come in up to 20 different formats. (Some will be fixed width, some will be comma delimited, column names will be different.)
Since these formats may potentially change, such as a new column being added, the process needs to be dynamic enough to handle this without changing the package itself. While this task is somewhat daunting, I was hoping that SSIS might be able to handle it.
One thought that I had was to somehow have dynamic file connection, with the information about the file coming from a table. (I.e. The table would store values to specify the column name for a file type, the delimiter used, etc.) However, I don't know if this is something that SSIS is equipped to handle.
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question: What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
I have AdventureWorks installed. I am using SQL Server 2005.
I need to import some large tab delimited text files into SQL. From the research i did I thought that using BCO would be the best solution becausr I will have to import these files and export in the same format.
I am attempting to follow the example at: Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx B. Creating a non-XML format file for character data
In my command window at C: I enter: bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T I was expecting to be prompted with questions about the source file and have a format file created. When i press enter nothing hallende. No errors. Nothing.
Can come one give a beginner the step by step on how to creat the format file and use it to import (and export would be nice too).
I have an FTP server that will be receiving files. The directory and file structure will be a folder with a client name (can be called anything) and it will have files in it (these files will have the same filenames as all the other directories. So I will have folder JimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue with files a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location to a DTS package so I can import all the data from the text file into a SQL server. The way the SQL server will be set up is that each Folder from the FTP site will be a separate Database and each file will 1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the file location to pull all those files and then importing them to the proper database.
I'm not limiting the solution to DTS packages so if .NET can be incorporated to make it easier then so be it. But keep in mind I can have up to 200 folders with 12 - 20 text files ranging from hundreds of rows of data to many thousands of rows. And the package needs to be ran twice a day so time/performance is an issue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Hi everyone,I am trying to bulk insert some data from a csv file to a table. I can do it as part of a button on click event, but don't know how to do it using a stored procedure. This is what I have,ALTER PROCEDURE dbo.TestImportData ( @filename varchar(50) ) AS BULK INSERT dbo.[TestTable] FROM @filename WITH ( FIELDTERMINATOR = ',' ) /* SET NOCOUNT ON */ RETURNI get the error message "Incorrect syntax near '@filename', Incorrect syntax near 'with'). What am I doing wrong? What should I do? Please help!
