0

I'm using SSIS and i want my string to become a table. The String i use comes from a database and i want to extract it and make another table which is instead of a string, it should be in a proper table. I was wondering what transformation should i use and how.

This is my example string

Column1:Row1 | Column2:Row2 | Column3:Row3 and so on..

And the output should be a table which is the column1 is the column and his data is the row1 and so on...

Column1 Column2 Column3
Row1....... Row2......Row3

(I'm not quite sure if SSIS can do that, but i think it can xD)
UPDATE
Another example string: (What if the string has 2 headers but differ in value?)
"Column1:Row1 | Column2:Row2 | Column3:Row3 | Column1:Row4|"

OUTPUT:
Column1 Column2 Column3
Row1....... Row2......Row3
Row4........Null.........Null

Any help is appreciated thanks!

Kuroi
  • 212
  • 2
  • 15
  • It can do that but I suggest you do it in pure T-SQL instead. Your example data isn't a table though. How is value column 1 row 2 defined? add it to your example. – Nick.Mc Sep 08 '15 at 10:00
  • i have this table which is the column are ProcessName and Processbody, under the processname is either Overtime or Leave then follows the processbody which is the string, just like the string i posted. i want create two new table, for the overtime and the leave, so i want the string to be fixed. The string is saved in my table exactly as that (Column Name:data | Column Name:Data......) – Kuroi Sep 08 '15 at 10:33
  • You should use the Execute SQL task, and execute sql commands that will build the table you want. – Tab Alleman Sep 08 '15 at 13:17
  • Can you add to your explanation what that data should look like in tabular form? Are the number and names of columns fixed? How is value column 1 row 2 defined? – Nick.Mc Sep 08 '15 at 23:12
  • @Nick.McDermaid ok, so for example, the overtime, i will be creating a new table just for the overtime which has a value of a long string (C=R | C=R,) C as a new column then R as his data then | acts as the (delimiter)separator for another column. yes, the string i fetch are fixed in case of a null it just saves as (C=). I used (=) to separate the column and row. Is there a way defining the C as Column and =R as his data in SSIS? the only thing i did was to create a separate table for the overtime and leave but the data i still save is rawstring, so i need to make it a table. Thanks – Kuroi Sep 09 '15 at 01:02
  • @TabAlleman so basically i should only use SQL commands? didn't know there was such a task, i really need to explore SSIS, anyway thanks for the info, i'll try it – Kuroi Sep 09 '15 at 01:30
  • In T-SQL, to turn one row into many you join to a tally table and use a where clause to limit the rows. To split a single column into many you just use a number of expressions. I can pose a brief solution but there still isn't enough info for it to be very helpful – Nick.Mc Sep 09 '15 at 02:55
  • Just to clarify: this is a bad way to store data. If you are stuck with this data and can't fix it then thats OK. But if you are considering actually storing your data in this way... I strongly suggest you do not. – Nick.Mc Sep 09 '15 at 02:58
  • You need to edit your original question and post a sample of the output required. – Nick.Mc Sep 09 '15 at 03:02
  • Is there any alternative way? the thing is im actually using VS2015 in storing my data (as string), (Report Purposes)coz the data i've been storing is from a site which we manually extract. So i'm not quite sure if SSIS can do the work and tried it. At first i store it as json string but change it to a string. is there a way in VS2015 that when automatically saved in the database it creates and store it to a new database respectively? like if the string is for process 1 and the other string is for process 2 and automatically create a table that convert those strings to table? – Kuroi Sep 09 '15 at 03:13
  • All these things are possible in SSIS, Visual Studio (C# I assume?) or T-SQL. But you aren't giving us enough info to answer anything you're just asking more questions. If the data already arrives as a string then it might be a good idea to transform that into a table as it makes it easier to report on with SQL queries afterwards. Why are you storing it in a database? To be reported on? – Nick.Mc Sep 09 '15 at 03:36
  • vb, yes for reporting. that's what im trying to do, converting string to table using SSIS – Kuroi Sep 09 '15 at 03:43
  • So looking at your edited question, you only want **one** row out of this data but many columns? that's pretty trivial really SSIS is total overkill. You can just use a split function in VB or T-SQL. The problem is when you get a column name in your string but that column does not exist in the database – Nick.Mc Sep 09 '15 at 04:16
  • i know right haha, well thanks for the answer. thank you – Kuroi Sep 09 '15 at 05:18
  • Here is a solution but I think it's really just a starting point for more discussion. The SQLFiddle is here: http://sqlfiddle.com/#!3/a1d86/6. It uses some code from here: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql It's quite complicated and you still need to actually define the columns twice. The important questions is: do you _already_ have a table defined to put this in? If so then it becomes easier. Please look at the solution because it will prompt you to comment on any gaps. – Nick.Mc Sep 09 '15 at 08:36
  • @Nick.McDermaid yes i have a table where i can put the data in, but what if i don't? is it possible? is it possible to base the header of the table on the string? i really don't think there's a way like that. But iv'e tried your suggested link and it's working but i found some minor problems like for example when the string has 2 same header for example name=name1 but has different value. The output will be the last header in the string and bypass the first header (name). – Kuroi Sep 17 '15 at 02:59
  • You can return a dataset without a table (a select statement) but every column name (header?) needs to be unique. There is some kind of mismatch here between what you're asking and what a database does. If your header (columns) aren't unique what happens when you type `SELECT name1 FROM Table`. Which name1 does it use? You need to explain some background in this. By header do you mean 'column names' or 'the first row of data'? – Nick.Mc Sep 17 '15 at 03:09
  • @Nick.McDermaid Column names – Kuroi Sep 17 '15 at 03:10
  • SSIS is not going to let you dynamically define columns. It's metadata is fixed and any change to it invalidates the package. – Nick.Mc Sep 17 '15 at 03:11
  • @Nick.McDermaid so basically, having 2 same column name on my string is impossible? – Kuroi Sep 17 '15 at 03:15
  • Thats fine in a **string**. A string is not a column. You can't have a non unique column, The fact that you _do_ want non unique columns tells me this does not belong in a table. You could add logic to the code to add a unique number to the end but I think the code is horrible enough as it is. – Nick.Mc Sep 17 '15 at 03:22
  • @Nick.McDermaid in a string yes, but when converting it into a table? just like I updated in the question above. Using the link you've suggested doesn't seems to fix that. Do you think there's a way? Let's just assume that the table where I want to put it in already has their own column name the same as the string. – Kuroi Sep 17 '15 at 03:29
  • You won't be able to create a table with two columns called "name1". So assuming that, you just put an INSERT statement in front of the select and it will load it into a table. But you have to explicitly name each column in the insert and the select. If the column doesn't exist it just gets blank. Do you know for certain how many columns are in your string every time? – Nick.Mc Sep 17 '15 at 03:31
  • Oh now I see you don't want two columns you want two rows? That is actually what I asked about waaaaaaaaaaaay in comment 4. There is nothing really in your data to say what row it goes on, except that one comes after the other. Let me have a think about this. – Nick.Mc Sep 17 '15 at 03:34
  • I _hate_ to say it but this looks like it would be best handled in a <_shudder_> script transformation. Are you familiar with C# etc.? – Nick.Mc Sep 17 '15 at 03:38
  • @Nick.McDermaid No, i won't be creating a table with two column names called "name1" Nope, i don't know for certain how many column names in my string. As for now i fetch it as JSON string then convert it into a rawstring that has some delimiter so i can convert it into a table. So my problem is (imagine the string i'm fetching is some character info) what if there's 2 character in one string, the column names on my string repeats right? but differ in value. I want them to insert in a table that already has a column name for them but i want them to be inserted as a TWO records – Kuroi Sep 17 '15 at 03:39
  • This is achievable with a script source in a SSIS package but it requires you to define the columns before hand. It might actually be easier to just write a standalone exe to do it for you rather than use SSIS. SSIS is really finicky about metadata (columns, data types) and doesn't play well with 'dynamic' metadata like this. Are you comfortable with C#? – Nick.Mc Sep 17 '15 at 03:41
  • are you comfortable with this info: https://msdn.microsoft.com/en-us/library/ms136060.aspx?f=255&MSPPError=-2147217396 – Nick.Mc Sep 17 '15 at 03:44
  • @Nick.McDermaid nope, I've been using vb for a long time but i'll try and take a look at it, i mean there isn't much difference in vb and c# (i guess?) anyway,im really really greatful to you. – Kuroi Sep 17 '15 at 04:24
  • No, I've just found there's just more examples in C# but VB does the same thing. I would actually suggest you simply create a EXE in VB that does what you want - accepts the JSON and transforms it and inserts it. You'll just be doing the same thing in SSIS except it has more constraints like you have to define the target columns – Nick.Mc Sep 17 '15 at 04:33

2 Answers2

0

You can use the token expression (technet link) in a ssis derived column.

Use something like below as an expression

TOKEN("COLUMN1","|",1)

to give you the first result

TOKEN("COLUMN1","|",)

to give you the second result etc.

Easiest way to do that in SSIS

attic_view
  • 48
  • 7
0

Two common ways to convert data in your data flow is to use the Data Conversion transformation to perform a simple conversion or to use the Derived Column transformation to create a column that is based on converted data.

Jason Clark
  • 1,307
  • 6
  • 26
  • 51