I need to use DTS to create a dynamic destination filename based on the value of a field. I tried creating a DTS package using the wizard. It works fine if I wanted a static filename for the destination filename. I tried creating a Dynamic Properties Task by selecting Connection2/OLEDB Properties/Data Source and then using a SQL Statement to return a value for the name of the file. It doesn't seem to run the query everytime the DTS is ran. The first time I run it it names the file correctly but after that it keeps naming the file the old value from the SQL statement. Any idea what I need to do to fix this?
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:
Scenario Configuration : VB.net 2005 Code, WebService for Executing SSIS on Server, SSIS deployed on the Database Server
Problem Description : We are developing windows applicaiton in which we call webservice which was deployed on the same server where SSIS packages are deployed. Now from Code we are passing FilePath name in variable and execute the Package. But the SSis result says that The file name "\computernamefol1fol2fol3fol4abc.txt" specified in the connection was not valid.
More Information:
1. Full Permission are given on this network folder. 2. Package executes successfully from SSIS development solution (BIS solution) 3. Deployed packed executes successfully from the Database Server. 4. From Development pc packege executes successfully. 5. Other packages deployed on the same server executed suucessfully with same configuration and scenario.
Only this package is not executing.
-- the only differece with this package with other is -
using ".txt" extension in Flat file connection and using VB Script task
Can any one suggest the appropirate solution for this problem...
I have a situation like i need to Update the Destination Server for the changes made in the Source server.
I have MS Access as my Source Server and having XYZ.mdb as my source database...My destination server is MS SQL server. During my first install i will create a dynamic DTS package to move data from XYZ.mdb to desitination server.Now when i do some changes in the Source Database(XYZ.Mdb) like i update one row on one table and alos included one row on the table...now how do i import only the Updated and inserted row to the Destination Server using DTS....
I will like to be able to call a DTS to which I pass in parameter the name and the path of a DBF file of which the function will be to create a table in my SQL server identical by the structure and the contents to the DBF. The problem is that I never know in advance the structure of the DBF. But, in DTS designer I am already obliged to inform the name and the structure of the target then to make the correspondences of column.
PS: I know how to pass parameters to a DTS via utility DTSRUN... / A... etc in order to provide him the name and the path of the DBF
I am currently working on a project where I want to load information from a web service into a table in SQL Server. I wrote a web service to returns a dataset. Next, I went into SSIS and added a Web Service Task. I had the web service task call the method and output the result into an .xml file. I then wrote a data flow to load the XML file, using the Web Source, into the database, using the OLE DB Dest. I then set the connection manager on the XML Source to use inline schema, because the returned dataset from the web service has the schema information included. Next, I picked the columns I wanted and proceded to pick the table and mappings I wanted from the OLE DB Dest. However, when I execute the package the .xml file gets generated correctly but none of the rows get added in the database. How can everything be set up "correctly", but no rows gets added? Is there a specfic format that an XML source must be for SSIS to use it correctly?I would think a dataset that is generated from a .NET web service should just work in SSIS. Any help would be greatly appreciated. Thanks.
For both OLEDB destiantions (and hopefully for the forthcoming ADO.NET destination adapter) it would be useful to have the following two output columns: NativeErrorCode and NativeErrorMessage.
For SQL Server, this would allow you distiguish between multiple errors which all roll up to the SSIS error "the value violated the integrity constraints of the column", which is way too generic for proper decision making (via conditional split).
For example, like SQL Server replication, you should be able to ignore duplicate key errors (error number 2627) indicating the record existed, but error out on nullability constraint errors (number 515) in which the record could not be inserted. If you had a native error code, you could make this decision, while the SSIS error for two different native errors is precisely the same.
There is a known and accepted race condition between a lookup transform and subsequent OLEDB dest insert attempt (assuming a non-transacted container and a common component target table), which is why the 2627 can be safely ignored in certain instances, while a 515 should not be.
I am using a foreach loop, with the data from an ado recordset, which contains the table name that I wish to write data to an OLEDB data dest. The table names are retrieved from an execute sql task in the an object var. Within the foreach loop, for each table name, I then use a datareader to an ado.net source to pull data from that table, via an expression construct into a variable - i.e. "select * from " + @[User::table_name]. This works fine for the first table, in which mappings are setup using the SSIS design environment. The data is retrieved. I then use a variable and set the data access mode for the oledb destination to "Table name or view name variable". This also saves data fine for the first table in the loop in the oledb dest. When the next table name is retrieved from the ado provider in the foreach loop, the datareader fails, as it still thinks the metadata mappings are from the first table, which was used for the mapping in the design environment. I.E. FIN_CLASS is a column from the first table in the loop.
Error: 0xC0202005 at Data Flow Task, DataReader Source [7181]: Column "FIN_CLASS" cannot be found at the datasource.
I have set the following properties, that I thought (in my feeble mind), are supposed to avoid that behavior. For the datareader, I set ValidateExternalMetadata to false, and for the data flow task (container for the datareader), I set DelayValidation to true. These settings, according to the doc, are supposed to evaluate metadata for the datareader source at runtime (not design time), so that the column metadata is dynamic, and so that the subsequent oledb destination can use the "data access mode" for the oledb destination of "Table name or view name variable".
If I cannot get this to work, I have 2 options: Use OPENQUERY via dynamic t-sql statements, OR create 30 separate flows in SSIS - one for each table - not looking forward to that one.
We have several hundred very simple ETL SSIS 2K8 package files (*.dtsx).
I'd like to be able to interrogate them to determine source and destination fields.
There's no great need to map source to dest or to extract data types.
So far, the most promising candidate is to load them using OPENROWSET into an XML field in a SS table.No problem there, but querying using OPENXML has me stumped.
The package files will change a couple of times per year, so the process needs to be repeatable with minimal manual intervention.
I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.
I searched on this in the forums but based on the question it's difficult to kow if it has been answered or not.
Details:
I am writing some SSIS packages that need to be executable on another server. Many of the Data Flow Tasks copy data (such as from a Fuzzy Grouping transformation, and lots of other stuff) into a new table. But the other server will not have these tables set up for the first run.
My current solution is to check information_schema.tables and drop IF EXISTS. But, then the Data Flow Task will not work (becase table does not exist). So, I script to new window a create table statement based on the existing table that I use in my dev environment. This is a hack and I want to find a better method.
It is quite possible (although unlikely) that the source columns could be changed in the future, or some query used to pull the data might be modified. If this happens, then I would need to change the CREATE TABLE Execute SQL task. I want my package to accommodate without having to modify it.
When I use the Import/Export Wizard, I can select a table name from the drop down list OR type in a new name. When I type in the new name, it assumes I want to create the table. NOW, is there a way to mimic this in BI Developer Studio? Yep, I saved the Wizard version of the SSIS package and all it does is run a CREATE TABLE statement first.
I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.
If you use "SELECT filename FROM sys.sysdatabases" this will return the full path , such as "E:MSSQLdatamyData.MDF" ,
is it possible to return just the "myData.MDF" part of it? I know that you can parse the string , but is it stored somewhere else , or is there a command that will return just the physical file name ?
Jack Vamvas -------------------- Search IT jobs from multiple sources- http://www.ITjobfeed.com
Hi Guys, got a question that I'm not sure there is an answer to other than 'No Way'.
I have to export a file whose filename must include a sequence number. I have created the table to hold the seq# and the code to increment it. My problem is how do I include the seq# in the output filename. I am using the Flat File Destination tool for exporting. should I be using another tool?
Does anyone know how to execute a directory listing of a file and return the file name to a variable in SQL? For instance, there's a file called c:datach20011010.txt and i execute the following: xp_cmdshell 'dir c:datach*.txt'
or any other transact sql or procedure, how can i get the filename into a variable...all in sql?
FYI - I need to pass a filename to a process so that it can bcp the data into a temp table. But I don't know the filename at the time the process is setup/scheduled.
Hi, I am using a foreach loop to go through the .txt files inside a folder. Using a variable I can pickup the filenames the loop is going through. At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure. Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.
The question is: While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
Please note, this is a different question to my other post.
Hi, I think I may have mis-guided some of you in my previous post. So I thought I explain it properly this time in a new post...
Here is the description of the ssis package:
Inside the connection manager there is a connection to the sql server database and a flat file connection.
The flat file connection inside the connection manager has the expression set i.e. ConnectionString set to @[User::FileName]
And so there is nothing inside the connectionstring property of this flatfile connection.
Inside control flow:
1)
Dataflow which contains a flat file source and an ole db destination
The flatfile source is supposed to pickup the .csv file that gets passed to the ssis package. Then the ole db destination is where the .csv gets imported into a sql server table.
2)
Back in the control flow, there is a task that calls a stored procedure with the parameter which is set to FileName
The question is: How does this FileName parameter gets set before it is passed to the stored procedure?
Hi, Using a ssis package I have several tasks. A variable is also created called FileName. There is a flat file connection called InputFileName. initially when I designed the package, I hardcoded the pass of one of the .csv files into the path of the flatfile connection.
There is a dataflow which has flatfile source and oledb destination. These two basically pickup the file and import into the database using mapping.
So therefore the package works and data gets imported into the database.
Then I used the variable to make the filename dynamic. So I removed the hardcoded connectionstring in the InputFileName.
Is this the right thing to do to make the package dynamic? I would like to pass in any .csv filename and so the package runs. My question is now that the connectionstring is deleted but instead placed an expression for the filename, then how does the system know which file to use?
Hi, I want to get my SSIS package to look for a file in a named directory that has a ceratin string in the filename. example - My file is in a folder called data and the filename is 'System_UT_INCR_BOOKINGHEADER_20080214000000.TXT' Every day the fiel name changes to reflect the current date. I need my package to search for the String "BOOKINGHEADER". I know I need to create a variable with that value but I am not sure (using the example below) how to write the code to do this.
I take a backupfile every night and its named like 20070911_xvall.bak. Now I need a procedure for an automatic restore in another sql server but I have to do a check to se that the date of the backupfile is the same as actual date so it cant happened that an older backupfile being restored. Is it a good way to take actual date och build up the wanted filename and how can I then check if the file exist, its stored on another computer? Anyone who has some sample I can start with? Thanks
I have a question regarding the report file which is included in an email, when a subscription is created:
Is it possible to programmatically add something to the name of the created file of the subscription? I want to have one parameter of the report, which is a date, added at the end of the filename to let the clients distinguish between different report files.
i have four text file which comes in different format , one of the fields i need to generate is the source Filename without extension as one of the coloums in the target tables.
The name of the file is requirment in the destination, how could i read the file name of the text file source and make it a coloumn and insert it into target.
How do I correct this error: "The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)" ? Where is the database stored and/or how can I change the relevant settings? I have Both Visual Web Developer Express and SQL server express. Exception detail: System.Web.HttpException was unhandled by user code Message="The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)" Source="System.Web" ErrorCode=-2147467259 StackTrace: at System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) at System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) at System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) at System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) at System.Configuration.SettingsBase.SetPropertyValueByName(String propertyName, Object propertyValue) at System.Configuration.SettingsBase.set_Item(String propertyName, Object value) at System.Web.Profile.ProfileBase.SetInternal(String propertyName, Object value) at System.Web.Profile.ProfileBase.set_Item(String propertyName, Object value) at System.Web.Profile.ProfileBase.SetPropertyValue(String propertyName, Object propertyValue) at ProfileCommon.set_MyNewProperty(String value) in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileslesson04385e3cebf08b8b90App_Code.jqcpv0qn.0.cs:line 24 at _Default.Page_Load(Object sender, EventArgs e) in D:Daniel's DocumentsVisual Studio 2005WebSitesLesson04Default.aspx.vb:line 15 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Thanks in advance
how can I store a filename in the database, i have some GIF files which i want to show up on report, how can i specify those filename with a path in the database. i tried manually entering the path c:Dataimages.gif but it didnt work. Can someone pls tell me how to do it.
I have a page called 'upload.vb', which is the code for a file upload project I am working on. I am trying to write the filenames to an SQL database that I have built, but I keep getting this error: Compiler Error Message: BC30201: Expression expected. It doesn't seem to like the @ on line 46 in the VALUES part of my INSERT statement. Can anybody shed any light on this. Thanks. 1 Imports System 2 Imports System.Data 3 Imports System.Configuration 4 Imports System.Web 5 Imports System.Web.Security 6 Imports System.Web.UI 7 Imports System.Web.UI.WebControls 8 Imports System.Web.UI.WebControls.WebParts 9 Imports System.Web.UI.HtmlControls 10 Imports System.IO 11 Imports System.Data.SqlClient 12 13 ''' <summary> 14 ''' Upload handler for uploading files. 15 ''' </summary> 16 Public Class Upload 17 Implements IHttpHandler, IReadOnlySessionState 18 19 Public Sub New() 20 End Sub 21 22 #Region "IHttpHandler Members" 23 24 Public ReadOnly Property IsReusable() As Boolean Implements System.Web.IHttpHandler.IsReusable 25 Get 26 Return True 27 End Get 28 End Property 29 30 31 32 Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest 33 34 If context.Request.Files.Count > 0 Then 35 ' get the applications path 36 Dim tempFile As String = context.Request.PhysicalApplicationPath 37 ' loop through all the uploaded files 38 Dim j As Integer = 0 39 While j < context.Request.Files.Count 40 ' get the current file 41 Dim uploadFile As HttpPostedFile = context.Request.Files(j) 42 ' if there was a file uploded 43 If uploadFile.ContentLength > 0 Then 44 45 uploadFile.SaveAs(String.Format("{0}{1}{2}", tempFile, "Uploads", uploadFile.FileName)) 46 SqlDataSource.InsertCommand = "INSERT INTO tblDocuments(TheFileName) VALUES ('" & @TheFileName & "')" 47 48 End If 49 System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1) 50 End While 51 End If 52 ' Used as a fix for a bug in mac flash player that makes the 53 ' onComplete event not fire 54 HttpContext.Current.Response.Write(" ") 55 End Sub 56 #End Region 57 58 End Class
Hi, I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk. I need to join the data in the XML with rows in the "Resources" table (and other tables). Is it possible to get a Stored Procedure to load the XML file into a temporary table? Is loading it into a table the best way to do it - or can I join directly to the XML file somehow? What is the best approach? (I know nothing about XML in SQL Server yet) Thanks in advance, Chiz.
