Hi ,
I have a table (source file) where all sourse file(its all text file) name and location stored. i have a dts packeage, through this package all the sourse file will upload. So I need to dynamically change the source file name in dts packages.
How i can do it??????
Can any body plz help me out!
Thnaks
SAndipan
I am a relative newbie to SSIS. I have been tasked with writing packages to import data from our clients. We have about 100 clients. Each client has a few different file formats. None of the clients have the same format as each other. We load files from each client each day. Each day the file name changes. I have done all of my current development work with a constant file name in a text file connection manager.
Ultimately we will write a VB application for the computer operator to select the flat file to load and the SSIS package to load it with. I had been planning on accomplishing this thru the SSIS command line interface. Can I specify the flat file to load via a variable that is passed through the command line? Do I need to use a Script Component to take the variable and assign it to the connection manager?
Is there a better way to do this? I have seen glimpses of a VB interface to SSIS. Maybe that is a better way to kick off the packages from a VB app?
Thanks,
Chris
Hi,
I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?
i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.
Any help would be appreciated
I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched
Regards Justin
First off, let me just say that I'm a complete newbie to SQL Reporting Services, and .NET in general. We have a VB 6 application that is trying to launch an SRS 2005 report in a viewer window. This was accomplished by creating A VB.NET "wrapper" window that launches the report, and allows the report to be previewed, exported, etc. I did not write any of this.
The report is using a Shared Data Source, which points to a specific database. My problem is that the users can select which db they want when they launch the VB 6 Application, so I need to find a way to edit the connection string in the report to specify which database to use on the fly. I have the db name in the "wrapper" application, but I can't figure out how to pass it to the report.
thx,
Paul
I want to change my text box text colour for following ;
MY data was:
column name: Â NAme
Unitedstatesofamerica
america state
Northamerica
southamerica
America should be bold and red colour.
I want to write expression to BOLD and change colour in ssrs for America.
hi all
I have a table which has the paths where the source files need to be collected.
So I have a set of files which I need to collect. So I need to iterate through the result set which retrieves the paths
where the files can be found and set this dynamically to connection string of flat file connection manager.
Can anyone please let me know how to do this.
Can you be a little elaborate as to how we can do this because I am really new to SSIS.
Please help me as I am new to SSIS.
Thanks
Sai
Hi all,
I got a unicode file source with this fields:
-DT_WSTR (100) originally is DT_STR(100)
-DT_WSTR (100) originally is DT_STR(100)
-DT_NTEXT
-DT_WSTR (20) originally is DT_DBTIMESTAMP
-DT_WSTR (5) originally is DT_BOOLEAN
I export a Query result to a File (see above) ...as unicode TXT destination.
OK, now I must to re-import into another DB and here is my difficult...'cause the DT_NTEXT is HTML code and I got always this error:
[Flat File Source [1050]] Error: The column delimiter for column "scheda" was not found.
Scheda field is the DT_NTEXT.
Into connection manager area I modify the advanced tab for the set-up of my fields setting all to:
Unicode string [DT_WSTR] with a variable of the len, but Try also to define everyone to the rigth type of the SQL destination like:
- DT_STR(100)
- DT_STR(100)
- DT_DTNTEXT
- DT_DBTIMESTAMP
- DT_BOOLEAN
In every type of action I see no message alert and all seem to be good, but when I try to execute got always same error...
So hope someone can help me...
-----------
here first line of my UNICODE TXT source file
----------
"codven" "manufacturer" "scheda" "last_modified" "modificata"
"CDGI2120" "Altri" "<datasheet><section ncellmax="1" id="1"><row order="1"><cell><![CDATA[Combat possiede mitragliatrici per intraprendere battaglie testa a ~testa del genere "spara o sei finito" in mezzo a territori ~butterati di crateri su carri armati del 23esimo secolo.~Caratteristiche:~* Cinque modalita' di gioco~* Tre tipi di carri armati~* Partita singola o in multiplayer~* Grafica in 2D, 3D]]></cell></row></section></datasheet>" "2007-12-11 13:02:26.290000000" "1"
"CDGI2586" "Disney Interactive" "<datasheet><section ncellmax="1" id="1"><row order="1"><cell><![CDATA[Entra con Tigro ed i suoi amici nel meraviglioso Bosco dei 100 Acri aiutalo a cercare il miele nella natura incantata di questo fantastico mondo! ~~Il giocatore vestirÓ i panni di Tigro, il simpatico e buffo amico di Winnie The Pooh, il quale dovrÓ raccogliere quanto pi¨ miele possibile, per rendere la festa di Winnie qualcosa di veramente speciale!!!]]></cell></row></section></datasheet>" "2007-12-11 13:02:26.290000000" "1"
Hi All,
I have this very big text file (2.5 GB - which of course I am not able to open). This resides on a server which has SQL 2005.
I have created a linked server for this text file on my SQL database, and when I try to query the table, I get an error:
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "txtsrv".
Why am I getting this error? When I make a dummy linked server on my local machine and test the steps it all works OK. but when I go to this server where the file and it's SQL instance is lying, I get this error. Please can someone provide me with some help on this?
Many thanks,
~S
P.S. Here is how I created the linked server (I also have the Schema.ini lying under the same folder):
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:SSAnalysis',
NULL,
'Text'
GO
I am trying to create a text file from an SQL query on a SQL table. I would like the SSIS package to prompt for the file name and path. The text file is tab delimited and the text qualifier is a double quote.
Thanks,
Fred
Hi,
we have one requirement to run the package daily basis.
The package should run at specific time on that day.
we are using windows schedular for that.
we will have one new flatfile everyday.
Is there any process to attach this file to flat file source dynamically?
The requirement is,
The flat file should be able to read the new flatfile everyday.
we have no option change it manually, the flatfile source should have to take the file automatically at that time.
So that it can take that flatfile and load it into database table.
Good Afternoon,
So this one has been bugging me for a while and I am ready to punt...
Is it possible to dynamically create a text file destination in SSIS and then pump the results of a query stored in a variable to this text file?
So my package looks like this
1) SQL task that pulls back a list of tables to be exported
2) For Each Loop ADO enum that passes the table name to a SQL Task that builds the select...ie select * from <DTS.Variables()>
3) Data flow task that sets the command from variable from step 2
4) Text File destinaiton that is built using a varable as the connectionstring
I am delaying validation in steps3 and 4 above without any luck...basically I am curious if i can even do what I am thinking I should be able to do here...I get as far as getting metadata errors because SSIS can't seem to handle dynamically filling the pipeline with the columns from the variable/SQL statement.
Am I missing something? Is this possible?!
Thanks in advance.
Dave
I have a select Script as follows:
SELECT c.ABC AS 'ABC'
, a.Qty AS 'Quantity_Recived'
, b.PC AS 'PC'
, b.PC AS 'PC'
, 'I' AS 'Flag'
FROM TNRInventory.dbo.tInventoryAlloc AS a
LEFT OUTER JOIN vwInventoryAllocMapping AS vwMap ON a.TNRAllocTypeID = vwMap.TNRInventoryAllocID
LEFT OUTER JOIN ABC.dbo.ZREFRESHTAB AS b ON a.DispenserID = b.Asset
LEFT OUTER JOIN ABC.dbo.TableJoinKey AS c ON a.TitleID = c.TITLE_ID
WHERE (vwMap.DataSourceID = 3) and vwMap.[DataSourceAllocName] = 'I'
group by c.SKU_NO , vwMap.[DataSourceAllocName],a.Qty , b.Profit_Center
order by c.SKU_NO,vwMap.[DataSourceAllocName]
GO
i have to send the result of aforesaid script in batch of 300 records per file (tab delimited text file)
now the file name must be dynamically created as each file will contain 300 records.
I have found some document related to same issue on this url
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1238184&SiteID=17
but still there is a catch.
Can any one guide/suggest me better way to do the aforesaid.
Thanks
hi all,
i'm facing a small problem with xml file source.
let me explain the scenario!
we have 4 source xml files with same format, each file has around 25k rows of records.
XML Source
Derived Columns
Data Conversion
Conditional Split
OLEDB Destination
its executing fine.
once we change xml file source (second xml file with same xsd),
all other components are showing errors. the error is that
Error 21 Validation error. Staging: DTS.Pipeline: input column "LateRsnCd" (17917) has lineage ID 19380 that was not previously used in the Data Flow task. OnTimeOrderEntry.dtsx 0 0
lineage id has changed it seems, when we are giving new file as xml source. of course xsd is same, but its not taking with names.
to resolve this, if we open that errored task, select all, map using column name--> apply. its getting mapped with the columns as it is in the input.
still the conditional split will show the error.
if we open the conditional flow and give ok, the error has gone, i think some meta dat change has occured in the opening task and giving ok.
what could be the possible probelm and how to fix it?
one more doubt, how can we include xml source in the configuration file? since it doesn't have connection manager, i'm struggling with it for dynamic file selection for xml.
i dont want hard code the source file path (as it reside in the server) in the poperties of the xml source task.
can i have some suggestions please?
I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.
I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.
On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.
I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?
That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.
Any help would be appreciated!
Hi,
I am using SQL Server2005 for SSIS. I want to change the source connection dynamicaly evertime.
Let me clear, I have to extract some column from excel to MS-Access. I am using Data Flow Task and able to successfully complete the job. But problem is that, whenever a new file comes , i must have to reconfigure my Excel Source.
All the time column in file are same, so no need to worry about mapping but how can my package select a file automatically.
I have a directory, suppose "C:dpak". I should able to pick the filename and sheet name from this directory every time when my package will execute.
To be clear:
You have a store procedure and inside you make Updates or Inserts against another Server. But that name can change and I dont want to change it manually in everyplace.
Per example:
SELECT *
FROM Himalaya.DBName.dbo.tblUserGroup
WHERE fldUserID = 7300
I have several Selects, Updates and Inserts pointing to that server.
What I can do if I want to change from Himalaya server to another server?
The same with the Database Name.
Thank you
I have implemented a package to load multiple files to a destination. Since the source was a txt file, i have created as flat file source. However now we are getting files in excel format as well.
Is there anyway the source gets changed dynamically based on the file extension, output of the foreach file enumerator? I can think one solution to have 2 dataflow tasks based on precedence constraining and expression one is for .txt and other one is for .xls.
I have a peculiar problem with a database.
I need to import data from text files where one column matches a value in a table that's already on the database. I cannot even fathom how to do this without pulling the entire 1GB file onto the database and simply deleting data that doesn't match the criteria. While this would work, I'm sure there's a more efficient way to do it! Can anyone give me some pointers? I'm not exactly a DTS expert.
Thank you.
I'm running EM on my local box and sql server on a remote internetaccessed box.How do I specify a file path for a DTS package to access files on theremote box?For example, to run a local dts package the filepath isc:filepathfile.txt.How would I change the file path/name to allow the dts package toaccess files on the same remote machine?-Dave
View 1 Replies View RelatedIn DTS 2000 I had a situation where I had a text file as input source and text file as output source. On migrating the package to 2005 it puts a wrapper around it which executes it as a 2000 package, the rest of the tasks are neatly converted to 2005 style tasks. I presume this to mean that this will not be supported through to the next version, and there is no direct equivalent in 2005.
My question is how do I import a non-flat file source which has different numbers of columns per line. I did ,somehow, manage to do this with 2000 but cannot seem to get anywhere with 2005.
The flat file source seems to be expecting a common number of columns and just can't seem to cope with no column delimiters on some lines. If anybody knows different I would be glad to hear about it.
Raw data is not helpful to me as only works with a specific raw type (apparently)
Went onto Bulk Insert Task but got this message
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file.".
Have already browsed with this on web but only find comments about changing timeout setting.
Can find timeout settings in DataFlow source and DataFlow destinations but not in Bulk Insert Task.
As you can see this is a long and protracted question.
If the answer is simple I apologise if not blame Microsoft. Other than that have found SSIS has some nice improvements, apart from the odd vague error message I keep coming across.
Unlike SQL Server 2000 DTS, SSIS Flat File Connection Manager Editor does not provide available list of Text qualifier,
i tried
-- ""
-- double quote {"}
-- "
--{"}
but none of them worked....!
My file sample looks like this
"Col1","Col2"
"1234","3456"
"3456","1234"
what qualifier should I use then?
Many Thanks,
Hi Mates,
I am unable to fetch data from Source which is CSV file whose sample contents are given below:
-----------------------------------------------------------------------------------------------------------
"Fiscal year";"Posting period";"Company Code";"Functional area";"Profit Center";"Business area";"Group Account";"Posting Level";"Document Type";"Accounting Principle";"Cost Pool";"Financial Owner";"Period Value GC"
"2006";"12";"AR00";"A107";"P1131";"7200";"3410";"10";"18";"GAAP";"#";"#";"$ 0.00"
"2006";"12";"AR00";"A107";"P1131";"9600";"3410";"10";"18";"GAAP";"#";"#";"$ 0.00"
--------------------------------------------------------------------------------------------------------------
The error message that comes while I preview the source data:
Error:
"The Preview sample contains embedded text qualifiers. The flat file parser doesnot support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at runtime"
Is there any alternative to remove these text qualifiers from the file. Do we have any utility that can convert these files into text qualifier free CSV file?
Hello Friends,
I have a problem with ASP.net with dynamic data transfer from asp page to microsoft sql server 2000. For example , I have asp
web page with one text field and a buttion.
When I click the buttion, the value entered in the text field should be transfered from the text field to database.
Kindly See the following section c# code .....
SqlConnection objconnect = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = objconnect;
cmd.CommandText = " select * from Table_Age where Age = @Age";
cmd.CommandType = CommandType.Text;
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@Age";
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Direction = ParameterDirection.Output;
parameter.Value = TextBox1.Text;
objconnect.Open(); // Add the parameter to the Parameters collection.
cmd.Parameters.Add(parameter);
SqlDataReader reader = cmd.ExecuteReader();
********************this section c# code is entered under the button control************************************
connection string is mentioned in the web.config file.
In the above c# code , I have a text field , where I entered the age , the value entered in the text field should be sent to
database. I have used SqlParameter for selecting the type of data should be sent from ASP.NET 2.0 to the microsoft sql server 2000. I am facing a problem
where I am unable to sent the random datas from a text field to the database server.
I have a created a database file in the microsoft server 2000.after the excution of the fIeld value is assinged to NULL in the main database i.e microsoft the sql server 2000.Can anyone help with this issue.My mail id phijop@hotmail.com- PHIJO MATHEW PHILIP.
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.Â
View 4 Replies View RelatedHi,
I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task).
After that, in an xml task, I set the input properties like that:
Operation type: xslt
SourceType: File connection
Source: ras.xml
All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that:
Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90
Me.VariableDispenser.LockOneForWrite("RAS", v)
v("RAS").Value = doc.OuterXml
v.Unlock()
and in the xml task, I set configuration like that:
Operation type: xslt
SourceType: Variable
Source: user::RAS
But when I execute, I got this following exception:
Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.".
Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed.
<?xml version="1.0" encoding="UTF-8"?>
<root> €¦
</root>
(When I store it in a file, I can see it in IE)
Have you got an idea ?
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.
Folks,
We have some reports that have optional columns. We have them working very nicely, with the column showing or hiding based on values in the report -- works great.
Except -- when the columns are present, the report spans onto two pages, when exported to PDF, in width. That's understandable, as there's a lot of extra data, and exactly what we want. However, when the columns are not present, we get empty pages instead, because the report doesn't automatically contract back onto the size that fits on one page.
Changing the report to a Matrix won't work, as the hidden columns on some of these come as sets of three, where each column in the three has different formatting (different widths, format codes, etc).
Thanks!
--randy
Hi all,
I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).
For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.
Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.
My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.
I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.
How can I achieve this the easiest way ?
Thanks in advance !
Geert
Is there a way I can build a case statment or similar to handle different where conditions?
I know I can do it dynamic sql but it would be nice to have a method where I can create the querries directly in a normal statement
Someting like:
Select c1, c2, c3
From Table
WHERE Case @Condition
WHEN '>' @SelColumn > @Limit
WHEN '=' @SelColumn = @Limit
WHEN '<' @SelColumn < @Limit
END
I know this doesn't work so an example that do work would be nice.
Hi All,
I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example:
Source table column Target table column
Name FN_Name
Age FN_Age
The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.
Which tasks or approaches can be used to implement this?
Thanks
Micror
Hi. I have this kind of problem since I am not very conversant with SSIS
and stripting in VB.NET.
The set-up is the following:
Flat File Source -> Script Component -> Flat File Destination
The flat file source looks like this:
NameOfFile
Headers
Data
Data
Data and many more rows of Data
NameOfAnotherFile
Headers
Data
Data
Data and many more rows
NameOfAnotherFile
Headers
Data
Data and so on in the same manner...
My stript looks like that (not very complicated):
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strRow As String = Row.Column0.Trim()
Dim strFolder As String = "Data"
Dim strConn As String
Dim charSep() As Char = {CChar(" ")}
If Mid(strRow.ToLower(), 1, 2) = "d0" Then
' This is the file name, so start a new connection to a new file
' which definitely does not exist yet
strConn = strFolder + strRow + ".csv"
' DestinationFile is the name of the
' Flat File Connection Manager
With Me.Connections.DestinationFile
.ConnectionString = strConn
End With
Else
Row.Column0 = String.Join(",", strRow.Split(charSep, StringSplitOptions.RemoveEmptyEntries))
End If
End Sub
I have several questions regarding this one.
1. Is it ok to change the ConnectionString property of the manager
to redirect the row to a different file? If not, what more to do?
2. If the new destination file does not yet exist, will it be created for me or will
I get an error? If I do, what to do not to?
3. The most important: What steps to take so that a row is discarded
from the pipeline in the script? I want some rows not to be directed
to the file destination. These are the lines that contain the name
of the file into which the data below belongs.
If you can optimize some steps in the script, then please do so by all means.
Thank you for any comments and the knowledge you kindly agree to share with me.
Darek
Is it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing.
SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
[code]....
ENDThe code runs fine, but doesn't actually increase the size of the file.
A section of this company's intranet site where I just started interning at has little company anniversary and birthday sections that look like (for the anniversary section.. in the birthday section, it looks the same, except it doesn't say how old the comployee is):
-Steve Cunningham 6/1 - 6 yrs
-Andrew Brown 6/3 - 11yrs
-Lisa Stone 6/4 - 3 yrs
How can I get it so instead of manually changing that text every month, it will look at a SQL database and automatically change that text every month? I'm guessing the pseudocode would be if the b-day or anniv. month matches the current month, display the first and last name, the date, and number of years (which would have to be calculated maybe?) Any help would be GREAT! Thanks!!
~Michael