I have a C# assembly that scans textfiles in a certain directory residing on the same server as the SQL2005 database server.
The names and directory are retrieved from a configurationtable on the SQLServer.
These textfiles have a header with column information.
The problem is that these headers can change and as a result the table in which to import this data no longer reflects the columnlayout of the header in the textfiles.
I would rather not use xp_cmdshell or bcp utility and would like to create a package that dynamically sets the input and output columns while the rest of the package remains unchanged.
At the moment whenever the header of the textfile has changed the assembly I created drops and recreates an importtable for the textfile. All this runs without a problem.
But if I then import the textfile with the dataflow-object in the existing package I have to reset the columns in the metadata object to reflect the changed columns.
This is easily done by pressing the reset columns button on the design surface in BIDS, but I would like to do this automatically (programmatically).
So what I would like is to load a package (=no problem) and then reset the columns of the textfile source and table-destination in code. The samples I found on the web seem rather obscure to me and I am not sure which way is the preferred way.
Can anyone give me an example of loading a package and then resetting the columns (VS_NEEDSNEWMETADATA ReinitializeMetaData ?) of a flatfile-datasource and a SQLServer destination
in code (C#)?
I have a source folder which have some *.dbf files (the number of files will change as time goes on) exported from another application. I want my SSIS package to load data from these dbf files in the source folder to my SQL2005 database no mather how many dbf files will be.If the destination table already exists in the database, the logic logic will do update (delete and import again), if not, create a new table on th fly and then pump the data.
What I did here is that I have a foreach loop container which contains Script task, Execute SQL task and a data flow. The Foreach container now can connect to the *.dbf files successfully, the Script task can read schema information from each dbf file and create script "Create table ...." successfully and Execute SQL task can create table in my new empty database successfully! But I have problem in the data flow task: my OLEDB Source points to the dbf file folder and set Data Access mode to "Table name or view name variable",but when I set Variable Name to a package level variable ("tableName"), I get error: Error at Data Flow Task_Pumping data from Admar [OLE DB Source [2737]]: A destination table name has not been provided. Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
Does anybody know what to do to create table and pumping data on the fly? (Using variable as table name)
Curently I am using a DTS package which is used to import data from Excel sheet into sql dollar table.
Now, the no. of Excel sheets is more than one and everytime the DTS package and VB Code has to be updated and sql dollar tables has to be increased to the no. of Excel sheets available.
The DTS package being executed by VB Code(.EXE).
How can I modify the DTS package and VB Code so that the import can be done dynamically irrespective of no. of Excel Sheets.
I have one share folder ,every month end-user will copy & paste excel file into particular share folder. Ok . Now i have to create new SSIS package as schedule should run every month to find the file and then load automatically into Sql server tables and then move those excel file to another share folder if file successfully loaded only. The excel file name will be changing every month. but the format wont change. If any body knows this process or steps. Please share with me .
I have huge text files coming from the other end.But they are sending as .zip files and when unziped i find .dat. Is there a way to set up such that my package can unzip those files and change .dat files to .txt and then start working with with those files.
I am transferring data from text file to sql server.I have created .dtsx packages. After the package executes i need to remove the data from the text file or even remove the text files. But i want my package to run it receives new textfile.What do i need to do??Please help????
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server. I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard. However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success) - Setting Source Connection (Error) Messages Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
I am trying to import an xlsx spreadsheet into a sql 2008 r2 database using the SSMS Import Wizard. When pointed to the spreadsheet ("choose a data source")  the Import Wizard returns this error:
"The operation could not be completed" The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine (System.Data)
How can I address that issue? (e.g. Where is this provider and how do I install it?)
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification.
Could you please look into this and guide me Thanks in advance venkatesh imtesh@gmail.com
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error) Messages Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.". (SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. (SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:
On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.
Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?
On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?
when trying to Ãmport files to our database server from a client, I keep getting an error:
- Validating (Error) Messages Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1). (SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175). (SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
Hi,I am having trouble importing data from an excel spreadsheet into MSSQL Server 2000 using DTS Wizard. The DTS import process issuccessfull, no errors, but only 50 rows of approx. 1500 rows of dataare imported. I tried to remove 20 rows in the excel spreadsheet inthe interval row 0-50. When i later ran the import, only 30 rows wereimported. I deleted almost every row in the interval 0-50, with theresult of the import having 0 rows imported (but job ransuccessfully). I decided to delete rows 0-100 in the spreadsheet inorder to see if the resolved the problem, but it didn't. As Isuspected something in the excel file to be the cause, I exported theexcel spreadsheeet to a tab delimited textfile, with only one row. ADTS import resulted in importing approx 100 rows, double the amount ofthe textfile, but the other 1400 rows were not imported. The data inthe column is containing numeric values only.Please help me! What could possibly be the cause of DTS skipping rowslike that. DTS doesn't feel reliable at all :/Regards,Björn
Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime as insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
How to create a sql statement that dynamically matches a database name in a stored proc. Somthing like this: Select Table_name From Information_schema.Tables Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0 and table_name like 'Item%Master' and (DATABASENAME=????????????????)
Hi....I am fresh to asp.net .... i want to get the Request.QueryString Value Dynamically in SQL Query ... I have attached the coding of two files please gothrough and give the solution..First File : -actyearwise.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actyearwise.aspx.cs" Inherits="acts_actyearwise" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="AccessDataSource1" Width="80%" align="center" runat="server"> <ItemTemplate> <tr style="font-family: Arial; font-size: small;"> <td align="left" width="7%"> <a id="href1" href="actcheck.aspx?2007" target="_self">2007</a> </td> <td align="left" width="7%"> <a id="A1" href="actcheck.aspx?2006" target="_self">2006 </a> </td> <td align="left" width="7%"> <a id="A2" href="actcheck.aspx?2005" target="_self">2005 </a> </td> <td align="left" width="7%"> <a id="A3" href="actcheck.aspx?2004" target="_self">2004 </a> </td> </tr> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex "></asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actyearwise.aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actyearwise : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }}// This page works Properly... In the actyearwise.aspx ..just some years with link... i have entered ...the href page is actcheck.aspx(below).. In this page i want the years dynamically.. please go throughSecond file : -actcheck. aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actcheck.aspx.cs" Inherits="acts_actfiles_actcheck" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="actno" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="actno" Visible="False"/> <asp:TemplateField SortExpression="actno"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> <%# Eval("actno") %> </td> <td align="left" width="75%"> <a id="href1" href="actshorttitle.aspx?<%# Eval("actid") %>" target="_self"> <%# Eval("actname") %> </a> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex where actyear = 'check' "> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actcheck. aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actfiles_actcheck : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { String check = Request.QueryString.ToString(); } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }} The text i had bold and italics in the actcheck.aspx... is the problem..... From the actyearwise.aspx .. i was passing the actyear value ..... In actcheck,aspx .. i want to get it dynamically in SQL Query...... Please give the solution .. I am using the SQL database.Table name actindexfields nameactnoactnameactidactyear In the actyear field i have different years .... Thanks in advance
I'm trying to dynamically set the TOP value in a query, but it doesn't work. Any other way it can be done?
Doesn't work: declare @sometopvalue int set @sometopvalue = 100 select top @sometopvalue field1, field2, field3 from table1 ORDER BY BRDCST_DT_TM field1 desc
my scenario is that, i have a table in which i have column such as col01,col02,col03....col31
i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col??') and i have to do integer increment in last two character (???01...???31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?
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?
I have an FTP Task where I have to set the server password at run-time. I have the ftp connection manager set up. What I normally do is configure the ftp server connection manager using a dtsConfig file but I can't do that in this case. I won't know the serverpassword until I get a parameter from the user.
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 so it will vary as per user selection . i need to run the same SSIS package to execute the package whichever the file name.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.
I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.
Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.
Hello Following is my code which is not working. <asp:SqlDataSource ID=events runat=serverSelectCommand="SELECT * FROM TBL_EVENT WHERE SECTION_ID=<%= Session("ID")%>"ConnectionString = "<%$ appSettings:SQLConnectionString %>"</asp:SqlDataSource> I want to pass the value of Session("ID") into that query. How can I do that?
i have to store some data on a remote sever(MS SQL SERVER2000). The scenario is like 1. The web application runs on a local machine. User (who inputs) uses through LAN.2. The Input should be stored in the remote server. if the remote connection is ok. otherwise it should be saved in local server's database(MS SQL 2000).3. In the application's web.config there is a connection string pointing to the remote server and another one (alternating one) points the local server's database. in scenario like this i first to tested the remote connection. if it is not ok then i initialize the local server's connection like thisprivate MyConnection() { try { connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForRemote"].ToString()); connectionSql.Open(); } catch (Exception ex) { connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForLocal"].ToString()); } finally { connectionSql.Close(); } connectionSql2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Temp"].ToString()); }My problem is when the remote connection is lost it takes almost 1 minute to store in local database. how can i make it more time efficient. Thanks....
Hi, how I do create SqlDataSource in the code-behind,setting Select SQL along with its parameters and, finally automically generating Del, Update, Insert SQL. Is there a way to achieve this goal as it would in VS designer? Thanks, Ricky.
Hi! I am using VS 2003 (v 1.1) I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net. How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation. Give me the complete code with can example (if possible)
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview. Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx Here is some of my code..Private Sub ApplyFilter() Dim _filterExpression As String = "" If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then _filterExpression = "CategoryName = '{0}'" End If Me.SqlDataSource1.FilterExpression = _filterExpression End Sub Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged ApplyFilter() End Sub But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
